Caso você seja um cliente de Hospedagem gerenciada, este tópico não se aplica a você.
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.
CURSOR_SHARING settings
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 Tuning Guide.
| 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 |
ALL_ROWS |
| 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. |
80-100 |
| 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 |
