Friday, December 14, 2018

SQL Plan Directives Part II

 
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