Some important tuning parameters in Oracle 12c

Here are some parameters that you will have to deal with when tuning an Oracle 12c database. Execute the following SQL to view their current setting:

col name format a40
col value format a20
col description format a80
set lines 200

SELECT NAME,VALUE,DESCRIPTION 
FROM   V$SYSTEM_PARAMETER
WHERE NAME IN ( 'optimizer_adaptive_features',
                'optimizer_adaptive_reporting_only',
                'optimizer_features_enable',
                'optimizer_use_sql_plan_baselines',
                'optimizer_capture_sql_plan_baselines',
                'optimizer_dynamic_sampling',
                'optimizer_adaptive_plans',
                'optimizer_adaptive_statistics');

OPTIMIZER_ADAPTIVE_FEATURES enables or disables all of the adaptive optimizer features, including

* adaptive plan (adaptive join methods and bitmap pruning)
* automatic re-optimization
* SQL plan directives
* adaptive distribution Methods

OPTIMIZER_DYNAMIC_SAMPLING controls both when the database gathers dynamic statistics, and the size of the sample that the optimizer uses to gather the statistics.

Dynamic statistics were called dynamic sampling in versions prior to 12c.

Range of values: 0 to 11. Default depends on the value of the parameter optimizer_features_enable:

• If OPTIMIZER_FEATURES_ENABLE is set to 10.0.0 or higher, then 2 
• If OPTIMIZER_FEATURES_ENABLE is set to 9.2.0, then 1 
• If OPTIMIZER_FEATURES_ENABLE is set to 9.0.1 or lower, then 0

At level 2, dynamic statistics are used "if at least one table in the statement has no statistics".

At level 4, Oracle is less restrictive and will use dynamic statistics when "at least one table in the statement has no statistics, the statement has one or more expressions used in the WHERE clause predicates... or the statement uses complex predicates".

When this parameter is set to 11, the optimizer will use dynamic statistics to verify cardinality estimates for all SQL operators, and it will determine an internal time limit to spend verifying the estimates.

Notice that Oracle says the following about level 11:

There are cases where the optimizer will automatically decide to use 11, for example:

•The query will run in parallel.
•The query was executed before and its history is available (from the cursor cache, Automatic Workload Repository, or the SQL Management Base).


From personal experience, this parameter is important! If it's turned off, I would recommend setting it to the default value. I have seen many cases where queries will actually perform better only by setting this parameter from 0 (off) to the default value.

Read more about the different Levels:
Oracle 12cR1
Oracle 12cR2

The two parameters appearing last in the list, optimizer_adaptive_plans and optimizer_adaptive_statistics, were added in 12.2, and replace the parameter optimizer_adaptive_features.