Performance Tuning Guide

10.3

Back Next

Recommendations for Oracle Database

You can configure the parameters related to the Oracle database environment, tablespace, Oracle table statistics, RAC recommendations, and Oracle flashback.

The following table lists the recommendations related to the Oracle database environment:

Parameter	Recommended Setting	Description
C_REPOS_APPLIED_LOCK	Enable caching for this table, C_REPOS_APPLIED_LOCK	The application uses this table frequently so you can cache this table to improve performance.
RMAN backups	Suppress RMAN backups from running during batch processing.	RMAN backups are good for a fast backup and restore. However, performance is decidedly lower when the RMAN backup is performed.
Archive Logging	Turn off archive logging during the initial data load. For steady-state operation, you can turn on archive logging after the initial data load ends.	Archive logging is unnecessary during the initial data load and adds an overhead. If there is a failure during the initial data load, Oracle rolls back the entire transaction (the current batch cycle of a batch job). The process can be re-run with no data loss. During the initial data load, back up the ORS schema that loads at regular checkpoints with no jobs running. For example, back up the ORS schema after major long running jobs have completed. At an absolute minimum, take backups after completion of each phase: Stage, Load, Match, and Merge. You require backups to safeguard the work already done before you proceed. You might enable archive logging for all steady-state operations (post initial data load). If you use a Standby database (database mirroring), disable the standby before doing the initial data load. When the initial data load is complete, copy the database to the standby site, and then enable the standby.

The following table lists the recommendations for the tablespace:

Parameter	Recommended Setting	Description
Tablespace	Use the following recommended settings for tablespace: Locally Managed. Uniform Extent. Auto Segment Management. Default Tablespace Block Size of 8 KB or 16 KB.	Applicable for all tablespaces involved in the MDM Hub including CMX_DATA , CMX_INDX , and CMX_TEMP . Default Tablespace Block Size of 8 KB is good for high API workload implementations. Use 16 KB as a balanced block size to support a mix of API and batch processing (small transactions and bulk read and write transactions). If you use a Default Tablespace Block Size of 16 KB and the database was created with default block size of 8 KB, then configure an appropriate DB_CACHE for it.
Tablespace Storage	Use the following Oracle recommendations for storage of tablespace data files: If you use RAID: use either RAID 1+0 or RAID 0. If you do not use a RAID controller: each tablespace must comprise multiple data files spread across different disks.	Many small disks perform better than a few large disks if everything else remains equal. Use the following steps to improve the performance: RAID1+0 (RAID 10) has a high degree of fault tolerance with mirroring. Use as many disks as possible. Disks must be as fast as possible. Avoid RAID 5 due to a writing overhead and poor performance if there is a disk failure. If there is no RAID controller: use multiple single disks to split several data files over more disks. Do not dedicate data files for one tablespace to a single disk. Use the fastest disks possible and stripe the disks. In such cases, each tablespace uses part of each disk instead of a tablespace being dependent on a single disk. Similarly, it is better to keep redo and undo logs in a different physical disk.

The following table lists the recommendations for the Oracle table statistics:

Parameter	Recommended Setting	Description
Table Statistics	Analyze the ORS schema on a regular (frequent) basis. Use the following options: Analyze the full schema (perform outside business hours). Analyze individual tables whenever 10% of the data is changed. Perform unplanned table analysis. Set the DBMS_STATS .SET_GLOBAL_PREFS (see Environment Sharing in general database recommendations). With no sharing: DBMS_STATS.SET_GLOBAL_PREFS( 'DEGREE', DBMS_STATS.AUTO_DEGREE); If forced to share: DBMS_STATS.SET_GLOBAL_PREFS( 'DEGREE', <(number of CPUs on DB Server) minus 1> );	Analyze the entire ORS schema on a regular basis as a best practice. Analyze individual tables whenever 10% of the data is changed by using data sampling. This is the Oracle recommendation. See Oracle database documentation for details. With sampling there is a trade-off. The best execution plans are always chosen when the statistics represent the entire table. When you use sampling, the execution plan is as appropriate to the tables as the data sample. For example, you can use 10% of the table as representative of the table as a whole. If you use a sample, be sure to use a large enough sample. A sample of 1 to 2% is too small unless the table is large. A sample of 10% is more representative on smaller tables. You can switch off sampling and perform a full analyze, which provides the most appropriate execution plans. However, a full analyze is achieved at the cost of far higher overhead in terms of the time taken when running the analyze. It is best to run outside of business hours to mitigate impact.

The following table lists the recommendations for RAC:

Parameter	Recommended Setting	Description
Environment	Each node must satisfy the same recommendations as set for the standalone environment.	-
_PKQ Sequence	Use NOORDER . Set sequence cache to 20000.	Informatica recommends to increase initial data load performance. These sequences are used to populate the ROWID_OBJECT in the base object tables. For more information, search the Informatica Knowledge Base for article number 115788.

The following table lists the recommendations for the Oracle flashback:

Parameter	Recommended Setting	Description
Flash recovery area	Fast file system.	Use a fast file system for your flash recovery area, preferably without operating system file caching.
Disk spindles	As needed.	Configure enough disk spindles for the file system to hold the flash recovery area.
Striped storage volume	Smaller stripe size.	If flash recovery area does not have non-volatile RAM, opt for striped storage volume with smaller stripe size such as 128k. This will allow each write to the flashback logs to be spread across multiple spindles, improving performance.