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:
1 | SQL> create table tmp_src as select * from dba_source; |
4 | SQL> exec dbms_stats.gather_table_Stats('HR','TMP_SRC'); |
6 | PL/SQL procedure successfully completed. |
I will execute a few times query with two predicates in WHERE clause:
2 | 1 select distinct owner |
4 | 3 where line between 10 and 20 |
5 | 4* and text like '%DUP%' |
Let’s find the query in V$SQL:
4 | 1 select sql_id, child_number, plan_hash_value, parse_calls, executions, |
5 | 2 IS_REOPTIMIZABLE, IS_RESOLVED_ADAPTIVE_PLAN |
7 | 4 where parsing_schema_name='HR' |
8 | 5* and sql_text like 'select owner%' |
12 | SQL_ID CHILD_NUMBER PLAN_HASH_VALUE PARSE_CALLS EXECUTIONS I I |
13 | ------------- ------------ --------------- ----------- ---------- - - |
14 | 3yc0yrjcc339d 0 2587360048 1 1 N |
Let’s check the execution plan for this SQL
1 | SQL> select * from table(dbms_xplan.display_cursor('3yc0yrjcc339d',0)); |
4 | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
5 | SQL_ID 3yc0yrjcc339d, child number 0 |
6 | ------------------------------------- |
7 | select distinct owner from tmp_src where text like '%DUP%' and line |
10 | Plan hash value: 2587360048 |
12 | ------------------------------------------------------------------------------ |
13 | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
14 | ------------------------------------------------------------------------------ |
15 | | 0 | SELECT STATEMENT | | | | 2136 (100)| | |
16 | | 1 | HASH UNIQUE | | 4 | 428 | 2136 (1)| 00:00:01 | |
17 | |* 2 | TABLE ACCESS FULL| TMP_SRC | 5 | 535 | 2135 (1)| 00:00:01 | |
18 | ------------------------------------------------------------------------------ |
20 | Predicate Information (identified by operation id): |
21 | --------------------------------------------------- |
23 | 2 - filter(("LINE"<=20 AND "TEXT" LIKE '%DUP%' AND "LINE">=10 AND |
28 | - dynamic statistics used: dynamic sampling (level=2) |
29 | - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold |
30 | - 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:
1 | SQL> exec dbms_spd.FLUSH_SQL_PLAN_DIRECTIVE |
3 | PL/SQL procedure successfully completed. |
8 | 2 from dba_sql_plan_directives |
9 | 3* where notes like '%TMP_SRC%' |
13 | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
15 | <internal_state>MISSING_STATS</internal_state> |
16 | <redundant>NO</redundant> |
17 | <spd_text>{C(HR.TMP_SRC)[LINE, TEXT]}</spd_text> |
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:
1 | SQL> column column_name format a30 |
2 | SQL> select column_name, histogram |
3 | 2 from user_tab_col_statistics |
4 | 3 where table_name='TMP_SRC'; |
7 | ------------------------------ --------------- |
After the directive was created, the next time when statistics are being gathered SQL – directive is used to create a new histogram:
1 | SQL> exec dbms_stats.gather_table_stats('HR','TMP_SRC'); |
3 | PL/SQL procedure successfully completed. |
8 | ------------------------------ --------------- |
