Adaptive Query Optimization is a term used in Oracle Database 12c to describe a collection of features that work together to allow the cost based optimizer (CBO) to improve the accuracy of execution plans. Some of the features are renamed versions of functionality from previous releases, while others are new to Oracle Database 12c. This article provides links to articles on the individual features of Adaptive Query Optimization. Although the features are discussed in separate articles, you really need to consider them in combination as there is a lot of interaction between the separate features. - Adaptive Plans Adaptive Join Methods Adaptive Parallel Distribution Methods - Adaptive Join Methods - Adaptive Parallel Distribution Methods - Adaptive Statistics Dynamic Statistics Automatic Reoptimization SQL Plan Directives - Dynamic Statistics - Automatic Reoptimization - SQL Plan Directives - Updates in 12.2 - Adaptive Join Methods - Adaptive Parallel Distribution Methods - Dynamic Statistics - Automatic Reoptimization - SQL Plan Directives In Oracle 12.1 the adaptive optimizer features are controlled by the parameter, which was set to TRUE by default.

In Oracle 12.2 the parameter has been removed and replace by two new parameters. - : Default (TRUE). Enables/disables adaptive plans, star transformation bitmap pruning and the adaptive parallel distribution method. - : Default (FALSE). Enables/disables SQL plan directives, statistics feedback for joins, performance feedback and adaptive dynamic sampling for parallel execution. Both parameters are modifiable at both the session and system level. Notice most of the new adaptive functionality is turned off by default in 12.2. The reasoning for this is many of these optimizations are more appropriate for data warehousing, where there optimization time is is a small proportion of the query runtime. In OLTP environments, where SQL runtime is typically smaller, the additional optimization time may become a significant part of the elapsed time, for little extra benefit. This change in the control of the adaptive optimizations has been back-ported to 12.1. You can read about it here.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

-- Session-level.
ALTER SESSION SET OPTIMIZER_ADAPTIVE_FEATURES = FALSE;
ALTER SESSION SET OPTIMIZER_ADAPTIVE_FEATURES = TRUE;

-- System-level.
ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_FEATURES = FALSE SCOPE=BOTH;
ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_FEATURES = TRUE SCOPE=BOTH;

-- Session-level.
ALTER SESSION SET OPTIMIZER_ADAPTIVE_PLANS = FALSE;
ALTER SESSION SET OPTIMIZER_ADAPTIVE_PLANS = TRUE;

ALTER SESSION SET OPTIMIZER_ADAPTIVE_STATISTICS = TRUE;
ALTER SESSION SET OPTIMIZER_ADAPTIVE_STATISTICS = FALSE;

-- System-level.
ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_PLANS = FALSE SCOPE=BOTH;
ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_PLANS = TRUE SCOPE=BOTH;

ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_STATISTICS = TRUE SCOPE=BOTH;
ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_STATISTICS = FALSE SCOPE=BOTH;

For more information see: Hope this helps. Regards Tim...