IBM Support

Setting up IBM Initiate Master Data Service with Oracle Database

Question & Answer


Question

We are preparing to install IBM Initiate Master data Service with Oracle database and were wondering if you had any recommendations on how we should set it up?

Cause

IBM conducts application testing using a certain Oracle configuration and we recommend similar settings since we know that the product performs well with these settings. The recommendations within this document are to be used as a guideline for the construction of the corresponding Oracle instance. IBM Initiate Master Data Service administrators or Oracle DBAs may chose to deviate from the recommendation and customize these settings, but it may lead to performance degradation unless the administrators have done extensive due diligence.

Answer

During initialization, we require the database to be created with AL32UTF8 character set and that NLS_LENGTH_SEMANTICS is set to CHAR (default is BYTE). We also recommend usage of Oracle’s ASMM (Automatic Storage Memory Management) capabilities during the early stages of the project to establish a baseline of SGA performance and then later adjust as needed. ASMM will manage the buffer cache (default pool), shared pool, large pool and java pool and we expect high Buffer Cache (> 97%) and Shared Pool (> 98%) Hit Ratios. In Oracle 11g and beyond, automatic memory management is enabled by using the memory_target  and memory_max_target initialization parameters and following are some of the key ASMM parameters and recommended (initial) settings.

STATISTICS_LEVEL – Typical or All
MEMORY_TARGET - 80 ~ 90% of available memory
SGA_TARGET - Set to non-zero to enable ASMM. This is the initial memory allocation when Oracle ASM enabled. Set this value to satisfy default buffer cache requirement with rule of thumbs 1 GB per million members.

Set the following 5 parameters to zero to allow Oracle automatically manage pool size
SHARED_POOL_SIZE = 0
LARGE_POOL_SIZE = 0
JAVA_POOL_SIZE = 0
DB_CACHE_SIZE = 0
STREAMS_POOL_SIZE = 0

DB_KEEP_CACHE_SIZE- At a minimum, this should be set to the size of the MPI_MEMCMPD1 and MPI_MEMBKTD1 index. Pinning this index in the Keep pool is a key performance requirement

Additional parameters include:
OPTIMIZER_MODE = FIRST ROWS
DB_BLOCK_SIZE = 8K
DB_FILE_MULTIBLOCK_READ_COUNT = 16
CURSOR_SHARING = FORCE
FILESYSTEMIO_OPTIONS = SETALL
OPTIMIZER_INDEX_COST_ADJ = 5
PERFORMANCE CONSIDERATIONS

Other Best Practices:
* A large percentage of I/O requests come from the MPI_MEMCMPD table and you can reduce Physical Reads by “pinning” the MPI_MEMCMPD1 index within the Keep Pool.

* To reduce the number of Parse requests, it is recommended to set the parameter CURSOR_SHARING=FORCE
* The randomness of the data selections requires that all disks participate evenly, with proper LUN and striping configuration. For optimal disk performance, Oracle should always use direct I/O to its data files, bypassing any caching at the OS layer. This must be enabled both in Oracle and in the operating system. ASM incorporates the Stripe and Mirror Everything (SAME) methodology for greater disk performance and storage management. It’s this methodology that IBM recommends for all storage systems supporting a IBM database Hub.
* Tablespace definitions should be configured for Extent Management and logical separation of Tables and Indexes. Locally managed Tablespaces with Uniform Extents sizing works well for most implementations. IBM utilities will create objects within the default Tablespace of the IBM user. Storage files (stofile) can be used to specify Oracle Table and Index creation syntax. Sample Sto file:
S|mpi_syskey|TABLESPACE MYDATA01 storage (initial 10M next 5M)||
S|mpi_syskey1|TABLESPACE MYINDEX1 storage (initial 10M next 5M)||
* It is recommended that indexes on the larger Member tables be analysed every 6 months to 1 year to determine if they are candidates for a rebuild. A rebuild of the larger indexes will reclaim some space from deleted rows.
* It is the recommendation of IBM to establish a good set of statistics once the Initial loading of the data is complete. Statistics should be re-executed periodically. However the execution plans are static and are not affected by “fresh” statistics. Running stats gathering jobs (DBMS_STATS_JOB) nightly, weekly or monthly should not affect the performance of the IBM queries, either negatively or positively.
If you choose to run statistics less frequently, tuning the initialization parameter, OPTIMIZER_DYNAMIC_SAMPLING may improve performance. The main objective of dynamic sampling is more accurate selectivity and cardinality estimates.

[{"Product":{"code":"SSLVY3","label":"Initiate Master Data Service"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"--","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"Version Independent","Edition":"Edition Independent","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Product Synonym

MDS;Master Data Service;MDM;MDMSE;Master Data Management;IBM Infosphere Master Data Service;MDM Standard Edition;MDM Hybrid Edition;Virtual MDM

Document Information

Modified date:
16 June 2018

UID

swg21674800