http:\/\/www.soug.ch\/events\/sig-150521-agenda.html<\/a><\/p>\nSQL 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.<\/p>\n
Query<\/h3>\n
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:<\/p>\n
SQL> select extract(notes,'\/spd_note\/spd_text\/text()').getStringVal() from dba_sql_plan_directives where directive_id in ('11620983915867293627','16006171197187894917');\n\nEXTRACT(NOTES,'\/SPD_NOTE\/SPD_TEXT\/TEXT()').GETSTRINGVAL()\n--------------------------------------------------------------------------------\n{ECJ(STOPSYS.EDGE)[CHILDID, CHILDTYPE, EDGETYPE]}\n{EC(STOPSYS.EDGE)[CHILDID, CHILDTYPE, EDGETYPE]}\n\n<\/code><\/pre>\nthose SPD has been responsible for the creation of following column groups:<\/p>\n
SQL> select owner,table_name,extension from dba_stat_extensions where extension_name='SYS_STSDXN5VXXKAWUPN9AEO8$$W$J';\n\nOWNER TABLE_NA EXTENSION\n-------- -------- ------------------------------------------------------------\nSTOPSYS EDGE (\"CHILDTYPE\",\"CHILDID\",\"EDGETYPE\")\n\n<\/code><\/pre>\nSo I’ve made the following query to match both:<\/p>\n
SQL> column owner format a8\nSQL> column table_name format a30\nSQL> column columns format a40 trunc\nSQL> column extension_name format a20\nSQL> column internal_state format a9\nSQL>\nSQL> select * from (\n select owner,table_name,listagg(column_name,',')within group(order by column_name) columns\n , extension_name\n from dba_tab_columns join dba_stat_extensions using(owner,table_name)\n where extension like '%\"'||column_name||'\"%'\n group by owner,table_name,extension_name\n order by owner,table_name,columns\n ) full outer join (\n select owner,object_name table_name,listagg(subobject_name,',')within group(order by subobject_name) columns\n , directive_id,max(extract(dba_sql_plan_directives.notes,'\/spd_note\/internal_state\/text()').getStringVal()) internal_state\n from dba_sql_plan_dir_objects join dba_sql_plan_directives using(directive_id)\n where object_type='COLUMN' and directive_id in (\n select directive_id\n from dba_sql_plan_dir_objects\n where extract(notes,'\/obj_note\/equality_predicates_only\/text()').getStringVal()='YES'\n and extract(notes,'\/obj_note\/simple_column_predicates_only\/text()').getStringVal()='YES'\n and object_type='TABLE'\n )\n group by owner,object_name,directive_id\n ) using (owner,table_name,columns)\n order by owner,table_name,columns\n ;\n<\/code><\/pre>\nThis is just the first draft. I’ll probably improve it when needed and your comments on that blog will help.<\/p>\n
Example<\/h3>\n
Here is an exemple of the output:<\/p>\n
OWNER TABLE_NAME COLUMNS EXTENSION_ DIRECTIVE_ID INTERNAL_\n------ ------------------------- ------------------- ---------- ------------ ---------\nSTE1SY AUTOMANAGE_STATS TYPE 1.7943E+18 NEW\nSTE1SY CHANGELOG NODEID,NODETYPE 2.2440E+18 PERMANENT\n...\nSYS AUX_STATS$ SNAME 9.2865E+17 HAS_STATS\nSYS CDEF$ OBJ# 1.7472E+19 HAS_STATS\nSYS COL$ NAME 5.6834E+18 HAS_STATS\nSYS DBFS$_MOUNTS S_MOUNT,S_OWNER SYS_NC0000\nSYS ICOL$ OBJ# 6.1931E+18 HAS_STATS\nSYS METANAMETRANS$ NAME 1.4285E+19 MISSING_S\nSYS OBJ$ NAME,SPARE3 1.4696E+19 NEW\nSYS OBJ$ OBJ# 1.6336E+19 HAS_STATS\nSYS OBJ$ OWNER# 6.3211E+18 PERMANENT\nSYS OBJ$ TYPE# 1.5774E+19 PERMANENT\nSYS PROFILE$ PROFILE# 1.7989E+19 HAS_STATS\nSYS SCHEDULER$_JOB JOB_STATUS SYS_NC0006\nSYS SCHEDULER$_JOB NEXT_RUN_DATE SYS_NC0005\nSYS SCHEDULER$_WINDOW NEXT_START_DATE SYS_NC0002\nSYS SYN$ OBJ# 1.4900E+19 HAS_STATS\nSYS SYN$ OWNER 1.5782E+18 HAS_STATS\nSYS SYSAUTH$ GRANTEE# 8.1545E+18 PERMANENT\nSYS TRIGGER$ BASEOBJECT 6.0759E+18 HAS_STATS\nSYS USER$ NAME 1.1100E+19 HAS_STATS\nSYS WRI$_ADV_EXECUTIONS TASK_ID 1.5494E+18 PERMANENT\nSYS WRI$_ADV_FINDINGS TYPE 1.4982E+19 HAS_STATS\nSYS WRI$_OPTSTAT_AUX_HISTORY SAVTIME SYS_NC0001\nSYS WRI$_OPTSTAT_HISTGRM_HIST SAVTIME SYS_NC0001\n<\/code><\/pre>\nConclusion<\/h3>\n
Because SPD are quite new, I’ll conclude with a list of questions:<\/p>\n
\n- Do you still need extended stats when a SPD is in PERMANENT state?<\/li>\n
- 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?<\/li>\n
- 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?<\/li>\n
- 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?<\/li>\n
- 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?<\/li>\n
- 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?<\/li>\n
- Do you keep your SQL Profiles when upgrading, or do you think that SPD can replace most of them.<\/li>\n<\/ul>\n
Don’t ignore them. SQL Plan Directive is a gread feature but you have to manage them.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198,59],"tags":[209,67],"type_dbi":[],"class_list":["post-4791","post","type-post","status-publish","format-standard","hentry","category-database-management","category-oracle","tag-oracle-12c","tag-performance"],"acf":[],"yoast_head":"\n
Matching SQL Plan Directives and extended stats - dbi Blog<\/title>\n<meta name=\"description\" content=\"SQL Plan Directives and extended statistics.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/matching-sql-plan-directives-and-extended-stats\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Matching SQL Plan Directives and extended stats\" \/>\n<meta property=\"og:description\" content=\"SQL Plan Directives and extended statistics.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/matching-sql-plan-directives-and-extended-stats\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-05-18T11:02:51+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/matching-sql-plan-directives-and-extended-stats\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/matching-sql-plan-directives-and-extended-stats\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Matching SQL Plan Directives and extended stats\",\"datePublished\":\"2015-05-18T11:02:51+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/matching-sql-plan-directives-and-extended-stats\/\"},\"wordCount\":503,\"commentCount\":0,\"keywords\":[\"Oracle 12c\",\"Performance\"],\"articleSection\":[\"Database management\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/matching-sql-plan-directives-and-extended-stats\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/matching-sql-plan-directives-and-extended-stats\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/matching-sql-plan-directives-and-extended-stats\/\",\"name\":\"Matching SQL Plan Directives and extended stats - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2015-05-18T11:02:51+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"SQL Plan Directives and extended statistics.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/matching-sql-plan-directives-and-extended-stats\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/matching-sql-plan-directives-and-extended-stats\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/matching-sql-plan-directives-and-extended-stats\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Matching SQL Plan Directives and extended stats\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Matching SQL Plan Directives and extended stats - dbi Blog","description":"SQL Plan Directives and extended statistics.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/matching-sql-plan-directives-and-extended-stats\/","og_locale":"en_US","og_type":"article","og_title":"Matching SQL Plan Directives and extended stats","og_description":"SQL Plan Directives and extended statistics.","og_url":"https:\/\/www.dbi-services.com\/blog\/matching-sql-plan-directives-and-extended-stats\/","og_site_name":"dbi Blog","article_published_time":"2015-05-18T11:02:51+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/matching-sql-plan-directives-and-extended-stats\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/matching-sql-plan-directives-and-extended-stats\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Matching SQL Plan Directives and extended stats","datePublished":"2015-05-18T11:02:51+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/matching-sql-plan-directives-and-extended-stats\/"},"wordCount":503,"commentCount":0,"keywords":["Oracle 12c","Performance"],"articleSection":["Database management","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/matching-sql-plan-directives-and-extended-stats\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/matching-sql-plan-directives-and-extended-stats\/","url":"https:\/\/www.dbi-services.com\/blog\/matching-sql-plan-directives-and-extended-stats\/","name":"Matching SQL Plan Directives and extended stats - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-05-18T11:02:51+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"SQL Plan Directives and extended statistics.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/matching-sql-plan-directives-and-extended-stats\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/matching-sql-plan-directives-and-extended-stats\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/matching-sql-plan-directives-and-extended-stats\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Matching SQL Plan Directives and extended stats"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4791","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=4791"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4791\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=4791"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=4791"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=4791"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=4791"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}