Oracle: Adaptive query optimization

Adaptive query optimization complements using statistics when generating an optimal plan to execute an SQL statement.

The two main areas of adaptive query optimization are

• Adaptive SQL plans
• Adaptive statistics

With adaptive SQL plans, the statement executor can adjust an SQL execution plan at run-time. Areas where such adaptions are possible include

• join methods (switch from a hash join to a nested loop join or vice versa)
• Parallel distribution methods
• Bitmap pruning

Adaptive statistics deals with

• Dynamic statistics
• Automatic reoptimization
• SQL plan directives

The optimizer chooses an adaptive plan when the SQL statement's joins and predicates reach a level of complexity that make an accurate cardinality estimation difficult if not impossible.

In such a case, the optimizer injects a statistics collector operation into its chosen plan.

In order to see all operations of an adaptive plan with xplan.display_cursor, the format parameter must include adaptive:

select * from table(dbms_xplan.display_cursor(format => 'adaptive'));

Adaptive plan generation can be turned of using opt_param hint like so: opt_param('_optimizer_adaptive_plans', 'false').