8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

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.

Related articles.

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.

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.

Long term storage and transfer of SQL plan directives is possible using the following routines.

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:

Hope this helps. Regards Tim...

Back to the Top.