12c Database : Adaptive Query Optimization

Before to 12c, all the sql tuning features like ACS, SQL Profiles, SQL Outlines, SPM etc are for proactive tuning and plan stability. There is no way around to decide and change the optimizer decision on fly during the execution of a sql statement.

Since 12c, Oracle introduced the set of capabilities called "adaptive query optimization" that changes the optimizer decision on fly during the execution of a statement. This means the execution plan may initially choose hash join but if that is not correct this may change on fly to nested loops. Further more is that now all execution plans and statistics can be stored in SYSAUX tables and if a query runs next time it will compare the previous execution statistics and can change the execution plan or can choose to collect statistics on fly etc using a set of directive

Let's look closely what does they mean to us.
To start using this capabilities, the following parameters must be set

OPTIMIZER_FEATURES_ENABLE set to 12.1.0.1 above
OPTIMIZER_ADAPTIVE_REPORTING_ONLY set to false
optimizer_adaptive_features set to TRUE

If the above are set, there are four enhancements you will see Oracle 12c uses.

1) Adapative Plans
2) SQL Plan directives
3) Dynamic statistics
4) Automatic re-optimization

To start with I will represent with a diagram to better understand this whole features in combined.

Adaptive Plans:-
When a query runs first time, Optimizer usually estimates and present a optimal plan based on the statistics available. Ocassionaly (not true majority :)) optimizer picks a suboptimal plan due to some misestimates. So the query run time or performance can impact. What if , if the plan can adapt a new join method i.e switch basically a new plan on fly. If so, the optimizer defer the estimated and changes it run time. The estimations that has been kept in the plan will be discarded and uses new final plan during execution.

The ability to adapt the plan at run time based on actual execution statistics results in a more optimal final plan. After choosing the final plan, the optimizer uses it for subsequent executions, thus ensuring that the suboptimal plan is not reused.

	
	Comes in two different flavour's
	1) Influencing join methods by switch the nested loops to hash join and vice versa
	2) Influencing the parallel distribution method by introducing hybrid hash mechanism
	
	One can find whether the plan is adapted, it can be two ways
	a) The note section of a execution plan contains the following
		- adaptive plan has been used
	b) v$sql.is_resolved_adaptive_plan = Y means the query is adaptive

Automatic Reoptimization:-

As you see above, The optimizer defers the plan and re-assess the join methods from nested loops to hash join or vice versa (as of now 12c Release 1). But consider a scenario, a join order situation which perform sub optimally due to cardinality misestimate in those cases Oracle optimizer now utilizes the technique called Automatic re-optimization. Adaptive plans will change in first execution itself where in the automatic re-optimization works for the second time of execution of statement.

Earlier to 12c, the execution statistics were not persistent and not available to the optimizer immediately, now they store in sysaux tablespace and optimizer quickly get them and use them for further to improvise the plans. It will try to optimize every time the statement runs and keep storing the information unlike discarding the old plans. So this can be steps (as of my understanding)

a) When a statement executes first time,

1) Optimizer estimates the plan and execute it

2) At end, optimizer compare the execution statistics with estimated statistics

3) If it found differs, (for example e-rows differs than a-rows), then optimizer marks it as re-optimization for next run

4) Creates SQL Plan directives and stores

b) Upon next run, The optimizer see that this statement is need to reoptimized and see what kind of optimization technique it should use. It can use either of the following depending on cases.

a) For example, if a object has missing stats, and dynamic sampling set to 11(new) level then it uses the feature "Dynamic statistics" and gather the statistics run time

b) For example, if a cardinality misestimate happens in a join order, then it uses the technique called "Statistics feedback(formerly known as cardinality feedback) or "performance feedback" i.e switching from serial to parallel execution (Adaptive parallelism if DOP set as adaptive)

Note: In both the cases above SQL Plan directives that gathered for previous execution will be used and performed accordingly.

Finally, the statement will be executed with new plan and optimizer again compare the execution statistics and estimated statisitcs, if it found both same it marks this statement is_reoptimizable as NO. If it's not then the above cycle continues. Optimizer learns everytime and tries to reoptimize the statement everytime. (ofcourse many child cursors, many parses etc)

SQL Plan Directives:-

A SQL plan directive is additional information that the optimizer uses to generate a more optimal plan. For example, when the database joins two tables that have a data skew in their join columns, a SQL plan directive can direct the optimizer to use dynamic statistics to obtain an accurate cardinality estimate.The optimizer collects SQL plan directives on query expressions rather than at the statement level. In this way, the optimizer can apply directives to multiple SQL statements. The database automatically maintains directives, and stores them in the SYSAUX tablespace. You can manage directives using the package DBMS_SPD.

By default every 15 minutes this sql plan directives are flushed to sysaux as they collect automatically.

Columns V$SQL=is_reoptimizable columns value 'Y' tells whether this query needs reoptimization if so the next execution will use all sorts of reoptimization techninques using SQL Plan directives exists.

You can view DBA_SQL_PLAN_DIRECTIVES, DBA_SQL_PLAN_DIR_OBJECTS to view the objects used in statement were been used of which technique.

Dynamic Statistics (Dynamic sampling) Enhancements:-

When an object statistics missing during execution, Oracle optimizer will collect the statistics based on sampling of blocks (optimizer_dynamic_sampling level 2 etc). But this can be very misleading sometimes since sampling can be inaccurate.

From 12c, onwards a new level has been introduced i.e setting optimizer_dynamic_sampling to level 11) causes optimizer "AUTOMATICALLY DETERMINES " to collect the statistics run time of execution which eases your job if you are unsure of the data trend and tries to utilize the new statistics. Albeit the query execution time will increase ofcourse.

Next Posts on closely looking at each of this options:-