|
|
This was first published on https://blog.dbi-services.com/matching-sql-plan-directives-and-extended-stats (2015-05-18)
Republishing here for new followers. The content is related to the the versions available at the publication date
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.
If you want to match the directives (from SQL_PLAN_DIRECTIVES) with the extended statistics (from DBA_STATS_EXTENSION) 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.
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
Because SPD are quite new, I’ll conclude with a list of questions:
Don’t ignore them. SQL Plan Directive is a gread feature but you have to manage them.