By Franck Pachot

.
This year is the year of migration to 12c. Each Oracle version had its CBO feature that make it challenging. The most famous was the bind variable peeking in 9iR2. Cardinality feedback in 11g also came with some surprises. 12c comes with SPD in any edition, which is accompanied by Adaptive Dynamic Sampling. If you want to know more about them, next date is in Switzerland:http://www.soug.ch/events/sig-150521-agenda.html

SQL Plan Directives in USABLE/MISSING_STATS state can create column groups and extended stats on it at the next dbms_stats gathering. When the next usage of the SPD validates that static statistics are sufficient to get good cardinality estimates, then the SPD goes into the SUPERSEDED/HAS_STATS state. If an execution still see misestimates on them, then the state will go to SUPERSEDED/PERMANENT and dynamic sampling will be used forever. Note that disabled SPD can still trigger the creation of extended statistics but not the dynamix sampling.

Query

If you want to match the directives (from SQL_PLAN_DIRECTIVES) with the extended statistics (from DBA_STATS_EXTENSIONS) there is no direct link. Both list the columns, but not in the same order and not in the same format:

SQL> select extract(notes,'/spd_note/spd_text/text()').getStringVal() from dba_sql_plan_directives where directive_id in ('11620983915867293627','16006171197187894917');

EXTRACT(NOTES,'/SPD_NOTE/SPD_TEXT/TEXT()').GETSTRINGVAL()
--------------------------------------------------------------------------------
{ECJ(STOPSYS.EDGE)[CHILDID, CHILDTYPE, EDGETYPE]}
{EC(STOPSYS.EDGE)[CHILDID, CHILDTYPE, EDGETYPE]}

those SPD has been responsible for the creation of following column groups:

SQL> select owner,table_name,extension from dba_stat_extensions where extension_name='SYS_STSDXN5VXXKAWUPN9AEO8$$W$J';

OWNER    TABLE_NA EXTENSION
-------- -------- ------------------------------------------------------------
STOPSYS  EDGE     ("CHILDTYPE","CHILDID","EDGETYPE")

So I’ve made the following query to match both:

SQL> column owner format a8
SQL> column table_name format a30
SQL> column columns format a40 trunc
SQL> column extension_name format a20
SQL> column internal_state format a9
SQL>
SQL> select * from (
    select owner,table_name,listagg(column_name,',')within group(order by column_name) columns
     , extension_name
    from dba_tab_columns join dba_stat_extensions using(owner,table_name)
    where extension like '%"'||column_name||'"%'
    group by owner,table_name,extension_name
    order by owner,table_name,columns
    ) full outer join (
    select owner,object_name table_name,listagg(subobject_name,',')within group(order by subobject_name) columns
     , directive_id,max(extract(dba_sql_plan_directives.notes,'/spd_note/internal_state/text()').getStringVal()) internal_state
    from dba_sql_plan_dir_objects join dba_sql_plan_directives using(directive_id)
    where object_type='COLUMN' and directive_id in (
        select directive_id
        from dba_sql_plan_dir_objects
        where extract(notes,'/obj_note/equality_predicates_only/text()').getStringVal()='YES'
          and extract(notes,'/obj_note/simple_column_predicates_only/text()').getStringVal()='YES'
        and object_type='TABLE'
    )
    group by owner,object_name,directive_id
    ) using (owner,table_name,columns)
   order by owner,table_name,columns
  ;

This is just the first draft. I’ll probably improve it when needed and your comments on that blog will help.

Example

Here is an exemple of the output:

OWNER  TABLE_NAME                COLUMNS             EXTENSION_ DIRECTIVE_ID INTERNAL_
------ ------------------------- ------------------- ---------- ------------ ---------
STE1SY AUTOMANAGE_STATS          TYPE                             1.7943E+18 NEW
STE1SY CHANGELOG                 NODEID,NODETYPE                  2.2440E+18 PERMANENT
...
SYS    AUX_STATS$                SNAME                            9.2865E+17 HAS_STATS
SYS    CDEF$                     OBJ#                             1.7472E+19 HAS_STATS
SYS    COL$                      NAME                             5.6834E+18 HAS_STATS
SYS    DBFS$_MOUNTS              S_MOUNT,S_OWNER     SYS_NC0000
SYS    ICOL$                     OBJ#                             6.1931E+18 HAS_STATS
SYS    METANAMETRANS$            NAME                             1.4285E+19 MISSING_S
SYS    OBJ$                      NAME,SPARE3                      1.4696E+19 NEW
SYS    OBJ$                      OBJ#                             1.6336E+19 HAS_STATS
SYS    OBJ$                      OWNER#                           6.3211E+18 PERMANENT
SYS    OBJ$                      TYPE#                            1.5774E+19 PERMANENT
SYS    PROFILE$                  PROFILE#                         1.7989E+19 HAS_STATS
SYS    SCHEDULER$_JOB            JOB_STATUS          SYS_NC0006
SYS    SCHEDULER$_JOB            NEXT_RUN_DATE       SYS_NC0005
SYS    SCHEDULER$_WINDOW         NEXT_START_DATE     SYS_NC0002
SYS    SYN$                      OBJ#                             1.4900E+19 HAS_STATS
SYS    SYN$                      OWNER                            1.5782E+18 HAS_STATS
SYS    SYSAUTH$                  GRANTEE#                         8.1545E+18 PERMANENT
SYS    TRIGGER$                  BASEOBJECT                       6.0759E+18 HAS_STATS
SYS    USER$                     NAME                             1.1100E+19 HAS_STATS
SYS    WRI$_ADV_EXECUTIONS       TASK_ID                          1.5494E+18 PERMANENT
SYS    WRI$_ADV_FINDINGS         TYPE                             1.4982E+19 HAS_STATS
SYS    WRI$_OPTSTAT_AUX_HISTORY  SAVTIME             SYS_NC0001
SYS    WRI$_OPTSTAT_HISTGRM_HIST SAVTIME             SYS_NC0001

Conclusion

Because SPD are quite new, I’ll conclude with a list of questions:

  • Do you still need extended stats when a SPD is in PERMANENT state?
  • Do you send to developers the list of extended stats for which SPD is in HAS_STATS, so that they integrate them in their data model? Then, do you drop the SPD when new version is released or wait for retention?
  • When you disable a SPD and an extended statistic is created, do you re-enable the SPD in order to have it in HAS_STAT?
  • Having too many extended statistics have an overhead during statistics gathering (especially when having histograms on them). But it helps to have better estimations. Do you think that having a lot of HAS_STATS is a good thing or not?
  • Having too many usable (MISSING_STATS or PERMANENT) SPD has an overhead during optimization (dynamic sampling) . But it helps to have better estimations. Do you think that having a lot of PERMANENT is a good thing or not?
  • Do you think that only bad data models have a lot of SPD? Then why SYS (the oldest data model optimized at each release) is the schema with most SPD?
  • Do you keep your SQL Profiles when upgrading, or do you think that SPD can replace most of them.

Don’t ignore them. SQL Plan Directive is a gread feature but you have to manage them.