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.
- Adaptive Plans
- Adaptive Statistics
- Updates in 12.2
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.
OPTIMIZER_ADAPTIVE_PLANS: Default (TRUE). Enables/disables adaptive plans, star transformation bitmap pruning and the adaptive parallel distribution method.
OPTIMIZER_ADAPTIVE_STATISTICS: 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.
-- 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:
- Adaptive Query Optimization
- Optimizer with Oracle Database 12c
- Recommendations for Adaptive Features in Oracle Database 12c Release 1 (12.1) (Doc ID 2187449.1)
Hope this helps. Regards Tim...