Oracle 12c SQL Plan Directives with Oracle Optimizer

Version 6
    By Wissem El Khlifi, Oracle ACE

     

    Introduction

     

    In previous releases the database stored compilation and execution statistics in a shared sql area which is non persistent. Starting in 12c the database can use a sql plan directive, which is additional information and instructions that the optimizer can use to generate a better execution plan. SQL plan directive may instruct the optimizer to record a column groups and expressions statistics (record extended statistics).

     

    For example, a sql plan directive might instruct the optimizer to:

    • Collect missing statistics
    • Create column group statistics
    • Perform dynamic statistics (Dynamic Sampling).

     

    During sql compilation or execution, the database analyzes the query that is missing statistics or that miss-estimated for cardinality to create a sql plan directive.


    When the optimizer generates an execution plan, the directives give the optimizer additional information about objects that are referenced in the plan. SQL plan directives are not tight to a specific statement or specific sql id. Directives can be used and shared between multiple statements:

     

    • The optimizer can use directives that are collected on query expressions.
    • Directives are automatically maintained created as needed during compilation or execution (missing statistics, cardinality misestimates) and purged if not used after a year.
    • Directives can be monitored using dba_sql_plan_directives and dba_sql_plan_dir_objects dictionary tables.
    • Sql plan directives are persisted to disk in the SYSAUX tablespace; a plan directive is created and purged to disk (SYSXAUX) every 15 minutes.

     

    What Are Dynamic Statistics (Dynamic Sampling)?

     

    The Oracle optimizer uses a number of inputs to come with an optimal plan. Those inputs can be: object constraints, statistics on the tables, columns, and indexes involved in the query, system statistics (IO speed, CPU). Those inputs help the optimizer to come up with estimated cardinalities. Those cardinalities serve the optimizer to decide the cost of execution of the query and decide the execution plan. When estimated cardinalities are wrong, the optimizer will choose an inefficient execution plan. When dynamic statistics is used the optimizer may get the right estimated cardinality values or better estimated cardinalities thus the optimizer may come up with a better estimated execution cost and optimal execution plan.

     

    Dynamic statistics is used to compensate for missing or not updated statistics that would lead to a bad execution plan.

     

    Dynamic statistics are automatically used when the optimizer decides it necessary and the resulting statistics are persistent in the statistics repository making them available to other queries.

     

    In Oracle Database 12c dynamic sampling has been enhanced to become dynamic statistics.

     

    Dynamic sampling offers 12 setting levels (0 through 11). The table below shows the Dynamic Statistics Levels.

     

    References: Oracle Database SQL Tuning Guide

                                                                                                                                                                                                                                                                                                                                                                                                                  

    Level

    When the Optimizer Uses Dynamic Statistics

    Sample Size (Blocks)

    0

    Do not use dynamic statistics

    n/a

    1

    Use dynamic statistics for all tables that do not have statistics, but only if the following criteria are met:

     
    • There is at least 1 non partitioned table in the query that does not have statistics.
    • This table has no indexes.
    • This table has more blocks than the number of blocks that would be used for dynamic statistics of this table.

    32

    2

    Use dynamic statistics if at least   one table in the statement has no statistics. This is the default setting.

    64

    3

    Use dynamic statistics if any of   the following conditions is true:

     
    • The statement meets level 2 criteria.
    • The statement has one or more expressions used in the WHERE clause predicates,        for example, WHERE SUBSTR (CUSTLASTNAME, 1, 3).

    64

    4

    Use dynamic statistics if any of   the following conditions is true:

     

    • The statement meets level 3 criteria.
    • The statement uses complex predicates (an OR or AND operator between multiple predicates on the same table).

    64

    5

    Use dynamic statistics if the statement meets level 4 criteria.

    128

    6

    Use dynamic statistics if the statement meets level 4 criteria.

    256

    7

    Use dynamic statistics if the statement meets level 4 criteria.

    512

    8

    Use dynamic statistics if the statement meets level 4 criteria.

    1024

    9

    Use dynamic statistics if the statement meets level 4 criteria.

    4086

    10

    Use dynamic statistics if the statement meets level 4 criteria.

    All blocks

    11

    Use dynamic statistics   automatically when the optimizer deems it necessary. The resulting statistics   are persistent in the statistics repository, making them available to other   queries.

    Automatically determined

     

    There are two ways to use dynamic statistics:

      • The OPTIMIZER_DYNAMIC_SAMPLING parameter can be set at the database instance level and can also be overridden at the session level with the ALTER SESSION command.
      • The DYNAMIC_SAMPLING query hint can be added to specific queries. /*+ DYNAMIC_SAMPLING (4)*/

    How SQL Plan Directive Works

     

    To illustrate when Sql Plan Directive can be used; a user issue a query on the database. The query select a list of columns based on columns conditions.

    • User submits a query with an SQL ID: 4jm5nz5f3dwbg

                

    SELECT   CODE_COMBINATION_ID
               FROM GL_CODE_COMBINATIONS_KFV
              WHERE   CONCATENATED_SEGMENTS = :B1
              AND   CHART_OF_ACCOUNTS_ID = :B2;

     

    • About the SQl ID 4jm5nz5f3dwbg; The Optimizer determines a plan and after execution the cardinality estimation is discovered to be wrong and Dynamic Statistics (Dynamic Sampling) is recommended.

    12csqlplandirectives_1.jpg

    • User submits a similar query: say its SQL ID is 3gr4ca7f2zabd with different values in the where condition.

                

    SELECT   *
               FROM GL_CODE_COMBINATIONS_KFV
              WHERE   CONCATENATED_SEGMENTS = :B3
              AND   CHART_OF_ACCOUNTS_ID = :B4;

     

    • About the SQL ID 3gr4ca7f2zabd; The Optimizer checks on directives and finds one created on CONCATENATED_SEGMENTS and CHART_OF_ACCOUNTS_ID columns.  The optimizer decides about the plan considering the help of the SQL Plan directives.  The optimizer adds columns group creation for CONCATENATED_SEGMENTS and CHART_OF_ACCOUNTS_ID columns and gathers their statistics in the next GL_CODE_COMBINATIONS_KFV table gather statistics.

     

    12csqlplandirectives_2.jpg

     

    SQL Plan Directive Demonstration

     

    We consider the following example; first we connect to the 12c container database (CDB).

                

    12c(bicdb):/home/oracle>sqlplus   / as sysdba

     

    SQL*Plus: Release   12.1.0.2.0 Production on Sat Feb 28 12:07:57 2015

     

    Copyright (c) 1982, 2014,   Oracle.  All rights reserved.

     

     

     

    Connected to:
                Oracle Database 12c   Enterprise Edition Release 12.1.0.2.0 - 64bit Production
                With the Partitioning,   Automatic Storage Management, OLAP, Advanced Analytics
                and Real Application   Testing options

     

     

     

    SQL>

     

    We display the information about Pluggable databases (PDBs) associated with the current CDB instance.

     

    SQL> select   con_id,dbid,NAME,OPEN_MODE from v$pdbs;

        CON_ID       DBID            NAME                           OPEN_MODE
            ----------           ----------          ------------------------------ ----------
                     2            3280889553    PDB$SEED                   READ ONLY
                     3               211023784     BICLR1                          MOUNTED
                     4              428310707    BIAPPS                          READ   WRITE

     

    We connect to the Pluggable database BIAPPS;

     

    SQL> alter session set   container=BIAPPS;

    Session altered.

    We create a user “Wissem” for the test.

     


          SQL> alter user wissem   identified by wissem123;

     

    User altered.

     

    Let’s create a table called big_table which has different products, channel ids. Note the table has missing statistics; we didn’t gathered any statistics on table and columns.

     


          SQL>
          SQL> set timi on
          SQL> drop table   wissem.big_table purge;
          drop table   wissem.big_table purge
                            *
          ERROR at line 1:
          ORA-00942: table or view   does not exist

     

    Elapsed: 00:00:00.08
            SQL> create table   wissem.big_table as
              2    select
              3    'iPhone' as product,
              4    mod(rownum,5) as channel_id,
              5    mod(rownum,1000) as cust_id
              6    from dual connect by level<=2000000
              7    UNION ALL
            select
              8      9  'Motorola' as product,
             10    mod(rownum,5) as channel_id,
             11    mod(rownum,1000) as cust_id
             12    from dual connect by level<=10
             13    UNION ALL
            select
             14     15  'Nokia' as product,
             16    mod(rownum,5) as channel_id,
             17    mod(rownum,1000) as cust_id
             18    from dual connect by level<=20401
             19    UNION ALL
             20    select
             21    'Samsung' as product,
             22    mod(rownum,5) as channel_id,
             23    mod(rownum,1000) as cust_id
             24    from dual connect by level<=1000000;

    Table created.

    Elapsed: 00:00:06.77
            SQL>

     

    We issue a query on big table specifying conditions on both product and channel columns.

     


          set autotrace traceonly
          select   /*+gather_plan_statistics*/
          cust_id ,   channel_id,product
          from wissem.big_table   where     
          product = 'Motorola' and   channel_id=1
          order by product
          ;

     

    Note we use gather_plan_statistics hint which tells Oracle to collect execution statistics for a SQL statement. After that we call the function DBMS_XPLAN.DISPLAY_CURSOR to display the execution plan. We must give the FORMAT input parameter the value of 'ALLSTATS LAST' to show the optimizer estimated rows along with the actual rows gathered during the sql execution phase.

     

    SQL> select   /*+gather_plan_statistics*/
          cust_id ,   channel_id,product
          from wissem.big_table   where
          product = 'Motorola' and   channel_id=1
          order by product  2      3    4    5
            6  ;

       CUST_ID CHANNEL_ID PRODUCT
            ---------- ----------   --------
                     1          1 Motorola
                     6          1 Motorola

    Elapsed: 00:00:00.05
            SQL> SELECT   plan_table_output FROM table(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=>'ALLSTATS   LAST'));

    12csqlplandirectives_y.jpg


        Elapsed: 00:00:01.49
        SQL>
        SQL>

     

    From the above output, we see the estimated row (151K) is significantly different from the actual rows (2). Therefore we need to see a bad estimated plan showing up.
    When we query the v$sql dynamic view, we see the is_reoptimizable  column value set to YES which means the optimizer will use Automatic reoptimization to fix the suboptimal plan chosen due to incorrect optimizer estimates.

     

    SQL> set linesize 20000
      SQL> set pagesize 20000
      SQL> col sql_id format a20
      SQL> col child_number format 99
      SQL>  col sql_text  format a40
      SQL> col is_reoptimizable format a20
      SQL>  select sql_id, child_number, sql_text, is_reoptimizable from v$sql where sql_text like '%gather_plan_statistics%';

     

    12csqlplandirectives_3.jpg

     

    Let’s flush the actual directive to the SYSAUX tablespace. Remember that the plan directives are purged to disk (SYSXAUX) every 15 minutes.
    We call the dbms_spd.flush_sql_plan_directive procedure.

     

    SQL> exec   dbms_spd.flush_sql_plan_directive;

    PL/SQL procedure   successfully completed.

    Elapsed: 00:00:00.47
            SQL>

     

    We can select the sql plan directives created in the example, but making a join between dba_sql_plan_directives and dba_sql_plan_dir_objects tables.
      We specify the owner ; in this case the user Wissem.

     


          select   to_char(d.directive_id) directive_id,
                 o.owner as owner,
                 o.object_name as object_name,
                 o.subobject_name as subobject_name,
                 o.object_type,
                 d.type,
                 d.state,
                 d.reason
            from dba_sql_plan_directives d,
                 dba_sql_plan_dir_objects o
           where d.directive_id = o.directive_id
             and o.owner = 'WISSEM';

     

    12csqlplandirectives_4.jpg

     

    From the output above you see that the plan directive is created because of cardinality miss estimation for the table big table. Oracle has defined dynamic statistics (dynamic sampling) on both product and channel columns.

     

    So let's run again the query and we would expect to have dynamic statistics (dynamic sampling) used and plan directives help the optimizer to adjust the execution plan.

     

    SQL> select   /*+gather_plan_statistics*/
          cust_id ,   channel_id,product
          from wissem.big_table   where
          product = 'Motorola'
           and channel_id=1
          order by product   ;

       CUST_ID CHANNEL_ID PRODUCT
            ---------- ----------   --------
                     1          1 Motorola
                     6          1 Motorola

    Elapsed: 00:00:00.14

     

    12csqlplandirectives_5-1.jpg

    We see estimated rows are 10 and the actual rows returned are 2. With the help of the Dynamic statistics the optimizer estimated nearly correct cardinalities.
    From the output above, you can see one sql plan directive is used the statement and dynamic sampling is set to 2.
    We query again the v$sql.

     

    SQL> select sql_id,   child_number, sql_text, is_reoptimizable from v$sql where sql_text like   '%gather_plan_statistics%';

    12csqlplandirectives_x.jpg
             SQL>

     

    From the output above we see a different sql id assigned and the is_reoptimizable  column value set to YES which means the optimizer will use Automatic reoptimization to fix the suboptimal plan chosen due to incorrect optimizer estimates.

    We change now the value of product from Motorola to Nokia and we check if the Optimizer will still use the plan directive and dynamic sampling

     

    SQL>
          select /*+gather_plan_statistics*/
          cust_id ,   channel_id,product
          from wissem.big_table   where
          product = 'Nokia' and   channel_id=1
          order by product ;

     

       CUST_ID CHANNEL_ID PRODUCT
            ---------- ----------   --------
                   351          1 Nokia
                   356          1 Nokia
                   361          1 Nokia
          

    .....
            ..... => content   truncated , too many rows returned
            .....
            4081 rows selected.

    Elapsed: 00:00:01.14
            SQL>

     

    SQL>  SELECT plan_table_output FROM   table(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=>'ALLSTATS LAST'));

     

    12csqlplandirectives_6.jpg
         

     

    We can see that the optimizer uses sql plan directive for the new sql statement and dynamic sampling is set to 2.

    Conclusion:


    In this article, we have see how sql plan directive can help the Oracle optimizer with additional information, column group statistics and instructions on missing statistics, cardinality misestimates so the optimizer can generate a better cardinality estimation and better execution plan.

     


    About the Author

    Wissem is a Senior DBA with over 12 years of experience specialized in Oracle HA solutions / Big Data. He works for the “Schneider Electric / APC Global operations”. Wissem has worked as well for several international enterprises leaders in sectors of Banks, Telecommunications, Internet and Energy. Wissem is the first Oracle ACE in Spain and He is OCP DBA. Follow Wissem on the Oracle Community Platform at orawiss