Skip to main content
Blackboard Help

Setting Oracle Initialization Parameters

Oracle initialization parameters influence the performance characteristics of a running instance of Oracle. In general, a very simple initialization file with few settings is appropriate in most situations, and the initialization file should not be the main focus for performance tuning. Performance improvement projects should involve the analysis of under-performing SQL and the causes of latency, which could be related to physical database design, stale database statistics, system resource saturation, or poorly configured instance configuration.


For performance reasons, especially when using Oracle and licensing content management, CURSOR_SHARING should be set to the following:

  • Oracle 10g: EXACT or FORCE
  • Oracle 11g and later: EXACT

Access issues with groups and files may also occur.

Understanding the Importance of Optimizer Statistics

Optimizer statistics provide detail about a database and its objects and can be used to select the best execution plan for each SQL statement. Physical and logical system statistics also can be maintained. The combination of these statistics, whether managed automatically or manually, can directly affect the performance or service time of a query.

The following table lists the initialization parameters that directly affect the way statistics are used and subsequently how they shape the decision of the Optimizer.

These settings are recommendations and should be tested out over time. Oracle has put together a very informative guide about achieving the best performance from the Optimizer. To learn more, see Oracle Performance Optimization.

Initialization Parameters
Parameter Name Description Recommended Setting
OPTIMIZER_MODE Mechanism for result set retrieval
A subset of values are available: ALL_ROWS, FIRST_ROWS_1, FIRST_ROWS_10, FIRST_ROWS_100, and FIRST_ROWS_1000
OPTIMIZER_DYNAMIC_SAMPLING Controls the level of dynamic sampling performed by the Optimizer
This parameter can introduce overhead because the setting forces the CBO to perform read-ahead scans of tables to capture more accurate estimates.
Set this parameter to 0 (no sampling) to 10. By default, this parameter is set to 2, meaning that the Optimizer will dynamically sample all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.
1 or 2
OPTIMIZER_FEATURES_ENABLE Umbrella parameter for enabling a series of Optimizer features based on an Oracle release number Do not change
OPTIMIZER_INDEX_CACHING Adjusts the behavior of cost-based optimization to favor nested loops joins and IN-list iterators
Setting this parameter to a higher value makes nested loops joins and IN-list iterators look less expensive to the Optimizer. As a result, it will be more likely to pick nested loop joins over hash or sort-merge joins and to pick indexes using IN-list iterators over other indexes or full table scans. By default, this parameter is set to 0.
OPTIMIZER_INDEX_COST_ADJ Tunes the Optimizer behavior for access path selection to be more or less index friendly—that is, makes the Optimizer more or less prone to selecting an index access path over a full table scan 10-30