What’s new ?

• Oracle 12c introduces a major update called Adaptive Query Optimization which is based on :
  • Adaptive execution plans
  • Adaptive Statistics
• These two functionnalities are used to improve execution plans by using dynamic statistics gathered during the first part of the SQL execution. This allow to create more efficient plans that those using only the existing statistics which could be inaccurate or insufficient.
• Oracle 12c introduce 2 new types of histograms
• Finally there are significant improvements on SQL Plan Management.

Adaptive Query Optimization

Adaptive Plans

• The adaptive execution plan feature is meant to solve problems caused by inexact or incomplete statistics leading the Oracle Optimizer to create suboptimal execution plans.
• Instead of using predifined execution plan, the Oracle Optimizer postpone the final decision to the first part of the sql execution. At this time the optimizer can make adjustement to the plan, mostly change the join method  which could result in better execution plans.

Adaptive Statistics

Dynamic Statistics

Dynamic it was called in previous versions Dynamic Sampling. In Oracle 12c the optimizer choose at execution time if it needs additionnal statistics about join cardinalities an table statistics, these statistics are stored to be used for subsequent execution.

The parameter OPTIMIZER_DYNAMIC_SAMPLING let you define at instance or session level when dynamic statistics should be gathered :

• If defined at 2 (which is the default) dynamics statistics will be gathered if at leat one table in the query has no statistics.
• If defined to 11 the database will use dynamic statistics  automatically when statistics are missing, statistics are stale, statistics are insufficient.
• If defined to 0, the dynamic statistics gathering is disabled.

Automatic reoptimization

Certain parts of an execution plan can be changed on the fly but it is not feasible for all parts, for example changing the join order. For those plan, the automatic reoptimization change the plan after the first SQL execution for a better performance in subsequent executions.

Statistics feedback

During the first execution of an SQL Statement Oracle can use monitoring statistics when:

• Some Tables have no statistics
• There are multiple conjunctive or disjunctive filter predicates on a table
• The predicates containing complex operators for which the optimizer cannot accurately compute selectivity estimates

When the first execution ends, the optimizer compares the existing cardinality estimates with the monitoring statistics cardinality estimates. If a significant difference is found, the correct estimates is stored to be reused for subsequent executions of the query.

After the first execution the optimizer also created a SQL plan directive to allow other statements to use the correct statistics.

Performance feedback

The performance feedback is directly related to the parameter PARALLEL_DEGREE_POLICY. When set to ADAPTIVE, this optimizer can choose the degree of parallelism to use for a statement.

After the first execution, the optimizer compares the degree of parallelism chosen by the optimizer with the one computed based on the actual query execution performance. If they differ significantly, the optimizer marks the statement for reparsing  and stores this information for use in subsequent executions.

Default value for the PARALLEL_DEGREE_POLICY parameter is MANUAL, this disables the automatic degree of parallelism.

SQL Plan directives

The SQL plan directives are additional information used by the optimizer to generate better execution plans.

During an SQL execution, if the optimizer notice a misestimation of the cardinalities it create SQL plan directives, these directives will be used during the SQL compilation to gather addition informations like extended statistics or missing histograms.

SQL plan directives are not related to a single statement, they can also be used for nearly identical statements.

Here are some examples for a better understanding of how the SQL plan directives are used.

Existing SQL plan directives can be consulted in the DBA_SQL_PLAN_DIRECTIVES and DBA_SQL_PLAN_DIR_OBJECTS views.

They can also be managed using the packahe DBMS_SPD :

• The FLUSH_SQL_PLAN_DIRECTIVE manually flushs the SQL Plan directives recorded in the SGA.
• The DROP_SQL_PLAN_DIRECTIVE can be used to drop a sql plan directive.
• The ALTER_SQL_PLAN_DIRECTIVE is used to change attributes for a SPD.

Enhanced statistics gathering

Online statistics gathering for bulk load operations

Online statistics gathering is now enabled for during bulk load operations. This eliminate the need to perform a full table scan to gather the statistics after the table is loaded.

Concurrent statistics gathering

The statistics gathering can now be performed for multiple objects concurrently using different cpus. The DBMS_STATS package creates multiple DBMS_SCHEDULER jobs and add them to a queue list using the Advanced Queuing (AQ) mechanism.  By default the  the concurrent statistics gathering is disabled, you have to enable it using

BEGIN
    DBMS_STATS.SET_GLOBAL_PERFS('CONCURRENT','ALL');
END;
/

The concurrent attributes can have the following values :

• MANUAL : enabled only for manual statistics gathering
• AUTOMATIC : enabled only for automatic statistics gathering
• ALL : enables for all statistics gathering

New Historgrams

New TOP-n frequency histogram and Hybrid Histogram are introcuced in 12c

Better extended statistics

Extended statistics have been introduced in Oracle 12c to solve inacurrate cardinalities when queries are based on predicates of multiple columns of the same table.

To improve the cardinality estimates extended statistics are computed for groups of columns. The optimizer can choose to gather extended statistics based on a specific worload (SQL Tuning set).

To enable Workload Monitoring you can use the following command:

SQL> exec dbms_stats.seed_col_usage(null,null,600);

This will enable the monitoring for 10 minutes (600 seconds) and store the column usage information in the data dictionary.

You can report the column usage of a specific table with:

SQL> select dbms_stats.report_col_usage('SCHEMA','TABLE_NAME') from dual;

LEGEND:
.......
 
EQ         : Used in single table EQuality predicate
RANGE      : Used in single table RANGE predicate
LIKE       : Used in single table LIKE predicate
NULL       : Used in single table is (not) NULL predicate
EQ_JOIN    : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER     : Used in single table FILTER predicate
JOIN       : Used in JOIN predicate
GROUP_BY   : Used in GROUP BY expression
...............................................................................
 
###############################################################################
 
COLUMN USAGE REPORT FOR SCHEMA.TABLE_NAME
.........................................
 
1. COUNTRY_ID                          : EQ
2. CUST_CITY                           : EQ
3. CUST_STATE_PROVINCE                 : EQ
4. (CUST_CITY, CUST_STATE_PROVINCE,
    COUNTRY_ID)                        : FILTER
5. (CUST_STATE_PROVINCE, COUNTRY_ID)   : GROUP_BY
###############################################################################

 Adaptive SQL Plan management

•  The SQL Plan management is meant to guarantee that the performance of a sql statement doesn’t degrade due to an execution plan change.
• An history of the plans is stored for sql statement executed more than once. The history contains the SQL_TEXT. bind variables and the compilation environment. This information can be use to recreate an execution plan.
• When a new plan is generated for a SQL statement, the optimizer verify with historical plan information that the new plan is not causing a performance degradation.
• To activate this feature, you have to set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE (default is FALSE).
• During the database maintenance window, the SQL tuning job perform the verification of the new plan using DBMS_SPM.EVOLVE_SQL_PLAN_BASELINES and focuses on expensive sql statements first.
• To display a sql plan baseline you can use the DBMS_XPLAN.DISPLAY_PLAN_BASELINE function.
• The process of capture, selection and evolution of the plan is now managed automatically.