A SQL plan directive is additional information and instructions that the optimizer can use to generate a more optimal plan. The directive is a “note to self” by the optimizer that it is misestimating cardinalities of certain types of predicates, and also a reminder to DBMS_STATS to gather statistics needed to correct the misestimates in the future. For example, 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. For example, during query optimization, when deciding whether the table is a candidate for dynamic statistics, the database queries the statistics repository for directives on a table. If the query joins two tables that have a data skew in their join columns, then a SQL plan directive can direct the optimizer to use dynamic statistics to obtain an accurate cardinality estimate. The optimizer collects SQL plan directives on query expressions rather than at the statement level so that it can apply directives to multiple SQL statements. The optimizer not only corrects itself, but also records information about the mistake, so that the database can continue to correct its estimates even after a query—and any similar query—is flushed from the shared pool. The database automatically creates directives, and stores them in the SYSAUX tablespace. You can alter, save to disk, and transport directives using the PL/SQL package DBMS_SPD. When Adaptive Statistics Are Enabled Adaptive statistics are disabled by default. Adaptive statistics are enabled when the following initialization parameters are set: OPTIMIZER_ADAPTIVE_STATISTICS is TRUE (the default is FALSE) OPTIMIZER_FEATURES_ENABLE is 12.1.0.1 or later Setting OPTIMIZER_ADAPTIVE_STATISTICS to TRUE enables the following features: SQL plan directives Statistics feedback for join cardinality Adaptive dynamic sampling Note: Setting OPTIMIZER_ADAPTIVE_STATISTICS to FALSE preserves statistics feedback for single-table cardinality misestimates. Setting this parameter OPTIMIZER_ADAPTIVE_STATISTICS tofalsedisables the following adaptive features: SQL plan directives Statistics feedback for joins Adaptive dynamic sampling for parallel execution Note: SettingOPTIMIZER_ADAPTIVE_STATISTICStofalsepreserves the statistics feedback functionality that was introduced in Oracle Database 11g.OPTIMIZER_ADAPTIVE_STATISTICSdoes not control the creation of SQL plan directives. SQL plan directives will be created even if this parameter isfalse, but they will not be used to refine SQL execution plans with dynamic sampling. When the Database Creates SQL Plan Directives The database creates SQL plan directives automatically based on information learned during automatic reoptimization. If a cardinality misestimate occurs during SQL execution, then the database creates SQL plan directives. For each new directive, the DBA_SQL_PLAN_DIRECTIVES.STATE column shows the value USABLE. This value indicates that the database can use the directive to correct misestimates. The optimizer defines a SQL plan directive on a query expression, for example, filter predicates on two columns being used together. A directive is not tied to a specific SQL statement or SQL ID. For this reason, the optimizer can use directives for statements that are not identical. For example, directives can help the optimizer with queries that use similar patterns, such as queries that are identical except for a select list item. The Notes section of the execution plan indicates the number of SQL plan directives used for a statement. Obtain more information about the directives by querying the DBA_SQL_PLAN_DIRECTIVES and DBA_SQL_PLAN_DIR_OBJECTS views. How the Database Uses SQL Plan Directives When compiling a SQL statement, if the optimizer sees a directive, then it obeys the directive by gathering additional information. The optimizer uses directives in the following ways: Dynamic statistics The optimizer uses dynamic statistics whenever it does not have sufficient statistics corresponding to the directive. For example, the cardinality estimates for a query whose predicate contains a specific pair of columns may be significantly wrong. A SQL plan directive indicates that the whenever a query that contains these columns is parsed, the optimizer needs to use dynamic sampling to avoid a serious cardinality misestimate. Dynamic statistics have some performance overhead. Every time the optimizer hard parses a query to which a dynamic statistics directive applies, the database must perform the extra sampling. Starting in Oracle Database 12c Release 2 (12.2), the database writes statistics from adaptive dynamic sampling to the SQL plan directives store, making them available to other queries. Column groups The optimizer examines the query corresponding to the directive. If there is a missing column group, and if the DBMS_STATS preference AUTO_STAT_EXTENSIONS is set to ON (the default is OFF) for the affected table, then the optimizer automatically creates this column group the next time DBMS_STATS gathers statistics on the table. Otherwise, the optimizer does not automatically create the column group. If a column group exists, then the next time this statement executes, the optimizer uses the column group statistics in place of the SQL plan directive when possible (equality predicates, GROUP BY, and so on). In subsequent executions, the optimizer may create additional SQL plan directives to address other problems in the plan, such as join or GROUP BY cardinality misestimates. Note: Currently, the optimizer monitors only column groups. The optimizer does not create an extension on expressions. When the problem that occasioned a directive is solved, either because a better directive exists or because a histogram or extension exists, the DBA_SQL_PLAN_DIRECTIVES.STATE value changes from USABLE to SUPERSEDED. More information about the directive state is exposed in the DBA_SQL_PLAN_DIRECTIVES.NOTES column. SQL Plan Directive Maintenance The database automatically creates SQL plan directives. You cannot create them manually. The database initially creates directives in the shared pool. The database periodically writes the directives to the SYSAUX tablespace. The database automatically purges any SQL plan directive that is not used after the specified number of weeks (SPD_RETENTION_WEEKS), which is 53 by default. You can manage directives by using the DBMS_SPD package. For example, you can: Enable and disable SQL plan directives (ALTER_SQL_PLAN_DIRECTIVE) Change the retention period for SQL plan directives (SET_PREFS) Export a directive to a staging table (PACK_STGTAB_DIRECTIVE) Drop a directive (DROP_SQL_PLAN_DIRECTIVE) Force the database to write directives to disk (FLUSH_SQL_PLAN_DIRECTIVE)
Demonstration -1
1=> I will be creating Table with Skewed data ,this is important condition for Directive usages,Then Query must use Multi column Expressions as well.
SQL> create table TECH_FOR_SPD as select 'JAPAN' as product, mod(rownum,5) as shop_id, mod(rownum,1000) as cons_id from dual connect by level<=20000 UNION ALL select 'INDIA' as product, mod(rownum,5) as shop_id, mod(rownum,1000) as cons_id from dual connect by level<=90 UNION ALL select 'GERMANY' as product, mod(rownum,5) as shop_id, mod(rownum,1000) as cons_id from dual connect by level<=2000 UNION ALL select 'RUSSIA' as product, mod(rownum,5) as shop_id, mod(rownum,1000) as cons_id from dual connect by level<=10000; Table created.
2=> Check Last analyzed stats (Table got auto analyzed due to Bulk insertions)
SQL> ALTER SESSION SET NLS_DATE_FORMAT='dd-mon-yyyy hh24:mi:ss'; COL TABLE_NAME FOR A20 SET LINES 5000 SELECT TABLE_NAME,LAST_ANALYZED FROM USER_TABLES WHERE TABLE_NAME='TECH_FOR_SPD'; Session altered. TABLE_NAME LAST_ANALYZED -------------------- -------------------- TECH_FOR_SPD 27-dec-2021 23:23:08
3=> Check Parameter for SPD
SQL> show parameter optimizer_ADAPTIVE_STATISTICS NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_adaptive_statistics boolean FALSE SQL> show parameter PARALLEL_DEGREE_POLICY NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ parallel_degree_policy string MANUAL SQL> show parameter optimizer_dynamic_sampling NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_dynamic_sampling integer 2
4=> Check for HISTOGRAMS, Columns Stats ,Auto Column stats extensions.
SQL> SELECT COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
FROM USER_TAB_COL_STATISTICS
WHERE TABLE_NAME = 'TECH_FOR_SPD'
ORDER BY 1;
COLUMN_NAME NUM_DISTINCT HISTOGRAM
-------------------------------------------------------------------------------------------------------------------------------- ------------ ---------------
CONS_ID 1000 NONE
PRODUCT 4 NONE
SHOP_ID 5 NONE
SQL> SELECT * FROM USER_STAT_EXTENSIONS WHERE TABLE_NAME='TECH_FOR_SPD';
SQL> SQL>
no rows selected
SET LONG 100000
SET LINES 120
SET PAGES 1000
SELECT DBMS_STATS.REPORT_COL_USAGE('ABHI_TEST', 'TECH_FOR_SPD')
FROM DUAL;
SQL> SQL> SQL> SQL> SQL> 2
DBMS_STATS.REPORT_COL_USAGE('ABHI_TEST','TECH_FOR_SPD')
--------------------------------------------------------------------------------
LEGEND:
.......
EQ : Used in single table EQuality predicate
RANGE : Used in single table RANGE predicate
LIKE : Used in single table LIKE predicate
NULL : Used in single table is (not) NULL predicate
EQ_JOIN : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER : Used in single table FILTER predicate
JOIN : Used in JOIN predicate
GROUP_BY : Used in GROUP BY expression
...............................................................................
###############################################################################
COLUMN USAGE REPORT FOR ABHI_TEST.TECH_FOR_SPD
..............................................
###############################################################################
5=> Let we Run query with 'GERMANY' literal.
SQL> select cons_id , shop_id,product from TECH_FOR_SPD where product = 'GERMANY' and shop_id=1 order by product ;
CONS_ID SHOP_ID PRODUCT
---------- ---------- -------
1 1 GERMANY
6 1 GERMANY
11 1 GERMANY
....
....
996 1 GERMANY
400 rows selected.
6=> Let we check Explain plan and expxtected recrods details
SQL> SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=>'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------ SQL_ID cav0tuj8c14mr, child number 0 ------------------------------------- select cons_id , shop_id,product from TECH_FOR_SPD where product = 'GERMANY' and shop_id=1 order by product Plan hash value: 242169553 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 400 |00:00:00.13 | 111 | 81 | |* 1 | TABLE ACCESS FULL| TECH_FOR_SPD | 1 | 1605 | 400 |00:00:00.13 | 111 | 81 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("SHOP_ID"=1 AND "PRODUCT"='GERMANY')) 19 rows selected.
Since there is Huge difference b/w E-rows and A-rows ,Hence this Query is candidate for Automatic Reoptimazation.
7=> Let we check for REOPTIMIZATION candidacy
col SQL_ID for a20 col CHILD_NUMBER FOR 9 COL SQL_TEXT FOR A50 COL IS_REOPTIMIZABLE FOR A30 set lines 500 SELECT SQL_ID, CHILD_NUMBER, SQL_TEXT, IS_REOPTIMIZABLE FROM V$SQL WHERE SQL_TEXT LIKE '%GERMANY%' AND SQL_TEXT NOT LIKE '%V$SQL%' AND SQL_TEXT NOT LIKE '%create%'; SQL> SQL> SQL> SQL> SQL> SQL> 2 3 SQL_ID CHILD_NUMBER SQL_TEXT IS_REOPTIMIZABLE -------------------- ------------ -------------------------------------------------- ------------------------------ cav0tuj8c14mr 0 select cons_id , shop_id,product from TECH_FOR_SP Y D where product = 'GERMANY' and shop_id=1 order by product
8=> Check if Any Directive Exisits for this Query.
SQL> SELECT TO_CHAR(d.DIRECTIVE_ID) dir_id, o.OWNER AS "OWN", o.OBJECT_NAME AS "OBJECT",
o.SUBOBJECT_NAME col_name, o.OBJECT_TYPE, d.TYPE, d.STATE, d.REASON
FROM DBA_SQL_PLAN_DIRECTIVES d, DBA_SQL_PLAN_DIR_OBJECTS o
WHERE d.DIRECTIVE_ID=o.DIRECTIVE_ID
AND o.OWNER IN ('ABHI_TEST') AND o.OBJECT_NAME='TECH_FOR_SPD'
ORDER BY 1,2,3,4,5;
2 3 4 5 6
no rows selected
SQL> SQL>
9=> Let we Query Again.
SQL> select cons_id , shop_id,product from TECH_FOR_SPD where product = 'GERMANY' and shop_id=1 order by product ;
CONS_ID SHOP_ID PRODUCT
---------- ---------- -------
1 1 GERMANY
6 1 GERMANY
11 1 GERMANY
16 1 GERMANY
21 1 GERMANY
26 1 GERMANY
.....
.....
996 1 GERMANY
400 rows selected.
SQL> SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=>'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------
SQL_ID cav0tuj8c14mr, child number 1
-------------------------------------
select cons_id , shop_id,product from TECH_FOR_SPD where product =
'GERMANY' and shop_id=1 order by product
Plan hash value: 242169553
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 400 |00:00:00.01 | 111 |
|* 1 | TABLE ACCESS FULL| TECH_FOR_SPD | 1 | 400 | 400 |00:00:00.01 | 111 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("SHOP_ID"=1 AND "PRODUCT"='GERMANY'))
Note
-----
- statistics feedback used for this statement
This Query now resulting Correct Cardinality ,But yet SPD not used.
10=> Check if this Query is still REOPTIMIZABLE
SQL> col SQL_ID for a20 col CHILD_NUMBER FOR 9 COL SQL_TEXT FOR A50 COL IS_REOPTIMIZABLE FOR A30 set lines 500 SELECT SQL_ID, CHILD_NUMBER, SQL_TEXT, IS_REOPTIMIZABLE FROM V$SQL WHERE SQL_TEXT LIKE '%GERMANY%' AND SQL_TEXT NOT LIKE '%V$SQL%' AND SQL_TEXT NOT LIKE '%create%'; SQL> SQL> SQL> SQL> SQL> 2 3 SQL_ID CHILD_NUMBER SQL_TEXT IS_REOPTIMIZABLE -------------------- ------------ -------------------------------------------------- ------------------------------ cav0tuj8c14mr 0 select cons_id , shop_id,product from TECH_FOR_SP Y D where product = 'GERMANY' and shop_id=1 order by product cav0tuj8c14mr 1 select cons_id , shop_id,product from TECH_FOR_SP N D where product = 'GERMANY' and shop_id=1 order by product
11=> Now let we modify OPTIMIZER_ADAPTIVE_STATISTICS to TRUE at session level and see if any NOTE section changes.
SQL> alter session set OPTIMIZER_ADAPTIVE_STATISTICS=TRUE; Session altered. SQL> select cons_id , shop_id,product from TECH_FOR_SPD where product = 'GERMANY' and shop_id=1 order by product ; CONS_ID SHOP_ID PRODUCT ---------- ---------- ------- 1 1 GERMANY 6 1 GERMANY 11 1 GERMANY 16 1 GERMANY .... ... 996 1 GERMANY 400 rows selected. SQL> SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=>'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ------------------------------------------ SQL_ID cav0tuj8c14mr, child number 2 ------------------------------------- select cons_id , shop_id,product from TECH_FOR_SPD where product = 'GERMANY' and shop_id=1 order by product Plan hash value: 242169553 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 400 |00:00:00.01 | 111 | |* 1 | TABLE ACCESS FULL| TECH_FOR_SPD | 1 | 1605 | 400 |00:00:00.01 | 111 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("SHOP_ID"=1 AND "PRODUCT"='GERMANY')) 19 rows selected.
Due to Environement change Now cardinality got miscalculated,
12=> Check if Query is REOPTIMIZABLE.
col SQL_ID for a20 col CHILD_NUMBER FOR 9 COL SQL_TEXT FOR A50 COL IS_REOPTIMIZABLE FOR A30 set lines 500 SELECT SQL_ID, CHILD_NUMBER, SQL_TEXT, IS_REOPTIMIZABLE FROM V$SQL WHERE SQL_TEXT LIKE '%GERMANY%' AND SQL_TEXT NOT LIKE '%V$SQL%' AND SQL_TEXT NOT LIKE '%create%'; SQL> SQL> SQL> SQL> SQL> SQL> 2 3 SQL_ID CHILD_NUMBER SQL_TEXT IS_REOPTIMIZABLE -------------------- ------------ -------------------------------------------------- ------------------------------ cav0tuj8c14mr 0 select cons_id , shop_id,product from TECH_FOR_SP Y D where product = 'GERMANY' and shop_id=1 order by product cav0tuj8c14mr 1 select cons_id , shop_id,product from TECH_FOR_SP N D where product = 'GERMANY' and shop_id=1 order by product cav0tuj8c14mr 2 select cons_id , shop_id,product from TECH_FOR_SP Y D where product = 'GERMANY' and shop_id=1 order by product
13=>Check if there any Directive Generated Yet.
SELECT TO_CHAR(d.DIRECTIVE_ID) dir_id, o.OWNER AS "OWN", o.OBJECT_NAME AS "OBJECT",
o.SUBOBJECT_NAME col_name, o.OBJECT_TYPE, d.TYPE, d.STATE, d.REASON
FROM DBA_SQL_PLAN_DIRECTIVES d, DBA_SQL_PLAN_DIR_OBJECTS o
WHERE d.DIRECTIVE_ID=o.DIRECTIVE_ID
AND o.OWNER IN ('ABHI_TEST') AND o.OBJECT_NAME='TECH_FOR_SPD'
ORDER BY 1,2,3,4,5; 2 3 4 5 6
no rows selected
14=> Now from SYS user perform below task to ENABLE Column Directives.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('ABHI_TEST', 'TECH_FOR_SPD');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.SET_GLOBAL_PREFS ('AUTO_STAT_EXTENSIONS','ON');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('ABHI_TEST', 'TECH_FOR_SPD');
PL/SQL procedure successfully completed.
15=> Let we run Same Query Again and see if Directive generated and used.
SQL> select cons_id , shop_id,product from TECH_FOR_SPD where product = 'GERMANY' and shop_id=1 order by product ;
CONS_ID SHOP_ID PRODUCT
---------- ---------- -------
1 1 GERMANY
6 1 GERMANY
11 1 GERMANY
16 1 GERMANY
21 1 GERMANY
...
...
991 1 GERMANY
996 1 GERMANY
400 rows selected.
SQL> SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=>'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-----------------------------------------
SQL_ID cav0tuj8c14mr, child number 3
-------------------------------------
select cons_id , shop_id,product from TECH_FOR_SPD where product =
'GERMANY' and shop_id=1 order by product
Plan hash value: 242169553
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 400 |00:00:00.01 | 111 |
|* 1 | TABLE ACCESS FULL| TECH_FOR_SPD | 1 | 400 | 400 |00:00:00.01 | 111 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("SHOP_ID"=1 AND "PRODUCT"='GERMANY'))
Note
-----
- statistics feedback used for this statement
23 rows selected.
16=> Check if Query still candidate for REOPTIMIZATION
SQL> col SQL_ID for a20
col CHILD_NUMBER FOR 9
COL SQL_TEXT FOR A50
COL IS_REOPTIMIZABLE FOR A30
set lines 500
SELECT SQL_ID, CHILD_NUMBER, SQL_TEXT, IS_REOPTIMIZABLE
FROM V$SQL
WHERE SQL_TEXT LIKE '%GERMANY%' AND SQL_TEXT NOT LIKE '%V$SQL%' AND SQL_TEXT NOT LIKE '%create%';
SQL> SQL> SQL> SQL> SQL> 2 3
SQL_ID CHILD_NUMBER SQL_TEXT IS_REOPTIMIZABLE
-------------------- ------------ -------------------------------------------------- ------------------------------
cav0tuj8c14mr 0 select cons_id , shop_id,product from TECH_FOR_SP Y
D where product = 'GERMANY' and shop_id=1 order
by product
cav0tuj8c14mr 1 select cons_id , shop_id,product from TECH_FOR_SP N
D where product = 'GERMANY' and shop_id=1 order
by product
cav0tuj8c14mr 2 select cons_id , shop_id,product from TECH_FOR_SP Y
D where product = 'GERMANY' and shop_id=1 order
by product
cav0tuj8c14mr 3 select cons_id , shop_id,product from TECH_FOR_SP N
D where product = 'GERMANY' and shop_id=1 order
by product
17=> Check of Directive Exists now .
SELECT TO_CHAR(d.DIRECTIVE_ID) dir_id, o.OWNER AS "OWN", o.OBJECT_NAME AS "OBJECT",
o.SUBOBJECT_NAME col_name, o.OBJECT_TYPE, d.TYPE, d.STATE, d.REASON
FROM DBA_SQL_PLAN_DIRECTIVES d, DBA_SQL_PLAN_DIR_OBJECTS o
WHERE d.DIRECTIVE_ID=o.DIRECTIVE_ID
AND o.OWNER IN ('ABHI_TEST') AND o.OBJECT_NAME='TECH_FOR_SPD'
ORDER BY 1,2,3,4,5;
SQL> /
DIR_ID OWN OBJECT COL_NAME OBJECT_TYPE TYPE STATE REASON
---------------------------------------- ---------- -------------------- -------------------- ------------------------- ----------------------- ---------- ------------------------------------
1236193561904742673 ABHI_TEST TECH_FOR_SPD PRODUCT COLUMN DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
1236193561904742673 ABHI_TEST TECH_FOR_SPD SHOP_ID COLUMN DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
1236193561904742673 ABHI_TEST TECH_FOR_SPD TABLE DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
Directive Exists but Not yet used. Let we Execute same Query Again and see if used,
SQL> select cons_id , shop_id,product from TECH_FOR_SPD where product = 'GERMANY' and shop_id=1 order by product ;
CONS_ID SHOP_ID PRODUCT
---------- ---------- -------
1 1 GERMANY
6 1 GERMANY
...
...
996 1 GERMANY
400 rows selected.
SQL> SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=>'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
---------------------------------------
SQL_ID cav0tuj8c14mr, child number 3
-------------------------------------
select cons_id , shop_id,product from TECH_FOR_SPD where product =
'GERMANY' and shop_id=1 order by product
Plan hash value: 242169553
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 400 |00:00:00.01 | 111 |
|* 1 | TABLE ACCESS FULL| TECH_FOR_SPD | 1 | 400 | 400 |00:00:00.01 | 111 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("SHOP_ID"=1 AND "PRODUCT"='GERMANY'))
Note
-----
- statistics feedback used for this statement
23 rows selected.
Since Cardinality for this Statement is already corrected ,Hence SPD not being used.
18=> As we know that SPD is SQL_ID independent and depnends only column expression. Let we keep expression same and change the Literals only and see of Directive being used.
Change Literal value from GERMANY to RUSSIA
SQL> select cons_id , shop_id,product from TECH_FOR_SPD where product = 'RUSSIA' and shop_id=1 order by product ;
CONS_ID SHOP_ID PRODUCT
---------- ---------- -------
1 1 RUSSIA
6 1 RUSSIA
11 1 RUSSIA
16 1 RUSSIA
21 1 RUSSIA
....
....
996 1 RUSSIA
2000 rows selected.
SQL> SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=>'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID 7s8xkbgstty2x, child number 0
-------------------------------------
select cons_id , shop_id,product from TECH_FOR_SPD where product =
'RUSSIA' and shop_id=1 order by product
Plan hash value: 242169553
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2000 |00:00:00.01 | 218 |
|* 1 | TABLE ACCESS FULL| TECH_FOR_SPD | 1 | 2000 | 2000 |00:00:00.01 | 218 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("SHOP_ID"=1 AND "PRODUCT"='RUSSIA'))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
24 rows selected.
As we can see Sql Plan Directive is being used for Column Group Expression with Correct Cardinality.
Let we see Status for Directive as below .All of them are USABLE.
SQL> SELECT TO_CHAR(d.DIRECTIVE_ID) dir_id, o.OWNER AS "OWN", o.OBJECT_NAME AS "OBJECT",
o.SUBOBJECT_NAME col_name, o.OBJECT_TYPE, d.TYPE, d.STATE, d.REASON
FROM DBA_SQL_PLAN_DIRECTIVES d, DBA_SQL_PLAN_DIR_OBJECTS o
WHERE d.DIRECTIVE_ID=o.DIRECTIVE_ID
AND o.OWNER IN ('ABHI_TEST') AND o.OBJECT_NAME='TECH_FOR_SPD'
ORDER BY 1,2,3,4,5;
2 3 4 5 6
DIR_ID OWN OBJECT COL_NAME OBJECT_TYPE TYPE STATE REASON
---------------------------------------- ---------- -------------------- -------------------- ------------------------- ----------------------- ---------- ------------------------------------
1236193561904742673 ABHI_TEST TECH_FOR_SPD PRODUCT COLUMN DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
1236193561904742673 ABHI_TEST TECH_FOR_SPD SHOP_ID COLUMN DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
1236193561904742673 ABHI_TEST TECH_FOR_SPD TABLE DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
19606713042951582 ABHI_TEST TECH_FOR_SPD TABLE DYNAMIC_SAMPLING_RESULT USABLE VERIFY CARDINALITY ESTIMATE
Demonstration -2
Continuing the same above steps let we test impact of optimizer_adaptive_statistics parameter in terms of Sql Plan Directives.
1=> Let we change OPTIMIZER_ADAPTIVE_STATISTICS to false as per below,
SQL> show parameter OPTIMIZER_ADAPTIVE_STATISTICS NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_adaptive_statistics boolean TRUE SQL> alter session set OPTIMIZER_ADAPTIVE_STATISTICS=FALSE; Session altered. SQL>
2=> Let we change Literal value from RUSSIA to INDIA and see if Direcives being used,
SQL> select cons_id , shop_id,product from TECH_FOR_SPD where product = 'INDIA' and shop_id=1 order by product ;
CONS_ID SHOP_ID PRODUCT
---------- ---------- -------
1 1 INDIA
6 1 INDIA
....
....
18 rows selected.
SQL> SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=>'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
---------------------------------------
SQL_ID gcdg19xq3zyv5, child number 0
-------------------------------------
select cons_id , shop_id,product from TECH_FOR_SPD where product =
'INDIA' and shop_id=1 order by product
Plan hash value: 242169553
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 18 |00:00:00.01 | 86 |
|* 1 | TABLE ACCESS FULL| TECH_FOR_SPD | 1 | 18 | 18 |00:00:00.01 | 86 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("PRODUCT"='INDIA' AND "SHOP_ID"=1))
19 rows selected.
As we can see there is NO Directive Being used now.
3=> Let we check Directive State and availability.
SQL> SELECT TO_CHAR(d.DIRECTIVE_ID) dir_id, o.OWNER AS "OWN", o.OBJECT_NAME AS "OBJECT",
o.SUBOBJECT_NAME col_name, o.OBJECT_TYPE, d.TYPE, d.STATE, d.REASON
FROM DBA_SQL_PLAN_DIRECTIVES d, DBA_SQL_PLAN_DIR_OBJECTS o
WHERE d.DIRECTIVE_ID=o.DIRECTIVE_ID
AND o.OWNER IN ('ABHI_TEST') AND o.OBJECT_NAME='TECH_FOR_SPD'
ORDER BY 1,2,3,4,5;
2 3 4 5 6
DIR_ID OWN OBJECT COL_NAME OBJECT_TYPE TYPE STATE REASON
---------------------------------------- ---------- -------------------- -------------------- ------------------------- ----------------------- ---------- ------------------------------------
1236193561904742673 ABHI_TEST TECH_FOR_SPD PRODUCT COLUMN DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
1236193561904742673 ABHI_TEST TECH_FOR_SPD SHOP_ID COLUMN DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
1236193561904742673 ABHI_TEST TECH_FOR_SPD TABLE DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
19606713042951582 ABHI_TEST TECH_FOR_SPD TABLE DYNAMIC_SAMPLING_RESULT USABLE VERIFY CARDINALITY ESTIMATE
4=> Let we againg change Literal from INDIA to RUSSIA and see if Directive being used,
SQL> select cons_id , shop_id,product from TECH_FOR_SPD where product = 'RUSSIA' and shop_id=1 order by product ;
SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=>'ALLSTATS LAST'));
CONS_ID SHOP_ID PRODUCT
---------- ---------- -------
1 1 RUSSIA
6 1 RUSSIA
11 1 RUSSIA
...
...
996 1 RUSSIA
2000 rows selected.
PLAN_TABLE_OUTPUT
----------------------------------------
SQL_ID 7s8xkbgstty2x, child number 1
-------------------------------------
select cons_id , shop_id,product from TECH_FOR_SPD where product =
'RUSSIA' and shop_id=1 order by product
Plan hash value: 242169553
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2000 |00:00:00.01 | 218 |
|* 1 | TABLE ACCESS FULL| TECH_FOR_SPD | 1 | 2000 | 2000 |00:00:00.01 | 218 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("SHOP_ID"=1 AND "PRODUCT"='RUSSIA'))
19 rows selected.
Still NO Directive being used.
5=> Now let we change parameter OPTIMIZER_ADAPTIVE_STATISTICS value to TRUE
SQL> SQL> alter session set OPTIMIZER_ADAPTIVE_STATISTICS=TRUE; Session altered. SQL>
6=> Let we execute again same syntax and check for Directives.
SQL>
select cons_id , shop_id,product from TECH_FOR_SPD where product = 'RUSSIA' and shop_id=1 order by product ;
SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=>'ALLSTATS LAST'));SQL> SQL>
CONS_ID SHOP_ID PRODUCT
---------- ---------- -------
1 1 RUSSIA
6 1 RUSSIA
11 1 RUSSIA
16 1 RUSSIA
21 1 RUSSIA
26 1 RUSSIA
....
....
996 1 RUSSIA
2000 rows selected.
/
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID 7s8xkbgstty2x, child number 0
-------------------------------------
select cons_id , shop_id,product from TECH_FOR_SPD where product =
'RUSSIA' and shop_id=1 order by product
Plan hash value: 242169553
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2000 |00:00:00.01 | 218 |
|* 1 | TABLE ACCESS FULL| TECH_FOR_SPD | 1 | 2000 | 2000 |00:00:00.01 | 218 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("SHOP_ID"=1 AND "PRODUCT"='RUSSIA'))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
24 rows selected.
SQL>
SELECT TO_CHAR(d.DIRECTIVE_ID) dir_id, o.OWNER AS "OWN", o.OBJECT_NAME AS "OBJECT",
o.SUBOBJECT_NAME col_name, o.OBJECT_TYPE, d.TYPE, d.STATE, d.REASON
FROM DBA_SQL_PLAN_DIRECTIVES d, DBA_SQL_PLAN_DIR_OBJECTS o
WHERE d.DIRECTIVE_ID=o.DIRECTIVE_ID
AND o.OWNER IN ('ABHI_TEST') AND o.OBJECT_NAME='TECH_FOR_SPD'
ORDER BY 1,2,3,4,5;SQL> 2 3 4 5 6
DIR_ID OWN OBJECT COL_NAME OBJECT_TYPE TYPE STATE REASON
---------------------------------------- ---------- -------------------- -------------------- ------------------------- ----------------------- ---------- ------------------------------------
1236193561904742673 ABHI_TEST TECH_FOR_SPD PRODUCT COLUMN DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
1236193561904742673 ABHI_TEST TECH_FOR_SPD SHOP_ID COLUMN DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
1236193561904742673 ABHI_TEST TECH_FOR_SPD TABLE DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE
19606713042951582 ABHI_TEST TECH_FOR_SPD TABLE DYNAMIC_SAMPLING_RESULT USABLE VERIFY CARDINALITY ESTIMATE
626443202495085515 ABHI_TEST TECH_FOR_SPD TABLE DYNAMIC_SAMPLING_RESULT USABLE VERIFY CARDINALITY ESTIMATE
As we can see there is Huge Impact of Directive usages on changing parameter value from FALSE to TRUE
Important View & Parameters
DBA_SQL_PLAN_DIRECTIVES
DBA_SQL_PLAN_DIR_OBJECTS
OPTIMIZER_ADAPTIVE_STATISTICS
DBMS_STATS.SET_GLOBAL_PREFS ('AUTO_STAT_EXTENSIONS','ON')
Parameter Changes from 12.2
OPTIMIZER_ADAPTIVE_FEATURES Initialization Parameter The OPTIMIZER_ADAPTIVE_FEATURES initialization parameter is removed and desupported in this release. The functions of this parameter are replaced by two new parameters. The default value for OPTIMIZER_ADAPTIVE_PLANS is TRUE. When set to TRUE, this parameter determines alternate execution plans that are based on statistics collected as a query executes. OPTIMIZER_ADAPTIVE_STATISTICS is set by default to FALSE. When set to TRUE, the optimizer augments the statistics gathered in the database with adaptive statistics gathered at SQL statement parse time to improve the quality of SQL execution plans.
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444