Posted by On AskTOM episode 8, I’ve taken a look at locating the SQL Plan Directives used for a particular query. Here is the script output from the video if you want to use this for your own exploration
SQL>
SQL> create table t as
2 select *
3 from dba_objects
4 where owner = 'SYS' and rownum <= 20
5 union all
6 select *
7 from dba_objects
8 where owner = 'SYSTEM'
9 and rownum
SQL> create index ix on t ( owner);
Index created.
SQL>
SQL> select COLUMN_NAME,NUM_DISTINCT,AVG_COL_LEN, num_nulls, density
2 from user_tab_cols
3 where table_name = 'T'
4 order by COLUMN_ID;
COLUMN_NAME NUM_DISTINCT AVG_COL_LEN NUM_NULLS DENSITY
------------------------------ ------------ ----------- ---------- ----------
OWNER 2 7 0 .5
OBJECT_NAME 199 18 0 .005025126
SUBOBJECT_NAME 1 3 199 1
OBJECT_ID 220 4 0 .004545455
DATA_OBJECT_ID 167 4 49 .005988024
OBJECT_TYPE 9 8 0 .111111111
CREATED 12 8 0 .083333333
LAST_DDL_TIME 16 8 0 .0625
TIMESTAMP 13 20 0 .076923077
STATUS 1 6 0 1
TEMPORARY 2 2 0 .5
GENERATED 2 2 0 .5
SECONDARY 1 2 0 1
NAMESPACE 4 3 0 .25
EDITION_NAME 0 0 220 0
SHARING 2 10 0 .5
EDITIONABLE 1 2 206 1
ORACLE_MAINTAINED 1 2 0 1
APPLICATION 1 2 0 1
DEFAULT_COLLATION 1 7 136 1
DUPLICATED 1 2 0 1
SHARDED 1 2 0 1
CREATED_APPID 0 0 220 0
CREATED_VSNID 0 0 220 0
MODIFIED_APPID 0 0 220 0
MODIFIED_VSNID 0 0 220 0
26 rows selected.
SQL>
SQL> insert into t select * from dba_objects;
78329 rows created.
SQL> insert into t select * from dba_objects;
78329 rows created.
SQL> insert into t select * from dba_objects;
78329 rows created.
SQL> insert into t select * from dba_objects;
78329 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> select /*+ gather_plan_statistics */ count(created)
2 from t
3 where owner = 'SYS'
4 and object_type = 'JAVA CLASS';
COUNT(CREATED)
--------------
138424
1 row selected.
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3qyuxjtjy92m5, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(created) from t where owner
= 'SYS' and object_type = 'JAVA CLASS'
Plan hash value: 2143077847
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.08 | 5991 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.08 | 5991 |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 12 | 138K|00:00:00.07 | 5991 |
|* 3 | INDEX RANGE SCAN | IX | 1 | 110 | 207K|00:00:00.03 | 1218 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_TYPE"='JAVA CLASS')
3 - access("OWNER"='SYS')
22 rows selected.
SQL>
SQL> select sql_id, child_number,is_reoptimizable from v$sql where sql_id = '3qyuxjtjy92m5';
SQL_ID CHILD_NUMBER I
------------- ------------ -
3qyuxjtjy92m5 0 Y
1 row selected.
SQL>
SQL> exec dbms_spd.FLUSH_SQL_PLAN_DIRECTIVE
PL/SQL procedure successfully completed.
SQL>
SQL> select /*+ gather_plan_statistics */ count(created)
2 from t
3 where owner = 'SYS'
4 and object_type = 'JAVA CLASS';
COUNT(CREATED)
--------------
138424
1 row selected.
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3qyuxjtjy92m5, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ count(created) from t where owner
= 'SYS' and object_type = 'JAVA CLASS'
Plan hash value: 2143077847
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.06 | 5383 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.06 | 5383 |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 138K| 138K|00:00:00.05 | 5383 |
|* 3 | INDEX RANGE SCAN | IX | 1 | 220 | 207K|00:00:00.02 | 610 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_TYPE"='JAVA CLASS')
3 - access("OWNER"='SYS')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- statistics feedback used for this statement
- performance feedback used for this statement
- 1 Sql Plan Directive used for this statement
29 rows selected.
SQL>
SQL> select count(*) from dba_sql_plan_directives;
COUNT(*)
----------
354
1 row selected.
SQL>
SQL> set lines 60
SQL>
SQL> desc v$sql_plan
Name Null? Type
----------------------------- -------- --------------------
ADDRESS RAW(8)
HASH_VALUE NUMBER
SQL_ID VARCHAR2(13)
PLAN_HASH_VALUE NUMBER
FULL_PLAN_HASH_VALUE NUMBER
CHILD_ADDRESS RAW(8)
CHILD_NUMBER NUMBER
TIMESTAMP DATE
OPERATION VARCHAR2(30)
OPTIONS VARCHAR2(30)
OBJECT_NODE VARCHAR2(40)
OBJECT# NUMBER
OBJECT_OWNER VARCHAR2(128)
OBJECT_NAME VARCHAR2(128)
OBJECT_ALIAS VARCHAR2(261)
OBJECT_TYPE VARCHAR2(20)
OPTIMIZER VARCHAR2(20)
ID NUMBER
PARENT_ID NUMBER
DEPTH NUMBER
POSITION NUMBER
SEARCH_COLUMNS NUMBER
COST NUMBER
CARDINALITY NUMBER
BYTES NUMBER
OTHER_TAG VARCHAR2(35)
PARTITION_START VARCHAR2(64)
PARTITION_STOP VARCHAR2(64)
PARTITION_ID NUMBER
OTHER VARCHAR2(4000)
DISTRIBUTION VARCHAR2(20)
CPU_COST NUMBER
IO_COST NUMBER
TEMP_SPACE NUMBER
ACCESS_PREDICATES VARCHAR2(4000)
FILTER_PREDICATES VARCHAR2(4000)
PROJECTION VARCHAR2(4000)
TIME NUMBER
QBLOCK_NAME VARCHAR2(128)
REMARKS VARCHAR2(4000)
OTHER_XML CLOB
CON_ID NUMBER
SQL> set lines 200
SQL>
SQL>
SQL> select other from v$sql_plan
2 where other is not null
3 and sql_id = '3qyuxjtjy92m5'
4 and child_number > 0;
no rows selected
SQL>
SQL> select remarks from v$sql_plan
2 where remarks is not null
3 and sql_id = '3qyuxjtjy92m5'
4 and child_number > 0;
no rows selected
SQL>
SQL> select other_xml from v$sql_plan
2 where other_xml is not null
3 and sql_id = '3qyuxjtjy92m5'
4 and child_number > 0;
OTHER_XML
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
yes
SQL> select xmltype(other_xml) from v$sql_plan
2 where other_xml is not null
3 and sql_id = '3qyuxjtjy92m5'
4 and child_number > 0;
XMLTYPE(OTHER_XML)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
yes
yes
12.2.0.1
2
1068910003
2143077847
1068910003
0
1
1 row selected.
SQL>
SQL> explain plan for
2 select count(created)
3 from t
4 where owner = 'SYS'
5 and object_type = 'JAVA CLASS';
Explained.
SQL>
SQL> select * from table(dbms_xplan.display(format=>'all'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2143077847
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 23 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 143K| 3226K| 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IX | 220 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
3 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_TYPE"='JAVA CLASS')
3 - access("OWNER"='SYS')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("CREATED")[22]
2 - "CREATED"[DATE,7]
3 - "T".ROWID[ROWID,10]
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
35 rows selected.
SQL>
SQL> select * from table(dbms_xplan.display(format=>'all +metrics'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2143077847
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 23 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 143K| 3226K| 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IX | 220 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
3 - SEL$1 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_TYPE"='JAVA CLASS')
3 - access("OWNER"='SYS')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("CREATED")[22]
2 - "CREATED"[DATE,7]
3 - "T".ROWID[ROWID,10]
Sql Plan Directive information:
-------------------------------
Used directive ids:
14906410523430420431
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
41 rows selected.
SQL>
SQL>
Learning is not a spectator sport 