SQL Plan directives (SPD) were introduced in
Oracle 12cR1 to provide additional information to optimizer to generate more
optimal plans, they can even help the optimizer to record a missing extension.
The key point is – directives are not tied to SQL
statements, instead they are defined on query expressions.
The below demo is from 12cR1 (12.1.0.2) database.
First created a table: with columns flag1, flag2,
flag3 and flag4 correlation between them (all columns have a value 0 or all
have a value 1)
demo@ORA12CR1>
create table t
2 as
3
select a.*,
4
mod(rownum,2) as flag1,
5
mod(rownum,2) as flag2,
6
mod(rownum,2) as flag3,
7
mod(rownum,2) as flag4
8 from
all_objects a;
Table created.
Let’s run a query and verify the plans.
demo@ORA12CR1>
select /*+ gather_plan_statistics */ count(distinct owner)
2 from
t
3
where flag1 = 1
4 and
flag2 = 1
5 and
flag3 = 1
6 and
flag4 = 1 ;
COUNT(DISTINCTOWNER)
--------------------
31
demo@ORA12CR1>
select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7qsfw42st98nq, child number 0
-------------------------------------
select /*+
gather_plan_statistics */ count(distinct owner) from t where
flag1 = 1 and
flag2 = 1 and flag3 = 1 and flag4 = 1
Plan hash
value: 2359337548
--------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | |
1 |
| 1 |
SORT AGGREGATE | |
1 | 1 | 1 |
| 2 |
VIEW | VW_DAG_0
| 1 | 32 |
31 |
| 3 |
HASH GROUP BY | |
1 | 32 | 31 |
|* 4 |
TABLE ACCESS FULL| T | 1 |
5723 |
45787 |
--------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
4 - filter(("FLAG1"=1 AND
"FLAG2"=1 AND "FLAG3"=1 AND "FLAG4"=1))
There is a cardinality deviation and the optimizer has detected that
and placed the cursor on IS_REOPTIMIZABLE = Y
demo@ORA12CR1>
select sql_id,child_number,is_reoptimizable
2 from
v$sql
3 where sql_id ='7qsfw42st98nq';
SQL_ID CHILD_NUMBER I
-------------
------------ -
7qsfw42st98nq 0 Y
Rerunning the query again uses statistics feedback.
demo@ORA12CR1>
select /*+ gather_plan_statistics */ count(distinct owner)
2 from
t
3
where flag1 = 1
4 and
flag2 = 1
5 and
flag3 = 1
6 and
flag4 = 1 ;
COUNT(DISTINCTOWNER)
--------------------
31
demo@ORA12CR1>
select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7qsfw42st98nq, child number 1
-------------------------------------
select /*+
gather_plan_statistics */ count(distinct owner) from t where
flag1 = 1 and
flag2 = 1 and flag3 = 1 and flag4 = 1
Plan hash
value: 2359337548
--------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | |
1 |
| 1 |
SORT AGGREGATE | |
1 | 1 | 1 |
| 2 |
VIEW | VW_DAG_0
| 1 |
32 | 31 |
| 3 |
HASH GROUP BY | |
1 | 32 | 31 |
|* 4 |
TABLE ACCESS FULL| T | 1 |
45787 |
45787 |
--------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
4 - filter(("FLAG1"=1 AND
"FLAG2"=1 AND "FLAG3"=1 AND "FLAG4"=1))
Note
-----
- statistics feedback used for this
statement
demo@ORA12CR1>
select sql_id,child_number,is_reoptimizable
2 from
v$sql
3
where sql_id ='7qsfw42st98nq';
SQL_ID CHILD_NUMBER I
-------------
------------ -
7qsfw42st98nq 0 Y
7qsfw42st98nq 1 N
The computed cardinality is correct now.
Manually flushing (by default the database flushes this for every 15
min) the SPD to SYSAUX tablespace, we can see them with “Internal_state = NEW”
demo@ORA12CR1>
exec dbms_spd.flush_sql_plan_directive;
PL/SQL
procedure successfully completed.
demo@ORA12CR1>
select directive_id,type,enabled,state,notes,reason
2 from
dba_sql_plan_directives
3
where directive_id in ( select directive_id from
dba_sql_plan_dir_objects where owner = user
4 and
object_name ='T' );
DIRECTIVE_ID
TYPE ENA STATE NOTES REASON
------------
---------------- --- ---------- ----------------------------------------
------------------------------------
1.5032E+19 DYNAMIC_SAMPLING YES USABLE <spd_note> SINGLE TABLE
CARDINALITY MISESTIMATE
<internal_state>NEW</internal_state>
<redundant>NO</redundant>
<spd_text>{EC(DEMO.T)[FLAG1, FLA
G2, FLAG3, FLAG4]}</spd_text>
</spd_note>
Executing a different queries with the same type of predicates will use
the SPD and dynamically gather stats (due to missing stats on column grouping)
demo@ORA12CR1>
select /*+ gather_plan_statistics */ count(*)
2 from
t
3
where flag1 = 1
4 and
flag2 = 1
5 and
flag3 = 1
6 and
flag4 = 1 ;
COUNT(*)
----------
45787
demo@ORA12CR1>
select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0n020wm1vka2r, child number 0
-------------------------------------
select /*+
gather_plan_statistics */ count(*) from t where flag1 = 1
and flag2 = 1
and flag3 = 1 and flag4 = 1
Plan hash
value: 2966233522
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows
| A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | |
1 |00:00:00.02 | 1672 |
| 1 |
SORT AGGREGATE | |
1 | 1 | 1 |00:00:00.02 | 1672 |
|* 2 |
TABLE ACCESS FULL| T | 1 |
44616 |
45787 |00:00:00.02 |
1672 |
-------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 - filter(("FLAG1"=1 AND
"FLAG2"=1 AND "FLAG3"=1 AND "FLAG4"=1))
Note
-----
- dynamic statistics
used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this
statement
The internal state of directive changed to MISSING STATS
demo@ORA12CR1>
exec dbms_spd.flush_sql_plan_directive;
PL/SQL
procedure successfully completed.
demo@ORA12CR1>
select directive_id,type,enabled,state,notes,reason
2 from
dba_sql_plan_directives
3
where directive_id in ( select directive_id from
dba_sql_plan_dir_objects where owner = user
4 and
object_name ='T' );
DIRECTIVE_ID
TYPE ENA STATE NOTES REASON
------------
---------------- --- ---------- ----------------------------------------
------------------------------------
1.5032E+19 DYNAMIC_SAMPLING YES USABLE <spd_note> SINGLE TABLE
CARDINALITY MISESTIMATE
<internal_state>MISSING_STATS</interna
l_state>
<redundant>NO</redundant>
<spd_text>{EC(DEMO.T)[FLAG1, FLA
G2, FLAG3, FLAG4]}</spd_text>
</spd_note>
The SPD will be used until fresh stats are gathered. Oracle will then
create extended (multi column) statistics.
demo@ORA12CR1>
exec dbms_stats.gather_table_stats(user,'T',options=>'gather
auto',no_invalidate=>false);
PL/SQL
procedure successfully completed.
demo@ORA12CR1>
select column_name,num_buckets,histogram
2 from
user_tab_col_statistics
3
where table_name ='T'
4 and
column_name like 'SYS%';
COLUMN_NAME NUM_BUCKETS HISTOGRAM
--------------------
----------- ---------------
SYS_STS##3#UAEFD##2J 2 FREQUENCY
M$8KNJ1P32
demo@ORA12CR1>
col extension_name format a30
demo@ORA12CR1>
col extension format a45
demo@ORA12CR1>
select * from user_stat_extensions where table_name ='T';
TABLE_NAME EXTENSION_NAME EXTENSION CREATO DRO
--------------------
------------------------------ ---------------------------------------------
------ ---
T
SYS_STS##3#UAEFD##2JM$8KNJ1P32
("FLAG1","FLAG2","FLAG3","FLAG4") SYSTEM YES
Running a sql again, will use SPD but will change the internal state to
HAS_STATS
demo@ORA12CR1>
select /*+ gather_plan_statistics */ sum(object_id)
2 from
t
3
where flag1 = 1
4 and
flag2 = 1
5 and
flag3 = 1
6 and
flag4 = 1 ;
SUM(OBJECT_ID)
--------------
1888859125
demo@ORA12CR1>
select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID f8z55za1k8sfw, child number 0
-------------------------------------
select /*+
gather_plan_statistics */ sum(object_id) from t where flag1
= 1 and flag2 =
1 and flag3 = 1 and flag4 = 1
Plan hash value:
2966233522
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows
| A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | |
1 |00:00:00.03 | 1672 |
| 1 |
SORT AGGREGATE | |
1 | 1 | 1 |00:00:00.03 | 1672 |
|* 2 |
TABLE ACCESS FULL| T | 1 |
44616 |
45787 |00:00:00.02 |
1672 |
-------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 - filter(("FLAG1"=1 AND
"FLAG2"=1 AND "FLAG3"=1 AND "FLAG4"=1))
Note
-----
- dynamic statistics
used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this
statement
25 rows
selected.
demo@ORA12CR1>
exec dbms_spd.flush_sql_plan_directive;
PL/SQL
procedure successfully completed.
demo@ORA12CR1>
select directive_id,type,enabled,state,notes,reason
2 from
dba_sql_plan_directives
3
where directive_id in ( select directive_id from
dba_sql_plan_dir_objects where owner = user
4 and
object_name ='T' );
DIRECTIVE_ID
TYPE ENA STATE NOTES REASON
------------
---------------- --- ---------- ----------------------------------------
------------------------------------
1.5032E+19 DYNAMIC_SAMPLING YES SUPERSEDED
<spd_note>
SINGLE TABLE CARDINALITY MISESTIMATE
<internal_state>HAS_STATS</internal_st
ate>
<redundant>NO</redundant>
<spd_text>{EC(DEMO.T)[FLAG1, FLA
G2, FLAG3, FLAG4]}</spd_text>
</spd_note>
With the extended stats in dictionary, the subsequent execution of sql
with similar predicates will make use of extended stats instead of directives.
demo@ORA12CR1>
select /*+ gather_plan_statistics */ max(object_id)
2 from
t
3
where flag1 = 1
4 and
flag2 = 1
5 and
flag3 = 1
6 and
flag4 = 1 ;
MAX(OBJECT_ID)
--------------
100689
demo@ORA12CR1>
select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 38x0rq12q1squ, child number 0
-------------------------------------
select /*+
gather_plan_statistics */ max(object_id) from t where flag1
= 1 and flag2 =
1 and flag3 = 1 and flag4 = 1
Plan hash
value: 2966233522
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows
| A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | |
1 |00:00:00.03 | 1672 |
| 1 |
SORT AGGREGATE | |
1 | 1 | 1
|00:00:00.03 | 1672 |
|* 2 |
TABLE ACCESS FULL| T | 1 |
45787 | 45787 |00:00:00.02 | 1672 |
-------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 - filter(("FLAG1"=1 AND
"FLAG2"=1 AND "FLAG3"=1 AND "FLAG4"=1))
20 rows
selected.
demo@ORA12CR1>
In the next blog post, will show you how this feature got changed in
12.2 database.
No comments:
Post a Comment