12c – SQL plan directives

One of the new CBO features in database 12c is "sql plan directives". The main purpose of this functionality is to deliver better adaptive plans if object statistics does not reflect the reality.

Quoting after documentation

During SQL execution, if a cardinality misestimate occurs, then the database creates SQL plan directives. During SQL compilation, the optimizer examines the query corresponding to the directive to determine whether missing extensions or histograms exist (see "Managing Extended Statistics"). The optimizer records any missing extensions. Subsequent DBMS_STATS calls collect statistics for the extensions.

Let’s try to enable this feature.
First I will create sample table:

SQL> create table tmp_src as select * from dba_source;

Table created.
SQL> exec dbms_stats.gather_table_Stats('HR','TMP_SRC');

PL/SQL procedure successfully completed.

I will execute a few times query with two predicates in WHERE clause:

SQL> ;
  1  select distinct owner
  2  from tmp_src
  3  where line between 10 and 20
  4* and   text like '%DUP%'

Let’s find the query in V$SQL:

SQL> ed
Wrote file afiedt.buf

  1  select sql_id, child_number, plan_hash_value, parse_calls, executions,
  2	    IS_REOPTIMIZABLE, IS_RESOLVED_ADAPTIVE_PLAN
  3  from v$sql
  4  where parsing_schema_name='HR'
  5* and sql_text like 'select owner%'
SQL> 
SQL> /

SQL_ID	      CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS EXECUTIONS I I
------------- ------------ --------------- ----------- ---------- - -
3yc0yrjcc339d		 0	2587360048	     1		1 N

Let’s check the execution plan for this SQL

SQL> select * from table(dbms_xplan.display_cursor('3yc0yrjcc339d',0));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	3yc0yrjcc339d, child number 0
-------------------------------------
select distinct owner from tmp_src where text like '%DUP%' and line
between 10 and 20

Plan hash value: 2587360048

------------------------------------------------------------------------------
| Id  | Operation	   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	     |	     |	     |	2136 (100)|	     |
|   1 |  HASH UNIQUE	   |	     |	   4 |	 428 |	2136   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| TMP_SRC |	   5 |	 535 |	2135   (1)| 00:00:01 |
------------------------------------------------------------------------------

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

   2 - filter(("LINE"<=20 AND "TEXT" LIKE '%DUP%' AND "LINE">=10 AND
	      "TEXT" IS NOT NULL))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
   - 1 Sql Plan Directive used for this statement

Notice this one: "1 Sql Plan Directive used for this statement". SQL Plan Directives are being kept in dba_sql_plan_directives view. We can force to flush entries from memory to disk and check our directive:

SQL> exec dbms_spd.FLUSH_SQL_PLAN_DIRECTIVE

PL/SQL procedure successfully completed.

SQL> set long 20000
SQL> ;
  1  select notes
  2  from dba_sql_plan_directives
  3* where notes like '%TMP_SRC%'
SQL> /

NOTES
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<spd_note>
  <internal_state>MISSING_STATS</internal_state>
  <redundant>NO</redundant>
  <spd_text>{C(HR.TMP_SRC)[LINE, TEXT]}</spd_text>
</spd_note>

This directive instructs CBO to gather dynamic statistics for columns LINE and TEXT of HR.TMP_SRC table, each time they are being used, because of MISSING_STATS the dynamic sampling shows up. Thats why in SQL Plan we can see line: "- dynamic statistics used: dynamic sampling (level=2)"

The SQL Plan Directive can also impact the statistics gathering. Before the directive, there was no histograms:

SQL> column column_name format a30
SQL> select column_name, histogram
  2  from user_tab_col_statistics 
  3  where table_name='TMP_SRC';

COLUMN_NAME		       HISTOGRAM
------------------------------ ---------------
OWNER			       NONE
NAME			       NONE
TYPE			       NONE
LINE			       NONE
TEXT			       NONE
ORIGIN_CON_ID		       NONE

After the directive was created, the next time when statistics are being gathered SQL – directive is used to create a new histogram:

SQL> exec dbms_stats.gather_table_stats('HR','TMP_SRC');

PL/SQL procedure successfully completed.

SQL> /

COLUMN_NAME		       HISTOGRAM
------------------------------ ---------------
ORIGIN_CON_ID		       NONE
TEXT			       HYBRID
LINE			       HYBRID
TYPE			       NONE
NAME			       NONE
OWNER			       NONE