INIT.ORA Recommendations for Oracle

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.

0 COMMENTS

We’d like to hear from you!