Performance Tuning Guide

10.5

Back Next

INIT.ORA Recommendations for Oracle

The INIT.ORA recommendations are based on the standard hardware of 24 GB RAM with 8-core CPU Oracle server.

The following table lists the basic initialization parameters for the Oracle database:

INIT.ORA Parameter	Applicable to version	Value	Description
cursor_sharing		EXACT	Only allows statements with identical text to share the same cursor.
db_block_checking		FALSE	To avoid additional overhead.
db_block_size		8192	This parameter affects the maximum value of the FREELISTS storage parameter for tables and indexes. Oracle uses one database block for each freelist group.
db_cache_size		2000M	Reduces additional overhead on dynamic allocation.
db_file_multiblock_read_count		0	To be auto-determined by Oracle database.
db_writer_processes (Single Core)		1	Oracle guideline.
db_writer_processes (Multi Core)		CPU / 8	Oracle guideline.
disk_async_io		TRUE	Oracle guideline.
filesystemio_options		SETALL	This bypasses file system buffer cache, especially on Linux.
java_pool_size		0	To be auto-determined by Oracle database.
large_pool_size		400M	If undefined, RMAN would use the SHARED POOL.
log_buffer		10M	A value of 10 MB is a reasonable initial size. Increase based on Oracle AWR report taken under typical heavy load. Section: Cache Sizes > Log Buffer
memory_target		Two thirds of available memory.	Two thirds of the available physical memory for Oracle. One third is reserved for system and other processes.
memory_max_target		Two thirds of available memory.	Two thirds of the available physical memory for Oracle. One third is reserved for system and other processes. Based on your requirement, you can increase this value.
open_cursors		1000	The MDM Hub uses parallel processing requires opening up multiple cursors.
parallel_adaptive_multi_user		TRUE	The MDM Hub uses multi-sessions for the same Oracle instance.
Processes		1000	Sufficient Oracle processes are allocated to support connection, parallel thread, internal process, and other usage. If set too small, some processes might fail to run.
Recyclebin		OFF	Set the value of the recyclebin parameter to OFF. The MDM Hub has many temporary tables that the recycle bin spends time trying to maintain when dropped.
shared_pool_size		400M	Use 400 MB initially. Increase this according to the value seen in the Oracle AWR report for this instance taken under typical heavy load. See the Section: Cache Sizes > Shared PoolSize.
streams_pool_size		0	The MDM Hub does not use Oracle streams functionality. Disable the functionality to prevent Oracle from reserving memory for it.
utl_file_dir		Do not set this	Use an Oracle Directory object instead of this parameter. For more information, see the Informatica Knowledge Base article 90456.
workareas_size_policy		AUTO	To be auto-determined by Oracle.
pga_aggregate_target	11g	0 in case of Automatic Memory Management (AMM) or One third of memory allocated to Oracle in case of Manual Memory Management (MMM).	Set PGA explicitly for MMM. For AMM, you need not set PGA.
sga_target	11g	Two thirds of Max_Memory in AMM or two thirds of allocated memory to Oracle in MMM.	For SGA, Informatica recommends that you allocate two thirds of memory available for Oracle regardless of AMM or MMM. In AMM configuration, the setting of SGA explicitly always reserves two thirds of memory for SGA. This makes Buffer Cache ready for large data loaded into it during the batch. If you do not SGA, AMM might allocate memory for something else. The remaining memory might also not be enough for a big batch and it might run slower.
optimizer_capture_sql_plan_baselines		False	Setting this parameter to false makes SQL plan management to not recalculate the execution plan for each repeatable SQL statement.
optimizer_index_caching		0	Setting this value to 0 defaults the behavior of cost-based optimization to favor nested loop joins and IN-list iterators.
optimizer_index_cost_adj		100%	To make use of default optimization based on table indexes, setting to default value evaluates index access paths at the regular cost. Default value is 100%.
optimizer_adaptive_features	12c	FALSE (Scope = BOTH).	Disables all the adaptive optimizer features, including adaptive plan, for better performance.
optimizer_use_sql_plan_ baselines		TRUE	Enables or disables the use of SQL plan baselines.

For more information about the INIT.ORA parameters, see Informatica knowledge base article 90408.

Rename Saved Search

Table of Contents

Performance Tuning Guide

Performance Tuning Guide

INIT.ORA Recommendations for Oracle

INIT.ORA Recommendations for Oracle