8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
SQL Plan Directives in Oracle Database 12c Release 1 (12.1)
SQL Plan Directives are one of the functionalities that make up Adaptive Query Optimization in Oracle Database 12c. SQL plan directives are like "extra notes" for the optimizer, to remind it that it previously selected a suboptimal plan, typically because of incorrect cardinality estimates. Incorrect cardinality estimates are often caused by issues like missing statistics, stale statistics, complex predicates or complex operators. Unlike SQL profiles, which are statement specific, SQL plan directives are linked to query expressions, so they can be used by several statements containing matching query expressions. Situations like missing histograms or missing extended statistics may result in SQL plan directives being generated.
The database manages SQL plan directives internally. Situations like automatic reoptimization may result in SQL plan directives being written to the SGA and later persisted to the SYSAUX tablespace, at which point they can be displayed using the DBA_SQL_PLAN_DIRECTIVES
and DBA_SQL_PLAN_DIR_OBJECTS
views. Alternatively, existing SQL plan directives can be persisted manually using the DBMS_SPD
package.
Control of the adaptive optimizations has altered in 12.2 and the change is available in 12.1 with the correct patches. See here.
- Setup
- Show Incorrect Cardinality Estimates
- Display SQL Plan Directives
- Show SQL Plan Directive Use
- SQL Plan Directives and Statistics
- DBMS_SPD Package
- Comments
Related articles.
- Adaptive Query Optimization in Oracle Database 12c (12.1 and 12.2)
- Adaptive Plans in Oracle Database 12c Release 1 (12.1)
- Dynamic Statistics in Oracle Database 12c Release 1 (12.1)
- Automatic Reoptimization in Oracle Database 12c Release 1 (12.1)
- Automatic Column Group Detection in Oracle Database 12c Release 1 (12.1)
Setup
Make sure the shared pool is flushed, so we know we are starting with a clean system.
CONN sys@pdb1 AS SYSDBA ALTER SYSTEM FLUSH SHARED_POOL;
The following test table contains columns to indicate gender and the presence of a Y chromosome. These columns have check constraints to tell the optimizer about the allowable values, as well as indexes on the columns. The data is deliberately skewed with the sample containing 10 rows marked as male and 90 marked as female.
CONN test/test@pdb1 DROP TABLE tab1 PURGE; CREATE TABLE tab1 ( id NUMBER, gender VARCHAR2(1), has_y_chromosome VARCHAR2(1), CONSTRAINT tab1_pk PRIMARY KEY (id), CONSTRAINT tab1_gender_chk CHECK (gender IN ('M', 'F')), CONSTRAINT tab1_has_y_chromosome_chk CHECK (has_y_chromosome IN ('Y', 'N')) ); INSERT /*+ APPEND */ INTO tab1 SELECT level, 'M', 'Y' FROM dual CONNECT BY level <= 10; COMMIT; INSERT /*+ APPEND */ INTO tab1 SELECT 10+level, 'F', 'N' FROM dual CONNECT BY level <= 90; COMMIT; CREATE INDEX tab1_gender_idx ON tab1(gender); CREATE INDEX tab1_has_y_chromosome_idx ON tab1(has_y_chromosome); EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');
Despite the skew in the data, there are no histograms created for the columns when the statistics are gathered with the default settings.
COLUMN column_name FORMAT A20 SELECT column_id, column_name, histogram FROM user_tab_columns WHERE table_name = 'TAB1' ORDER BY column_id; COLUMN_ID COLUMN_NAME HISTOGRAM ---------- -------------------- --------------- 1 ID NONE 2 GENDER NONE 3 HAS_Y_CHROMOSOME NONE SQL>
Show Incorrect Cardinality Estimates
We know that genetically speaking, all males have a Y chromosome and all females do not. Since the optimizer doesn't know about this relationship, and it has no histograms to indicate data skew, it does the best it can with the information and makes an estimate about the selectivity of the predicates. Assuming the data is spread evenly amongst the allowable values it would expect half of the rows to be marked as male and half to be marked as having a Y chromosome. If there were no relationship between the two columns, it would expect 100/2/2=25 rows to be marked as both male and having a Y chromosome. The following query pulls out all the rows matching this criteria and displays the execution plan used to return the data, including the expected and actual cardinalities.
CONN test/test@pdb1 SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM tab1 WHERE gender = 'M' AND has_y_chromosome = 'Y'; SET LINESIZE 200 PAGESIZE 100 SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------- SQL_ID 5t8y8p5mpb99j, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM tab1 WHERE gender = 'M' AND has_y_chromosome = 'Y' Plan hash value: 1552452781 ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 4 | |* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 25 | 10 |00:00:00.01 | 4 | |* 2 | INDEX RANGE SCAN | TAB1_GENDER_IDX | 1 | 50 | 10 |00:00:00.01 | 2 | ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("HAS_Y_CHROMOSOME"='Y') 2 - access("GENDER"='M') SQL>
As expected, the optimizer got the cardinality estimate wrong. This is a classic case where the presence of histograms, extended statistics or both would help the optimizer to estimate the cardinality more accurately.
The following query checks the IS_REOPTIMIZABLE
column of the V$SQL
view to see if the optimizer has noticed the bad cardinality estimate. The SQL_ID
is taken from the plan output above.
CONN sys@pdb1 AS SYSDBA COLUMN sql_text FORMAT A40 COLUMN is_reoptimizable FORMAT A16 SELECT sql_text, is_reoptimizable FROM v$sql WHERE sql_id = '5t8y8p5mpb99j'; SQL_TEXT IS_REOPTIMIZABLE ---------------------------------------- ---------------- SELECT /*+ GATHER_PLAN_STATISTICS */ Y * FROM tab1 WHERE gender = 'M' AN D has_y_chromosome = 'Y' SQL>
Since the statement is marked as reoptimizable, it is likely SQL plan directives has been created.
Display SQL Plan Directives
At this point, the optimizer has written SQL plan directives into the SGA, but they are not visible to us as they have not been persisted to the SYSAUX tablespace.
CONN sys@pdb1 AS SYSDBA SET LINESIZE 200 COLUMN dir_id FORMAT A20 COLUMN owner FORMAT A10 COLUMN object_name FORMAT A10 COLUMN col_name FORMAT A10 SELECT TO_CHAR(d.directive_id) dir_id, o.owner, o.object_name, 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 = 'TEST' ORDER BY 1,2,3,4,5; no rows selected SQL>
We can wait for the database to persist the SQL plan directives, or manually persist them using the DBMS_SPD
package.
CONN sys@pdb1 AS SYSDBA EXEC DBMS_SPD.flush_sql_plan_directive;
If we repeat the previous query, the SQL plan directives will be visible.
CONN sys@pdb1 AS SYSDBA SET LINESIZE 200 COLUMN dir_id FORMAT A20 COLUMN owner FORMAT A10 COLUMN object_name FORMAT A10 COLUMN col_name FORMAT A10 SELECT TO_CHAR(d.directive_id) dir_id, o.owner, o.object_name, 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 = 'TEST' ORDER BY 1,2,3,4,5; DIR_ID OWNER OBJECT_NAM COL_NAME OBJECT TYPE STATE REASON -------------------- ---------- ---------- ---------- ------ ---------------- ---------- ------------------------------------ 12422623998396966202 TEST TAB1 GENDER COLUMN DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE 12422623998396966202 TEST TAB1 TABLE DYNAMIC_SAMPLING USABLE SINGLE TABLE CARDINALITY MISESTIMATE SQL>
Show SQL Plan Directive Use
The following query is a repeat of the one we used to generate the SQL plan directives. Notice the change in the cardinality estimates and the notes at the end of the DBMS_XPLAN
output.
CONN test/test@pdb1 SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM tab1 WHERE gender = 'M' AND has_y_chromosome = 'Y'; SET LINESIZE 200 PAGESIZE 100 SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------- SQL_ID 5t8y8p5mpb99j, child number 1 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM tab1 WHERE gender = 'M' AND has_y_chromosome = 'Y' Plan hash value: 1552452781 ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 4 | |* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 10 | 10 |00:00:00.01 | 4 | |* 2 | INDEX RANGE SCAN | TAB1_GENDER_IDX | 1 | 10 | 10 |00:00:00.01 | 2 | ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("HAS_Y_CHROMOSOME"='Y') 2 - access("GENDER"='M') Note ----- - dynamic statistics used: dynamic sampling (level=2) - statistics feedback used for this statement - 1 Sql Plan Directive used for this statement SQL>
SQL Plan Directives and Statistics
SQL plan directives are typically an indication of "missing information", but that information may become available in the future. If we regather statistics for the TAB1
table, we will see that histograms have now been collected for the columns with skewed data.
EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1'); SELECT column_id, column_name, histogram FROM user_tab_columns WHERE table_name = 'TAB1' ORDER BY column_id; COLUMN_ID COLUMN_NAME HISTOGRAM ---------- -------------------- --------------- 1 ID NONE 2 GENDER FREQUENCY 3 HAS_Y_CHROMOSOME FREQUENCY SQL>
If we had extended statistics they would be visible using a query such as the following.
COLUMN extension FORMAT A30 SELECT extension_name, extension FROM user_stat_extensions;
Once the required histograms or extended statistics are present, or if an alternative SQL plan directive is created, the SQL plan directives may be switched to a state of SUPERSEDED
and no longer used.
CONN sys@pdb1 AS SYDBA SELECT state, COUNT(*) FROM dba_sql_plan_directives GROUP BY state ORDER BY state; STATE COUNT(*) ---------- ---------- SUPERSEDED 26 USABLE 44 SQL>
By default, SQL plan directives are deleted if they have not been used for 53 weeks.
Some SQL plan directives have an impact on the collection of column group statistics, as discussed here.
DBMS_SPD Package
The DBMS_SPD
packages gives some level of control over SQL plan directives.
Basic SQL plan directive management is performed using the following routines.
FLUSH_SQL_PLAN_DIRECTIVE
: Persists the SQL plan directives in the SGA to the SYSAUX tablespace.ALTER_SQL_PLAN_DIRECTIVE
: Alters theENABLED
andAUTO_DROP
attributes of a specified SQL plan directive.DROP_SQL_PLAN_DIRECTIVE
: Drops the specified SQL plan directive.
The length of time an unused SQL plan directive is retained for is determined by the SPD_RETENTION_WEEKS
preference, default 53 weeks. This is displayed and modified using the following routines.
GET_PREFS
: Displays the specified preference. OnlySPD_RETENTION_WEEKS
is currently supported.SET_PREFS
: Sets the specified preference. OnlySPD_RETENTION_WEEKS
is currently supported.
Long term storage and transfer of SQL plan directives is possible using the following routines.
CREATE_STGTAB_DIRECTIVE
: Create a staging table to export the SQL plan directives to.PACK_STGTAB_DIRECTIVE
: Export the SQL plan directives to a staging table.UNPACK_STGTAB_DIRECTIVE
: Import SQL plan directives from a staging table.
The ADMINISTER SQL MANAGEMENT OBJECT
privilege is required for much of the functionality in the DBMS_SPD
package.
Comments
The fact that SQL plan directives are managed internally can make things a little confusing at times. During my tests I came across situations where I expected them to be created and they were not. Also, I came across situations where I expected existing SQL plan directives to be marked as superseded and they were not. At first I though this was because I was forgetting to persist them, but this was not the case.
Always run the DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE
procedure before checking the DBA_SQL_PLAN_DIRECTIVES
and DBA_SQL_PLAN_DIR_OBJECTS
views.
There are some comments about how statistics feedback and SQL plan directives work with or against each other here.
For more information see:
- SQL Plan Directives
- DBA_SQL_PLAN_DIRECTIVES
- DBA_SQL_PLAN_DIR_OBJECTS
- DBMS_SPD
- Adaptive Query Optimization in Oracle Database 12c (12.1 and 12.2)
- Adaptive Plans in Oracle Database 12c Release 1 (12.1)
- Dynamic Statistics in Oracle Database 12c Release 1 (12.1)
- Automatic Reoptimization in Oracle Database 12c Release 1 (12.1)
- Automatic Column Group Detection in Oracle Database 12c Release 1 (12.1)
Hope this helps. Regards Tim...