Sql plan directives are automatically created based on information learned via Automatic Reoptimization. The optimizer uses Sql Plan Directives to generate a more optimal execution plan. For eg: When joining two tables that have a data skew in their join columns, a Sql plan directive can direct the optimizer to use dynamic statistics to obtain a more accurate join cardinality estimate.
Key thing to note is that SPD’s are not created on Queries or Objects. They are created on query expressions. So the SPD’s generated by one sql statement can be used by multiple sql statements. It is possible for one Sql statement to use more than one SPD. SPD’s are persisted in the SYSAUX tablespace. Any SPD that is unused for 53 weeks will be automatically purged.
After a SQL directive is used the optimizer decides if the cardinality missestimate could be resolved with a column group. If so it will create the column group, the next time statistics are gathered on the appropriate table.
Let us take a look at an example.
We are using the same sql statement from the example in my post about Adaptive ReOptimization
SQL_ID faca0yc2h1cdr, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ count(*) FROM customers WHERE
cust_state_province = 'CA' AND country_id=52790
Plan hash value: 296924608
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 405 (100)| | 1 |00:00:00.01 | 1458 |
| 1 | SORT AGGREGATE | | 1 | 1 | 16 | | | 1 |00:00:00.01 | 1458 |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 1 | 1115 | 17840 | 405 (1)| 00:00:01 | 3341 |00:00:00.01 | 1458 |
--------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / CUSTOMERS@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("CUST_STATE_PROVINCE"='CA' AND "COUNTRY_ID"=52790))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
2 - (rowset=906)
Next I will flush the plan directive
BEGIN
DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE;
END;
/
Lets check the plan directives that have been created
DIR_ID OWNER OBJECT_NAME COL_NAME OBJECT_TYPE TYPE STATE INST
---------------------------------------- ----- ------------------------- -------------------- ------------------------- ----------------------- ---------- -------------
REASON DC LU
------------------------------------ --------------------------- ---------------------------
1581183467014446692 SH CUSTOMERS COUNTRY_ID COLUMN DYNAMIC_SAMPLING USABLE NEW
SINGLE TABLE CARDINALITY MISESTIMATE 20-SEP-16 08:32:22
1581183467014446692 SH CUSTOMERS CUST_STATE_PROVINCE COLUMN DYNAMIC_SAMPLING USABLE NEW
SINGLE TABLE CARDINALITY MISESTIMATE 20-SEP-16 08:32:22
1581183467014446692 SH CUSTOMERS TABLE DYNAMIC_SAMPLING USABLE NEW
SINGLE TABLE CARDINALITY MISESTIMATE 20-SEP-16 08:32:22
Then let us a execute a new statement, by just changing one of the predicates.
SQL_ID aj2w592n68s4s, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ count(*) FROM customers WHERE
cust_state_province = 'FL' AND country_id=52790
Plan hash value: 296924608
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 405 (100)| | 1 |00:00:00.01 | 1457 |
| 1 | SORT AGGREGATE | | 1 | 1 | 16 | | | 1 |00:00:00.01 | 1457 |
|* 2 | TABLE ACCESS FULL| CUSTOMERS | 1 | 2549 | 40784 | 405 (1)| 00:00:01 | 2438 |00:00:00.01 | 1457 |
--------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / CUSTOMERS@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("CUST_STATE_PROVINCE"='FL' AND "COUNTRY_ID"=52790))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
2 - (rowset=906)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
In the notes section we can see that the plan directive has been used by this new statement.