Introduction to Oracle SQL Plan Directives in Oracle Database 12.2

    Apr 13, 2017 11:26:00 AM by Deiby Gomez

    Introduction

    An Execution Plan is composed by the steps that the optimizer does in order to process a SQL statement. Oracle Optimizer always tries to find out the best execution plan for a SQL statement, taking into consideration several things, such as access paths, parallelism, statistics, histograms, bind variables, database parameters, etc. However, there are situations when Oracle Optimizer doesn’t create the right execution plan because the information that the optimizer used to create the “best plan” was not correct, not updated, or not sufficient; an example of this (but not the only reason) is when statistics are not updated (stale statistics) and the data has changed considerably in the tables involved in the SQL statement. In such cases, the SQL statement will be executed with an execution plan that the optimizer thinks  is the best, but actually it is not.

    Oracle has designed several features that make the optimizer aware that there is “something wrong” with the actual execution plan; once the optimizer is aware of that it takes “feedback” and then creates a new execution plan, or takes some others action to “adapt” itself to the environment or data change. The set of features that make the optimizer “adapt” itself to the changes in the environment (for instance, database parameters) or in the data (for instance, skewed data) are called “adaptive features”. In Oracle Database 12.1.0.1 probably the most popular words were “adaptive” and “multi-tenant”; it was the first version with several new features that included the word “adaptive”.  For instance, adaptive index compression, adaptive query optimization, adaptive plans, adaptive joins, adaptive parallel and several additional adaptive things!

    “Adaptive Features” comprise two categories: “Adaptive Plans” and “Adaptive Statistics”.

     

    In 12.1.0.1 all the “adaptive” features were controlled by the database parameter “optimizer_adaptive_features”; however, in 12.2.0.1 that changed and now the database parameter “optimizer_adaptive_features” has been broken up into two new database parameters: optimizer_adaptive_plans and optimizer_adaptive_statistics. Each parameter controls a category of Adaptive Features. The database parameter “optimizer_adaptive_features” doesn’t exist in 12.2.0.1. 

    The definition of the parameter in 12.1.0.1:

    • optimizer_adaptive_features enables or disables all of the adaptive optimizer features, including adaptive plan (adaptive join methods and bitmap pruning), automatic re-optimization, SQL plan directives, and adaptive distribution methods.

    The definition of the parameters in 12.2.0.1:

    • optimizer_adaptive_plans controls adaptive plans. Adaptive plans are execution plans built with alternative choices that are decided at runtime based on statistics collected as the query executes.
    • optimizer_adaptive_statistics controls adaptive statistics. Some query shapes are too complex to rely on base table statistics alone, so the optimizer augments these statistics with adaptive statistics.

    Oracle SQL Plan Directives is part of the category “Adaptive Statistics”.  Basically, they are notes that the optimizer writes and stores in the database to “adapt” itself to the environment or data changes. For example, if the optimizer sees that the actual rows are considerably different than the estimated rows, then the optimizer writes a note to “remember” what happened, so that in the next execution of the same SQL statement (or one with the same query expressions), the optimizer can take actions to fix it. These notes taken by the optimizer are called “SQL Plan Directives”. SQL Plan Directives are not tied to a specific SQL_ID. SQL Plan Directives are based on a query expression rather than at the SQL statement level. This makes the SQL Plan Directives usable for others SQL_IDs as long as the query expression is the same. SQL Plan Directives can be queried using the views DBA_SQL_PLAN_DIR_OBJECTS and DBA_SQL_PLAN_DIRECTIVES.

    If you want to see a comparison between SQL Plan Directives in 12.1 and 12.2 you can read this good article written by Mauro Pagano.

    Oracle automatically handles all related to SQL Plan Directives; it creates and maintains them. The only operations allowed by Oracle for SQL Plan Directives are the following:

    • Flush the SQL Plan Directives to disk.
    • Delete a SQL Plan Directive
    • Export a SQL Plan Directive
    • Import a SQL Plan Directive.

    How to flush the SQL Plan Directives to disk: When a SQL Plan Directive is created, it is created only in memory. Oracle flushes all the new SQL Plan Directives to disk every 15 minutes. However, if you want to flush the SQL Plan Directives manually you can use the following sentences:

    BEGIN
      DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE;
    END;
    /

    Delete a SQL Plan Directive: To delete a SQL Plan Directive you can use the following SQL statement where the only value requested is the ID of the SQL Plan Directive:

    SQL> exec dbms_spd.drop_sql_plan_directive ('<SPD ID>');
    PL/SQL procedure successfully completed.

    Export and Import SQL Plan Directives: SQL Plan Directives are transported to others databases following the same method that we use to transfer SQL Tuning Sets. This article doesn’t cover those steps, but for more details you can see the Metalink Note:  How to Transport SQL Plan Directives (SPD) From One Database to Another (Doc ID 2064227.1)

    Now after covering these useful concepts, let’s do an example!

    In this example I am using Oracle Database 12.2.0.1 Enterprise Edition. I have the table dgomez.employee:

    SQL> desc dgomez.employee
    Name       Null?   Type
    --------- -------- ----------------------------
    AGE                NUMBER
    NAME               VARCHAR2(20)
    COUNTRY            VARCHAR2(20)

    In the table I have only one row with the data of one employee.

    select /*+gather_plan_statistics*/ *
    from dgomez.employee e
    where e.country='Guatemala' and e.age=21;

    AGE        NAME        COUNTRY
    ---------- ----------- --------------------
    21         Deiby       Guatemala

    You can see that the Estimated Rows (E-Rows) is the same as the value of Actual Rows (A-Rows):

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

    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------
    SQL_ID bmx5dfgyzm2ag, child number 0
    -------------------------------------
    select /*+gather_plan_statistics*/ * from dgomez.employee e where
    e.country='Guatemala' and e.age=21

    Plan hash value: 2119105728

    ----------------------------------------------------------------------------------------------

    | Id | Operation        | Name     | Starts | E-Rows | A-Rows | A-Time     | Buffers | Reads |
    --------------------------------------------------------------------------------------------
    | 0  | SELECT STATEMENT |          | 1      |        | 1      |00:00:00.01 | 7       | 6     |
    |* 1 | TABLE ACCESS FULL| EMPLOYEE | 1      | 1      | 1      |00:00:00.01 | 7       | 6     |
    ----------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------
    1 - filter(("E"."COUNTRY"='Guatemala' AND "E"."AGE"=21))

    19 rows selected.

    Now let’s query the view v$sql; this view has a column called “IS_REOPTIMIZABLE”. The definition of this column is the following:

     

    We can see that the SQL statement we executed has not been marked as reoptimizable:

    select sql_id, child_number, is_reoptimizable, sql_text from v$sql where sql_text like '%dgomez%' and sql_text not like '%insert%';

    SQL_ID CHILD_  NUMBER      I SQL_TEXT
    ------------- ------------ - ----------------------------------------
    bmx5dfgyzm2ag            0 N select /*+gather_plan_statistics*/ * fro
                                 m dgomez.employee e where e.country='Gua
                                 temala' and e.age=21

    PL/SQL procedure successfully completed.

    Now I will insert several others employees in order to create a difference between the estimated rows and the actual rows. Let’s execute the SQL statement again. I would like to highlight the fact that I had to execute this SQL statement four times in order to make it reoptimizable; in some other cases I had to execute it more times and in some others less times.

    select /*+gather_plan_statistics*/ *
    from dgomez.employee e
    where e.country='Guatemala' and e.age=21;

    AGE NAME    COUNTRY
    -- -------- --------------------
    21 Jose     Guatemala
    21 Maria    Guatemala
    21 Josh     Guatemala
    21 Julio    Guatemala
    21 Pedro    Guatemala
    21 Marvin   Guatemala
    21 Oscar    Guatemala
    21 Mauricio Guatemala
    21 Gabriel  Guatemala
    21 Jonathan Guatemala
    21 Lucrecia Guatemala
    21 Alex     Guatemala
    21 Alvaro   Guatemala
    21 Alan     Guatemala
    21 Deiby    Guatemala

    15 rows selected.

    We can see now that there is a difference between the Actual Rows and the Estimated Rows:

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

    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------------------
    SQL_ID bmx5dfgyzm2ag, child number 0
    -------------------------------------
    select /*+gather_plan_statistics*/ * from dgomez.employee e where
    e.country='Guatemala' and e.age=21

    Plan hash value: 2119105728

    --------------------------------------------------------------------------------------
    | Id | Operation        | Name     | Starts | E-Rows | A-Rows | A-Time     | Buffers |
    --------------------------------------------------------------------------------------
    | 0  | SELECT STATEMENT |          | 1      |        | 15     |00:00:00.01 | 8       |
    |* 1 | TABLE ACCESS FULL| EMPLOYEE | 1      | 1      | 15     |00:00:00.01 | 8       |
    --------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------
    1 - filter(("E"."COUNTRY"='Guatemala' AND "E"."AGE"=21))

    19 rows selected.

    The SQL statement was finally marked as reoptimizable. This is because the optimizer saw that there was a difference between the estimated rows and the actual rows.

    SQL_ID CHILD_NUMBER      I SQL_TEXT

    ------------- ---------- - ----------------------------------------
    bmx5dfgyzm2ag          0 Y select /*+gather_plan_statistics*/ * fro
                               m dgomez.employee e where e.country='Gua
                               temala' and e.age=21

    PL/SQL procedure successfully completed.

    Also, the optimizer wrote some “notes” (SQL Plan Directives) to remember in the next execution that there was something wrong with the estimated rows:

    select o.directive_id id, owner, o.object_name, o.object_type, d.state, d.reason,
    d.notes from DBA_SQL_PLAN_DIR_OBJECTS o, DBA_SQL_PLAN_DIRECTIVES d where o.OWNER='DGOMEZ' and o.directive_id=d.directive_id;


    ID                   OWNER  OBJECT_NAM OBJECT_TYP STATE  REASON               NOTES
    -------------------- ------ ---------- ---------- ------ -------------------- --------------------
    14767378624474121740 DGOMEZ EMPLOYEE   COLUMN     USABLE SINGLE TABLE CARDINA <spd_note>
                                                                                  LITY MISESTIMATE <internal_state>NE
                                                                                  W</internal_state>
                                                                                  <redundant>NO</red
                                                                                  undant>
                                                                                  <spd_text>{EC(DGOM
                                                                                  EZ.EMPLOYEE)[AGE, CO
                                                                                  UNTRY]}</spd_text>
                                                                                  </spd_note>

    14767378624474121740 DGOMEZ EMPLOYEE COLUMN       USABLE SINGLE TABLE CARDINA <spd_note>
                                                                                  LITY MISESTIMATE <internal_state>NE
                                                                                  W</internal_state>
                                                                                  <redundant>NO</red
                                                                                  undant>
                                                                                  <spd_text>{EC(DGOM
                                                                                  EZ.EMPLOYEE)[AGE, CO
                                                                                  UNTRY]}</spd_text>
                                                                                  </spd_note>

    14767378624474121740 DGOMEZ EMPLOYEE TABLE        USABLE SINGLE TABLE CARDINA <spd_note>
                                                                                  LITY MISESTIMATE <internal_state>NE
                                                                                  W</internal_state>
                                                                                  <redundant>NO</red
                                                                                  undant>
                                                                                  <spd_text>{EC(DGOM
                                                                                  EZ.EMPLOYEE)[AGE, CO
                                                                                  UNTRY]}</spd_text>
                                                                                  </spd_note>

    The TYPE of this Directive DYNAMIC_SAMPLING:

    SQL> select o.directive_id id, d.type from DBA_SQL_PLAN_DIR_OBJECTS o,
    DBA_SQL_PLAN_DIRECTIVES d where o.OWNER='DGOMEZ' and o.directive_id=d.directive_id;


    ID                   TYPE
    -------------------- -----------------------
    14767378624474121740 DYNAMIC_SAMPLING
    14767378624474121740 DYNAMIC_SAMPLING
    14767378624474121740 DYNAMIC_SAMPLING

    You can see several rows returned, but if you look at the “ID” column, you will see that only one SQL Plan Directive was created.

    Now let’s execute again the SQL statements and let’s see what happens:

    select /*+gather_plan_statistics*/ *
    from dgomez.employee e
    where e.country='Guatemala' and e.age=21;

    AGE NAME     COUNTRY
    --- -------- --------------------
    21  Jose     Guatemala
    21  Maria    Guatemala
    21  Josh     Guatemala
    21  Julio    Guatemala
    21  Pedro    Guatemala
    21  Marvin   Guatemala
    21  Oscar    Guatemala
    21  Mauricio Guatemala
    21  Gabriel  Guatemala
    21  Jonathan Guatemala
    21  Lucrecia Guatemala
    21  Alex     Guatemala
    21  Alvaro   Guatemala
    21  Alan     Guatemala
    21  Deiby    Guatemala

    15 rows selected.

    The SQL Plan Directive was used, as well as Dynamic Sampling Statistics, which made the optimizer fix the difference between estimated rows and actual rows. With the help of SQL Plan Directives, the optimizer was able to adapt itself to the change; in this case, a change in the data (several more rows were inserted). In this example Dynamic Sampling Statistics was used, but SQL Plan Directives can remind the optimizer to take other actions in addition to Dynamic Sampling Statistics. (At least, it was designed to have more TYPES, but at this time Dynamic Sampling Statistics (and its sub type DYNAMIC_SAMPLING_RESULT) is the only TYPE existing, as Mauro Pagan explains in this presentation.)  

    PLAN_TABLE_OUTPUT

    --------------------------------------------------------------------------------------
    SQL_ID bmx5dfgyzm2ag, child number 0
    -------------------------------------
    select /*+gather_plan_statistics*/ * from dgomez.employee e where
    e.country='Guatemala' and e.age=21

    Plan hash value: 2119105728

    -------------------------------------------------------------------------------------
    | Id | Operation        | Name     | Starts | E-Rows | A-Rows | A-Time     | Buffers |
    -------------------------------------------------------------------------------------
    | 0  | SELECT STATEMENT |          | 1      |        | 15     |00:00:00.01 | 8       |
    |* 1 | TABLE ACCESS FULL| EMPLOYEE | 1      | 15     | 15     |00:00:00.01 | 8       |
    -------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    1 - filter(("E"."COUNTRY"='Guatemala' AND "E"."AGE"=21))

    Note
    -----
    - dynamic statistics used: dynamic sampling (level=2)
    - 1 Sql Plan Directive used for this statement

    24 rows selected.

    And after reoptimizing the SQL Statement, the query is now marked as not reoptimizable:

    SQL_ID CHILD_ NUMBER I SQL_TEXT
    ------------- ------ - ----------------------------------------
    bmx5dfgyzm2ag      0 N select /*+gather_plan_statistics*/ * fro
                           m dgomez.employee e where e.country='Gua
                           temala' and e.age=21

    PL/SQL procedure successfully completed.

    A new note was added to the same SQL Plan Directive.  

    select o.directive_id id, owner, o.object_name, o.object_type, d.state, d.reason,
    d.notes from DBA_SQL_PLAN_DIR_OBJECTS o, DBA_SQL_PLAN_DIRECTIVES d where o.OWNER='DGOMEZ' and o.directive_id=d.directive_id;


    ID                   OWNER  OBJECT_NAM OBJECT_TYP STATE  REASON               NOTES
    -------------------- ------ ---------- ---------- ------ -------------------- --------------------
    14767378624474121740 DGOMEZ EMPLOYEE   COLUMN     USABLE SINGLE TABLE CARDINA <spd_note>
                                                                                  LITY MISESTIMATE <internal_state>MI
                                                                                  SSING_STATS</interna
                                                                                  l_state>
                                                                                  <redundant>NO</red
                                                                                  undant>
                                                                                  <spd_text>{EC(DGOM
                                                                                  EZ.EMPLOYEE)[AGE, CO
                                                                                  UNTRY]}</spd_text>
                                                                                  </spd_note>

    14767378624474121740 DGOMEZ EMPLOYEE   COLUMN     USABLE SINGLE TABLE CARDINA <spd_note>
                                                                                  LITY MISESTIMATE <internal_state>MI
                                                                                  SSING_STATS</interna
                                                                                  l_state>
                                                                                  <redundant>NO</red
                                                                                  undant>
                                                                                  <spd_text>{EC(DGOM
                                                                                  EZ.EMPLOYEE)[AGE, CO
                                                                                  UNTRY]}</spd_text>
                                                                                  </spd_note>

    14767378624474121740 DGOMEZ EMPLOYEE   TABLE      USABLE SINGLE TABLE CARDINA <spd_note>
                                                                                  LITY MISESTIMATE <internal_state>MI
                                                                                  SSING_STATS</interna
                                                                                  l_state>
                                                                                  <redundant>NO</red
                                                                                  undant>
                                                                                  <spd_text>{EC(DGOM
                                                                                  EZ.EMPLOYEE)[AGE, CO
                                                                                  UNTRY]}</spd_text>
                                                                                  </spd_note>

    7617691850148384040 DGOMEZ EMPLOYEE   TABLE       USABLE VERIFY CARDINALITY E <spd_note>
                                                      STIMATE                     <internal_state>NE
                                                                                  W</internal_state>
                                                                                  <redundant>NO</red
                                                                                  undant>
                                                                                  <spd_text>{(DGOMEZ
                                                                                  .EMPLOYEE, num_rows=
                                                                                  15) - (SQL_ID:2zbnc0
                                                                                  ugm2qzy, T.CARD=15[-
                                                                                  2 -2])}</spd_text>
                                                                                  </spd_note>

    This last SQL Plan Directive is of type “DYNAMIC_SAMPLING_RESULT”:

    SQL> select to_char(o.directive_id) id, d.type from DBA_SQL_PLAN_DIR_OBJECTS o,
    DBA_SQL_PLAN_DIRECTIVES d where o.OWNER='DGOMEZ' and o.directive_id=d.directive_id;

     
    ID                                     TYPE
    -------------------------------------- -----------------------
    14767378624474121740                   DYNAMIC_SAMPLING
    14767378624474121740                   DYNAMIC_SAMPLING
    14767378624474121740                   DYNAMIC_SAMPLING
    7617691850148384040                    DYNAMIC_SAMPLING_RESULT

    How to disable SQL Plan Directives: If you want to disable only SQL Plan Directives you can set the following parameters to ‘0’:

    This will stop creation of new SQL Plan Directives:

    SQL> alter system set "_sql_plan_directive_mgmt_control"=0;
    System altered.

    This will stop using existing SQL Plan Directives:

    SQL> alter system set "_optimizer_dsdir_usage_control"=0;
    System altered.

    You can disable SQL Plan Directives indirectly if you set the following parameter:

    • optimizer_adaptive_reporting_only = “TRUE”.
    • optimizer_features_enable < 12.1.0.1

    How to disable all the features in “Adaptive Statistics”:

    SQL> alter system set optimizer_adaptive_statistics=false;
    System altered.

    How to disable all the features in “Adaptive Plans”:

    SQL> alter system set optimizer_adaptive_plans=false;
    System altered.

    How to enable SQL Plan Directives:

    • The parameter _sql_plan_directive_mgmt_control must not be set to 0
    • The parameter _optimizer_dsdir_usage_control must not be set to 0.
    • The parameter optimizer_adaptive_statistics must be set to “TRUE”.
    • The parameter optimizer_adaptive_reporting_only    must be set to “FALSE”.
    • The parameter optimizer_features_enable must be set to a value >= 12.1.0.1

     

    Conclusion

    Oracle Database has been improving its features with every version, and in 12c several adaptive features were introduced. SQL Plan Directives are notes that help the optimizer remember things in the next execution; this allows the optimizer to adapt to changes. We saw a step-by-step example in this article, and we explained how to enable and disable SQL Plan Directives and the others database parameters related to adaptive features.

    Tags: Oracle

    Deiby Gomez

    Written by Deiby Gomez

    Deiby Gómez is the youngest Oracle ACE and Oracle ACE Director in the world and the first Guatemalan with these awards. Deiby is the youngest Latin American with the highest certifications “Oracle Certified Master 11g” and “Oracle Certified Master 12c”. He received In Vegas, United States the "SELECT Journal Editor’s Choice Award 2016", he became the first Guatemalan with that award. He is a frequent speaker in several Oracle Events around the world like “Technology Network Latin American Tour 2013, 2014, 2015 and 2016 in several countries like Guatemala, Costa Rica, Nicaragua, El Salvador, Uruguay, Argentina, Mexico, Brazil, Ecuador, Colombia, Peru; Collaborate in Vegas, USA; Latin American Oracle Open World in Brazil (2015 and 2016) and Oracle Open World in San Francisco, USA (2015 and 2016). He is the first Guatemalan who was accepted by Oracle Corporation as “Beta Tester” for the version “12cR2” in 2015. He is the official Technical Reviewer of the Book “Oracle Database 12c Release 2 Multitenant (1st Edition, McGraw-Hill)" and Co-Author of the book "Oracle Database 12c Release 2 Testing Tools and Techniques for Performance and Scalability", both can be found in Amazon. He is the first Guatemala who appeared as an outstanding expert in the official magazine of Oracle Corporation called “Oracle Magazine” in the Edition November/December in 2014, this magazine is delivered around the world in several countries. He has published several articles in the Official Website of Oracle (www.oracle.com) in Portuguese, Spanish and English. Currently he is President of Guatemalan Oracle Users Group (GOUG). Director of Support Quality en Latin American Oracle Users Group Community (LAOUC) for 2016-2017, founder of a very well known group in Oracle Community called “Oraworld Team” that has members from India, Guatemala, Brazil, France and Switzerland having in total 4 Oracle ACE Directors, 3 Oracle ACE, 5 Oracle Certified Masters (OCM) and 1 PhD. Deiby was part of Oracle ACE Hackaton in Amsterdam, Netherlands in April 2016 where he was building several solutions using Oracle Cloud Products. Currently Deiby Gómez is CEO in Nuvola Consulting Group, a company that provides excellence on Support and Consulting services with Oracle Technology like Databases, Middleware, Cloud and Engineered systems. Deiby is well known in the community because of his resilience, entrepreneurship and his availability to help and share his knowledge. Deiby loves to travel, to play chess and to enjoy a good cup of coffee with friends. Oracle ACE (at the age of 23) Oracle Certified Master 11g (at the age of 24) Oracle ACE Director (at the age of 25) Oracle Certified Master 12c (at the age 26) SELECT Journal Editor’s Choice Award 2016 Speaker in several Universities, OTN Tour 2013,2014,2015. Collaborate15, LA Oracle Open World. Technical Reviewer of the Book "Oracle Database 12c Release 2 Multitenant (Oracle Press) 1st Edition" Blogger. Oracle Certifications: Oracle Linux Certified Implementation Specialist. Oracle Database 11g Administrator Certified Professional. Oracle Database 11g Administrator Certified Master (OCM 11g) Oracle Database 12c Administrator Certified Master (OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Master (MAA OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Expert (MAA OCE 12c) Oracle Database 12c Administrator Certified Professional. Oracle Service Oriented Architecture Infrastructure Implementation Certified Expert. Oracle Exadata Database Machine Administrator. Oracle RAC 11g and Grid Infraestructure Administrator Oracle RAC 12c and Grid Infraestructure Administrator Oracle Real Application Clusters 12c Certified Implementation Specialist Oracle Database 12c: Data Guard Administrator