📋Steps in this guide1/7

SQL Plan Directives in Oracle Database 12c Release 1 (12.1)

This article explains the role of SQL Plan Directives in Adaptive Query Optimization in Oracle Database 12c Release 1 (12.1).

oracle 12cconfigurationintermediate

by OracleDba

12 views

Setup

Make sure the shared pool is flushed, so we know we are starting with a clean system. 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. Despite the skew in the data, there are no histograms created for the columns when the statistics are gathered with the default settings.

Code/Command (click line numbers to comment):

CONN sys@pdb1 AS SYSDBA

ALTER SYSTEM FLUSH SHARED_POOL;

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');

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. 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 column of the view to see if the optimizer has noticed the bad cardinality estimate. The is taken from the plan output above. Since the statement is marked as reoptimizable, it is likely SQL plan directives has been created.

Code/Command (click line numbers to comment):

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>

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>

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. We can wait for the database to persist the SQL plan directives, or manually persist them using the package. If we repeat the previous query, the SQL plan directives will be visible.

Code/Command (click line numbers to comment):

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>

CONN sys@pdb1 AS SYSDBA

EXEC DBMS_SPD.flush_sql_plan_directive;

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 output.

Code/Command (click line numbers to comment):

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 table, we will see that histograms have now been collected for the columns with skewed data. If we had extended statistics they would be visible using a query such as the following. 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 and no longer used. 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 .

Code/Command (click line numbers to comment):

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>

COLUMN extension FORMAT A30

SELECT extension_name, extension
FROM   user_stat_extensions;

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>

DBMS_SPD Package

The packages gives some level of control over SQL plan directives. Basic SQL plan directive management is performed using the following routines. - : Persists the SQL plan directives in the SGA to the SYSAUX tablespace. - : Alters the and attributes of a specified 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 preference, default 53 weeks. This is displayed and modified using the following routines. - : Displays the specified preference. Only is currently supported. - : Sets the specified preference. Only is currently supported. Long term storage and transfer of SQL plan directives is possible using the following routines. - : Create a staging table to export the SQL plan directives to. - : Export the SQL plan directives to a staging table. - : Import SQL plan directives from a staging table. The privilege is required for much of the functionality in the 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 procedure before checking the and 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...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!