8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

Adaptive Query Optimization in Oracle Database 12c (12.1 and 12.2)

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.

Updates in 12.2

In Oracle 12.1 the adaptive optimizer features are controlled by the OPTIMIZER_ADAPTIVE_FEATURES parameter, which was set to TRUE by default.

-- 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;

In Oracle 12.2 the OPTIMIZER_ADAPTIVE_FEATURES parameter has been removed and replace by two new parameters.

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.

-- 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;

This change in the control of the adaptive optimizations has been back-ported to 12.1. You can read about it here.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.