This will be extension of the previous
post, incase if you have not gone through that, please read that before to
proceed here.
The below demo was from 12cR2 (12.2.0.1) database.
Starting with 12.2 the parameter OPTIMIZER_ADAPTIVE_FEATURES got depreciated and replaced with two new parameter OPTIMIZER_ADAPTIVE_PLANS to control adaptive plans (which is True by default) and OPTIMIZER_ADAPTIVE_STATISTICS to control adaptive statistics (which is False by default)
demo@ORA12C>
show parameter optimizer_adaptive_statistics
NAME TYPE VALUE
------------------------------------
----------- --------------
optimizer_adaptive_statistics boolean FALSE
Creating a table with appropriate stats as in
12.1
demo@ORA12C>
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.
demo@ORA12C>
set serveroutput off
demo@ORA12C>
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)
--------------------
30
demo@ORA12C>
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 | 30 |
30 |
| 3 |
HASH GROUP BY | |
1 | 30 | 30 |
|* 4 | TABLE ACCESS FULL| T |
1 | 4528
| 36224 |
--------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
4 - filter(("FLAG1"=1 AND
"FLAG2"=1 AND "FLAG3"=1 AND "FLAG4"=1))
22 rows
selected.
As in 12.1 we have misestimate the cardinality,
the cursor is marked as re-optimizable.
demo@ORA12C>
select sql_id,child_number,is_reoptimizable
2 from
v$sql
3
where sql_id ='7qsfw42st98nq';
SQL_ID CHILD_NUMBER I
-------------
------------ -
7qsfw42st98nq 0 Y
As in 12.1, cardinality feedback kicks in and
fixes the misestimated cardinality, on the very next execution.
demo@ORA12C>
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)
--------------------
30
demo@ORA12C>
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 | 30 |
30 |
| 3 |
HASH GROUP BY | |
1 | 30 | 30 |
|* 4 |
TABLE ACCESS FULL| T | 1 |
36224 |
36224 |
--------------------------------------------------------------------
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
26 rows selected.
demo@ORA12C>
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
demo@ORA12C>
We can see that a new SPD got created:
demo@ORA12C>
exec dbms_spd.flush_sql_plan_directive;
PL/SQL
procedure successfully completed.
demo@ORA12C>
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.1853E+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, FLAG2, FL
AG3, FLAG4]}</spd_text>
</spd_note>
However in 12.2 this newly created SPD is not get
used for subsequent execution of queries with similar predicates and still lead
to cardinality deviation.
demo@ORA12C>
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(*)
----------
36224
demo@ORA12C>
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.03 | 1580 |
| 1 |
SORT AGGREGATE | |
1 | 1 | 1 |00:00:00.03 | 1580 |
|* 2 |
TABLE ACCESS FULL| T | 1 |
4528 |
36224 |00:00:00.03 |
1580 |
-------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 - filter(("FLAG1"=1 AND
"FLAG2"=1 AND "FLAG3"=1 AND "FLAG4"=1))
20 rows
selected.
However setting this parameter OPTIMIZER_ADAPTIVE_STATISTICS to True (at session level)
and re-running the above test case, allowed SPD to kick in.
demo@ORA12C>
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(*)
----------
36224
demo@ORA12C>
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 | 1580 |
| 1 |
SORT AGGREGATE | |
1 | 1 | 1 |00:00:00.02 | 1580 |
|* 2 |
TABLE ACCESS FULL| T | 1 |
33267 |
36224 |00:00:00.02 |
1580 |
-------------------------------------------------------------------------------------
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@ORA12C>
This created a new directive type called DYNAMIC_SAMPLING_RESULT – a new feature of 12.2 - That is the results of this dynamically
sampled data is stored in the repository, so that the results can be reused by
other queries in future.
demo@ORA12C>
exec dbms_spd.flush_sql_plan_directive;
PL/SQL
procedure successfully completed.
demo@ORA12C>
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.5724E+19 DYNAMIC_SAMPLING_RESULT
YES USABLE
<spd_note> VERIFY
CARDINALITY ESTIMATE
<internal_state>NEW</internal_state>
<redundant>NO</redundant>
<spd_text>{(DEMO.T, num_rows=72447) -
(SQL_ID:cvgnggk0cmpaf, T.CARD=33267[-2 -
2])}</spd_text>
</spd_note>
8.2364E+18 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, FLAG2, FL
AG3, FLAG4]}</spd_text>
</spd_note>
Now the internal state of this directive is in MISSING_STATS, Oracle will create the extended stats when gathering stats next
time.
demo@ORA12C>
exec dbms_stats.gather_table_stats(user,'T',options=>'gather
auto',no_invalidate=>false);
PL/SQL
procedure successfully completed.
demo@ORA12C>
select column_name,num_buckets,histogram
2 from
user_tab_col_statistics
3
where table_name ='T'
4 and
column_name like 'SYS%';
no rows
selected
demo@ORA12C>
select * from user_stat_extensions where table_name ='T';
no rows
selected
Regathering the stats doesn’t created any
extended stats in 12.2 database as it did in 12.1 database.
However starting with 12.2 creating extended
stats is controlled by the newly added global preference AUTO_STAT_EXTENSIONS – which is OFF by default.
Setting that preference to ON and regathering the
stats, resulted in creation of extended stats (similar to 12.1 database
behavior)
demo@ORA12C>
select dbms_stats.get_prefs('AUTO_STAT_EXTENSIONS') from dual;
DBMS_STATS.GET_PREFS('AUTO_STAT_EXTENSIONS')
----------------------------------------------------------------------------------------------------------------------------------------
OFF
demo@ORA12C>
exec dbms_stats.set_global_prefs('AUTO_STAT_EXTENSIONS','ON') ;
PL/SQL
procedure successfully completed.
demo@ORA12C>
select dbms_stats.get_prefs('AUTO_STAT_EXTENSIONS') from dual;
DBMS_STATS.GET_PREFS('AUTO_STAT_EXTENSIONS')
----------------------------------------------------------------------------------------------------------------------------------------
ON
demo@ORA12C>
exec dbms_stats.gather_table_stats(user,'T',options=>'gather
auto',no_invalidate=>false);
PL/SQL
procedure successfully completed.
demo@ORA12C>
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@ORA12C>
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
demo@ORA12C>
Once the query is executed again, the internal
state changes to
HAS_STATS (same as 12.1)
demo@ORA12C>
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)
--------------
1462835630
demo@ORA12C>
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.02 | 1580 |
| 1 |
SORT AGGREGATE | |
1 | 1 | 1 |00:00:00.02 | 1580 |
|* 2 |
TABLE ACCESS FULL| T | 1 |
33267 |
36224 |00:00:00.01 | 1580
|
-------------------------------------------------------------------------------------
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@ORA12C>
exec dbms_spd.flush_sql_plan_directive;
PL/SQL
procedure successfully completed.
demo@ORA12C>
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.5724E+19 DYNAMIC_SAMPLING_RESULT YES
USABLE <spd_note> VERIFY CARDINALITY EST
<internal_state>NEW</internal_state>
<redundant>NO</redundant>
<spd_text>{(DEMO.T,
num_rows=72447) -
(SQL_ID:cvgnggk0cmpaf, T.CARD=33267[-2 -
2])}</spd_text>
</spd_note>
8.2364E+18 DYNAMIC_SAMPLING YES SUPERSEDED <spd_note> SINGLE TABLE
CARDINALI
<internal_state>HAS_STATS</internal_st
ate>
<redundant>NO</redundant>
<spd_text>{EC(DEMO.T)[FLAG1, FLAG2, FL
AG3, FLAG4]}</spd_text>
</spd_note>
The next execution of the query uses the stats
instead of SPD.
demo@ORA12C>
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)
--------------
116844
demo@ORA12C>
demo@ORA12C>
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.01 | 1580 |
| 1 |
SORT AGGREGATE | |
1 | 1 | 1 |00:00:00.01 | 1580 |
|* 2 |
TABLE ACCESS FULL| T | 1 |
36224 |
36224 |00:00:00.01 |
1580 |
-------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 - filter(("FLAG1"=1 AND
"FLAG2"=1 AND "FLAG3"=1 AND "FLAG4"=1))
20 rows
selected.
demo@ORA12C>
So in summary there were some changes in 12.2
compared with 12.1 database.
·
Having OPTIMIZER_ADAPTIVE_STATISTICS = False (by default) – doesn’t disable the creation of SPD, instead
it prevents them from being used in conjunction with Dynamic statistics to
influence new execution plans – Hence SPD got created but doesn’t involve in
further optimization – subsequent execution of sql with similar combination of
predicates produced cardinality mismatch – “statistics feedback” kick-in again.
·
Starting with 12.2
automatic column group creation is controlled by DBMS_STATS preference AUTO_STAT_EXTENSIONS – which is OFF by default, setting it to ON and re-running my demo,
created the extended stats automatically.
·
Starting with 12.2
new SPD of type DYNAMIC_SAMPLING_RESULT will be created, that is the results of
this dynamically sampled data is stored in the repository, so that the results
can be reused by other queries in future.
No comments:
Post a Comment