Adaptive query optimization in 12c allows for changes to execution plan at run time and gathering of new information to generate better statistics.  The two aspects of Adaptive Query Optimization:

1. Adaptive Plan – Improving the initial execution of query at run time.
2. Adaptive Statistics – Gathering new statistics to improve future executions.

 

Adaptive Plans – allows optimizer to make decision on SQL at runtime and in mid execution.  It will actually make sub-plans and collect statistics to make final decision on execution.

1. This may cause a difference in the EXPAIN PLAN command and the DBMS_XPLAN.DISPLAY_CURSOR where the EXPLAIN PLAN will show the initial plan execution and DISPLAY_CURSOR will show the final plan executed.
2. In oracle 12c only Join and Parallel queries are supported of adaptive plans.
3. You can actually see all plans tried with the DBMS_XPLAN function by using the ‘+adaptive’ parameter with the notation (-) against the id column of plan table.
4. V$SQL view has new column IS_RESOLVED_ADAPTIVE_PLAN which show if SQL has an adaptive plan. If it ‘Y’ the statement has adaptive plan which has been used, if ‘N’ it has an adaptive plan that is not completed yet and if NULL it has no adaptive plan.
5. INIT parameter OPTIMIZER_ADAPTIVE_REPORTING_ONLY is set to TRUE (default is FALSE) information on adaptive plan will be collected but not used. You can then use DBMS_XPLAN plan to view data with ‘+report”.

 

Adaptive Statistics:

Optimizer uses statistics in order to create good execution plans.  Re-optimization uses the execution of previous statements results to determine if the executed plan was optimal and adjusts plans for future execution in an iterative process.

Three types of re-optimization:

1. Statistics Feedback – also known as cardinality feedback, it compares cardinality of estimated and actual executions. Storing the correct cardinality statistics and creates SQL plane directive for future use.
2. Performance Feedback – Used to improve degree of parallelism for future SQL statements based on previous executions when Automatic Degree of Parallelism (AutoDOP) is enabled in adaptive mode.
3. SQL plan directives – Is additional information used by optimizer to determine correct execution plans and are created on query expressions not SQL statements thus can be applied to multiple SQL statements. Multiple directives can be used for single SQL statement.  SQL directives are purged if not used in 53 days.  You can see directives in views:   DBA_SQL_PLAN_DIRECTIVES and DBA_SQL_PLAN_DIR_OBJECTS.

 

 

 

Partition table statistics – Two sets of statistics are gathered at the partition level and table level, in 12c the table level statistics are aggregated by partition level avoiding full table scan.

 

INCREMENTAL_STALENESS – preference setting in DBMS_STATS.SET_TABLE_PREFS allows you to determine when statistics on tables are stale by percentage of change with USE_STALE_PERCENT or locking statistics forcing their use regardless of how much change has occurred with USE_LOCKED_STATS.

 

Incremental statistics improved by EXCHANGE_PARTITION command allows you to statistics from non-partitioned table with those in a partition.  Prior to 12c you had to gather data on all partitions before using incremental global statistics.   Use the DBMS_STATS package preference of INCREMENTAL_LEVEL set to TABLE (the default is PARTITION) to use this EXCHANGE_PARTITION feature.

 

Concurrent Statistics – when global statistics gathering preference CONCURRENT is set, the Job Scheduler and Advance queuing gather states on table/partitions concurrently.  12c has optimized automatic statistics gathering by batching smaller jobs to run together.  When preference CONCURRENT is set to ALL or AUTOMATIC stats a gathered nightly.

 

 

Automatic column group detection – Gathering of statistics on columns used in filter predicates by estimating cardinality.   Determining columns to gather increased stats is determined by workload history.  Three step process determines Auto Column Group detection:

1. Seed column usage – workload history via the DBMS_STATS.SEED_COL_USAGE procedure.
2. Create the column groups – Use the DBMS_STATS.CREATE_EXTENDED_STATS function to id columns groups based on usage information gathered. Once identified extended statistics will be automatically generated.
3. Regather statistics – Regather statistics for all tables identified for column stats extended.

 

 

Larry Catt

OCP