Database Guide › Oracle Guidelines › Database Configuration › Initialization Parameters

Initialization Parameters

We recommend these values for the following Oracle initialization parameters.

General Parameters

optimizer_dynamic_sampling

Set this parameter to 4.

db_file_multiblock_read_count

Set this parameter to 128.

open_cursors

Set this parameter to 500.

session_cached_cursors

Set this parameter to 100.

QUERY_REWRITE_ENABLED

Set this parameter to True.

QUERY_REWRITE_INTEGRITY

Set this parameter to Trusted.

Oracle 10g (64 bit) Parameters

The recommended settings are different for small deployments and large deployments. (A small deployment sees less 500,000 events per day; a large deployment sees over 500,000 events per day.)

Note: Do not perform final tuning of the SGA and PGA until you have executed wgn_stats and applied all fixes. By default, CA DLP generates poor query plans that require little PGA. The optimizer only generates good query plans that need a correspondingly larger PGA after wgn_stats has been deployed and all fixes have been applied (including Oracle fix 5765456:7).

sga_target

For small deployments, set this parameter to 5Gb.

For large deployments, set this parameter to 10Gb.

Note: Refer to the SGA Target Advisory in the AWR report.

pga_aggregate_target

For small deployments, set this parameter to 2Gb.

For large deployments, set this parameter to 5Gb.

Note: Refer to the PGA Target Advisory in the AWR report.

"_fix_control"

Set this parameter to:

"_fix_control"="5764456:7"

Note: The need to apply Oracle fix 5765456:7 is covered in the following section.

Oracle 11g (64 bit) Parameters

The recommended settings are different for small deployments and large deployments (see the Oracle 10g section above).

memory_target

For small deployments, set this parameter to 7Gb.

For large deployments, set this parameter to 15Gb.

Note: Refer to the Memory Target Advisory in the AWR report.

Oracle 10.2 Fix 5765456:7

Oracle 10g users may see improved search and report performance by applying Oracle fix 5765456:7.

For details, see the My Oracle Support (formerly Oracle Metalink) notice "Bug 5040753 Optimal index is not picked on simple query / Column group statistics."

Note: This fix is included in Oracle 10.2.0.4, but it is disabled by default.

Statistics

Statistics collection is essential. See the Database Maintenance section .

More information:

Database Maintenance

Collecting Statistics

Oracle: Implement the Wgn_Stats Package