What Are SQL Plan Directives?

If you ever looked at an execution plan in Oracle 12c, most likely you noticed the following line, at the bottom of the execution plan:

...
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - statistics feedback used for this statement
   - this is an adaptive plan
   - 6 Sql Plan Directives used for this statement

Have you ever wondered what SQL Plan Directives are?
Why is Oracle using them, and how can they help you (or not help you)?

You might have an idea what SQL Plan Directives are, but maybe you don’t have the full picture of what is going on behind the scene.
Then this post is for you!

You might not have heard of SQL Plan Directives, and that is totally fine!
Then this post is definitely for you!

You might know how SQL Plan Directives work, and have some tips on them!
Then drop a line in the comments section of this post! I read every comment!

1.What Are SQL Plan Directives?
2.When Are SQL Plan Directives Created?
3.How Are SQL Plan Directives Used?
4.Where are SQL Plan Directives stored?
5.Dictionary Views for SQL Plan Directives.

1.What Are SQL Plan Directives?

SQL Plan Directives were introduced with Oracle 12c. SQL Plan Directives are objects that contain additional information and instructions for the optimizer, to generate a “better” execution plan.

Oracle defines it as “a note to self” by the optimizer, to remind it that it is misestimating cardinalities of certain types of predicates.
SQL Plan Directives are also a reminder for DBMS_STATS to gather stats needed to correct the misestimates, next time the default job runs.

Do you remember what cardinality is? Hint: Cardinality is the number of rows returned or accessed by an operation.
If you need a refresher on cardinality or selectivity, check out my older post 5 Things To Better Understand Selectivity And Cardinality.

SQL Plan Directives are not linked to a SQL Statement or a SQL ID, rather these are related to query expressions, group of columns used in a join, or in a select statement. Thus, SQL Plan Directives can be used by many SQL Statements.
The execution plan will tell you at the end, how many directives were used for that particular statement

Note
-----
  ...
   - 6 Sql Plan Directives used for this statement

2.When Are SQL Plan Directives Created?

SQL Plan Directives are created during the automatic re-optimization phase. This phase occurs after the SQL Statement has been executed, and a cardinality misestimate occurred.
During automatic re-optimization phase the optimizer learns from previous executions, and the outcome of it goes back to the optimizer to help it create better execution plans. One of these outcomes are SQL Plan Directives.

3.How Are SQL Plan Directives Used?

SQL Plan Directives contain instructions related to dynamic stats and column groups.
The optimizer obeys the SQL Plan Directives during the parsing phase of the SQL statement, if Plan Directives are present.

a) The SQL Plan Directive could indicate that whenever a combination of columns is used in a SQL query, the optimizer should use dynamic sampling, and gather statistics for that group of columns, to avoid misestimates.
This process, the dynamic sampling is an expensive operation and it could cause overhead (in reality it is causing a lot of overhead).
In version 12.1, the stats gathered dynamically were not saved, however, starting with version 12.2 the database writes the statistics gathered during this process, to the SQL Plan directives store, for future reuse.

b) The SQL Plan Directives could also store instructions for DBMS_STATS to gather extended stats, on column groups.
If the preference of DBMS_STATS on AUTO_STAT_EXTENSIONS is set to ON for the table in the query, and if there is a missing column group, then the optimizer creates the column group the next time stats are gathered on the table.

If the column group exists, the optimizer will use the extended stats instead of the SQL Plan Directives, if possible.

4.Where are SQL Plan Directives stored?

When SQL Plan Directives are created, they are stored in memory, more precisely in the Shared Pool.
The database writes the SQL Plan Directives to the SYSAUX tablespace periodically.

If you want to manually write the SQL Plan Directives to disk, you can use: DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE.

EXEC DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE;

The retention of the directives is 53 weeks by default, and it can be changed with: DBMS_SPD.SET_PREFS.

SELECT DBMS_SPD.SET_PREFS('SPD_RETENTION_WEEKS')
FROM dual;

EXEC DBMS_SPD.SET_PREFS('SPD_RETENTION_WEEKS', '5');

5.Dictionary Views for SQL Plan Directives

There are 2 dictionary views related to SQL Plan Directives. These are:

DBA_SQL_PLAN_DIRECTIVES
DBA_SQL_PLAN_DIR_OBJECTS

Are you hungry for more SQL Plan Directives knowledge? There is a major change in behaviour in 12.2 compared to 12.1.

Follow me next week, to find out more!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana