web.archive.org<\/a><\/i><\/p>\nDemo table<\/h3>\n
I create a table for the demo. The CTAS gathers statistics (12c online statistics gathering) with one row and then I insert more rows:<\/p>\n
\n\n10:33:56 SQL> create table DEMO as select rownum n from dual;\nTable DEMO created.\n10:33:56 SQL> insert into DEMO select rownum n from xmltable('1 to 41');\n41 rows inserted.\n10:33:56 SQL> commit;\nCommit complete.\n\n<\/code><\/pre>\nThe estimations are stale: estimates 1 row (E-Rows) but 42 actual rows (A-Rows)<\/p>\n
\n\n10:33:56 SQL> select \/*+ gather_plan_statistics *\/ count(*) from DEMO;\n\n COUNT(*) \n -------- \n 42 \n\n10:33:57 SQL> select * from table(dbms_xplan.display_cursor(format=>'basic +rows +rowstats last'));\n\nPLAN_TABLE_OUTPUT \n----------------- \nEXPLAINED SQL STATEMENT: \n------------------------ \nselect \/*+ gather_plan_statistics *\/ count(*) from DEMO \n \nPlan hash value: 2180342005 \n \n-------------------------------------------------------------- \n| Id | Operation | Name | Starts | E-Rows | A-Rows | \n-------------------------------------------------------------- \n| 0 | SELECT STATEMENT | | 1 | | 1 | \n| 1 | SORT AGGREGATE | | 1 | 1 | 1 | \n| 2 | TABLE ACCESS FULL| DEMO | 1 | 1 | 42 | \n-------------------------------------------------------------- \n\n<\/code><\/pre>\nPending Statistics<\/h3>\n
Here we are: I want to gather statistics on this table. But I will lower all risks by not publishing them immediately. Current statistics preferences are set to PUBLISH=TRUE:<\/p>\n
\n\n10:33:58 SQL> select num_rows,cast(last_analyzed as timestamp),dbms_stats.get_prefs('PUBLISH',owner,table_name) from dba_tab_statistics where owner='DEMO' and table_name in ('DEMO');\n\n NUM_ROWS CAST(LAST_ANALYZEDASTIMESTAMP) DBMS_STATS.GET_PREFS('PUBLISH',OWNER,TABLE_NAME) \n -------- ------------------------------ ------------------------------------------------ \n 1 12-SEP-18 10.33.56.000000000 AM TRUE \n \n<\/code><\/pre>\nI set it to FALSE:<\/p>\n
\n\n10:33:59 SQL> exec dbms_stats.set_table_prefs('DEMO','DEMO','publish','false');\n\nPL\/SQL procedure successfully completed.\n\n10:34:00 SQL> select num_rows,cast(last_analyzed as timestamp),dbms_stats.get_prefs('PUBLISH',owner,table_name) from dba_tab_statistics where owner='DEMO' and table_name in ('DEMO');\n\n NUM_ROWS CAST(LAST_ANALYZEDASTIMESTAMP) DBMS_STATS.GET_PREFS('PUBLISH',OWNER,TABLE_NAME) \n -------- ------------------------------ ------------------------------------------------ \n 1 12-SEP-18 10.33.56.000000000 AM FALSE \n \n<\/code><\/pre>\nI’m now gathering stats as I want to:<\/p>\n
\n\n10:34:01 SQL> exec dbms_stats.gather_table_stats('DEMO','DEMO');\nPL\/SQL procedure successfully completed.\n\n<\/code><\/pre>\nTest Pending Statistics<\/h3>\n
They are not published. But to test my queries with those new stats, I can set my session to use pending statistics:<\/p>\n
\n\n10:34:02 SQL> alter session set optimizer_use_pending_statistics=true;\nSession altered.\n\n<\/code><\/pre>\nRunning my query again, I can see the good estimations (E-Rows=A-Rows)<\/p>\n
\n\n10:34:03 SQL> select \/*+ gather_plan_statistics *\/ count(*) from DEMO;\n\n COUNT(*) \n -------- \n 42 \n\n10:34:04 SQL> select * from table(dbms_xplan.display_cursor(format=>'basic +rows +rowstats last'));\n\nPLAN_TABLE_OUTPUT \n----------------- \nEXPLAINED SQL STATEMENT: \n------------------------ \nselect \/*+ gather_plan_statistics *\/ count(*) from DEMO \n \nPlan hash value: 2180342005 \n \n-------------------------------------------------------------- \n| Id | Operation | Name | Starts | E-Rows | A-Rows | \n-------------------------------------------------------------- \n| 0 | SELECT STATEMENT | | 1 | | 1 | \n| 1 | SORT AGGREGATE | | 1 | 1 | 1 | \n| 2 | TABLE ACCESS FULL| DEMO | 1 | 42 | 42 | \n-------------------------------------------------------------- \n\n<\/code><\/pre>\nThe published statistics still show 1 row:<\/p>\n
\n\n10:34:05 SQL> select num_rows,cast(last_analyzed as timestamp),dbms_stats.get_prefs('PUBLISH',owner,table_name) from dba_tab_statistics where owner='DEMO' and table_name in ('DEMO');\n\n NUM_ROWS CAST(LAST_ANALYZEDASTIMESTAMP) DBMS_STATS.GET_PREFS('PUBLISH',OWNER,TABLE_NAME) \n -------- ------------------------------ ------------------------------------------------ \n 1 12-SEP-18 10.33.56.000000000 AM FALSE \n \n<\/code><\/pre>\nBut I can query the pending ones before publishing them:<\/p>\n
\n\n10:34:05 SQL> c\/dba_tab_statistics\/dba_tab_pending_stats\n 1* select num_rows,cast(last_analyzed as timestamp),dbms_stats.get_prefs('PUBLISH',owner,table_name) from dba_tab_pending_stats where owner='DEMO' and table_name in ('DEMO');\n10:34:05 SQL> \/\n\n NUM_ROWS CAST(LAST_ANALYZEDASTIMESTAMP) DBMS_STATS.GET_PREFS('PUBLISH',OWNER,TABLE_NAME) \n -------- ------------------------------ ------------------------------------------------ \n 42 12-SEP-18 10.34.01.000000000 AM FALSE \n \n<\/code><\/pre>\nI’ve finished my test with pending statistics:<\/p>\n
\n\n10:34:05 SQL> alter session set optimizer_use_pending_statistics=false;\nSession altered.\n\n<\/code><\/pre>\nNote that if you have Real Application Testing, you can use SQL Performance Analyzer to test the pending statistics on a whole SQL Tuning Set representing the critical queries of your application. Of course, the more you test there, the better it is.<\/p>\n
Delete Pending Statistics<\/h3>\n
Now let’s say that my test shows that the new statistics are not good, I can simply delete the pending statistics:<\/p>\n
\n\n10:34:06 SQL> exec dbms_stats.delete_pending_stats('DEMO','DEMO');\nPL\/SQL procedure successfully completed.\n\n<\/code><\/pre>\nThen all queries are still using the previous statistics:<\/p>\n
\n\n10:34:07 SQL> show parameter pending\nNAME TYPE VALUE\n-------------------------------- ------- -----\noptimizer_use_pending_statistics boolean FALSE\n\n10:34:07 SQL> select \/*+ gather_plan_statistics *\/ count(*) from DEMO;\n\n COUNT(*) \n -------- \n 42 \n\n10:34:08 SQL> select * from table(dbms_xplan.display_cursor(format=>'basic +rows +rowstats last'));\n\nPLAN_TABLE_OUTPUT \n----------------- \nEXPLAINED SQL STATEMENT: \n------------------------ \nselect \/*+ gather_plan_statistics *\/ count(*) from DEMO \n \nPlan hash value: 2180342005 \n \n-------------------------------------------------------------- \n| Id | Operation | Name | Starts | E-Rows | A-Rows | \n-------------------------------------------------------------- \n| 0 | SELECT STATEMENT | | 1 | | 1 | \n| 1 | SORT AGGREGATE | | 1 | 1 | 1 | \n| 2 | TABLE ACCESS FULL| DEMO | 1 | 1 | 42 | \n-------------------------------------------------------------- \n\n<\/code><\/pre>\nAccept Pending Statistics<\/h3>\n
Now I’ll show the second case where my tests show that the new statistics gathering is ok. I gather statistics again:<\/p>\n
\n\n10:34:09 SQL> exec dbms_stats.gather_table_stats('DEMO','DEMO');\nPL\/SQL procedure successfully completed.\n\n10:34:09 SQL> alter session set optimizer_use_pending_statistics=true;\nSession altered.\n\n10:34:11 SQL> select \/*+ gather_plan_statistics *\/ count(*) from DEMO;\n\n COUNT(*) \n -------- \n 42 \n\n\n10:34:12 SQL> select * from table(dbms_xplan.display_cursor(format=>'basic +rows +rowstats last'));\n\nPLAN_TABLE_OUTPUT \n----------------- \nEXPLAINED SQL STATEMENT: \n------------------------ \nselect \/*+ gather_plan_statistics *\/ count(*) from DEMO \n \nPlan hash value: 2180342005 \n \n-------------------------------------------------------------- \n| Id | Operation | Name | Starts | E-Rows | A-Rows | \n-------------------------------------------------------------- \n| 0 | SELECT STATEMENT | | 1 | | 1 | \n| 1 | SORT AGGREGATE | | 1 | 1 | 1 | \n| 2 | TABLE ACCESS FULL| DEMO | 1 | 42 | 42 | \n-------------------------------------------------------------- \n \n10:34:12 SQL> alter session set optimizer_use_pending_statistics=false;\nSession altered.\n\n<\/code><\/pre>\nWhen I’m ok with the new statistics I can publish them so that other sessions can see them. As doing this in production is probably a fix for a critical problem, I want the effects to take immediately, invalidating all cursors:<\/p>\n
\n\n10:34:13 SQL> exec dbms_stats.publish_pending_stats('DEMO','DEMO',no_invalidate=>false);\nPL\/SQL procedure successfully completed.\n\n<\/code><\/pre>\nThe default NO_INVALIDATE value is probably to avoid in those cases because you want to see the side effects, if any, as soon as possible. Not within a random window of 5 hours later where you have left the office. I set back the table preference to PUBLISH=TRUE and check that the new statistics are visible in DBA_TAB_STATISTICS (and no more in DBA_TAB_PENDING_STATS):<\/p>\n
\n\n10:34:14 SQL> exec dbms_stats.set_table_prefs('DEMO','DEMO','publish','true');\nPL\/SQL procedure successfully completed.\n\n10:34:15 SQL> select num_rows,cast(last_analyzed as timestamp),dbms_stats.get_prefs('PUBLISH',owner,table_name) from dba_tab_statistics where owner='DEMO' and table_name in ('DEMO');\n\n NUM_ROWS CAST(LAST_ANALYZEDASTIMESTAMP) DBMS_STATS.GET_PREFS('PUBLISH',OWNER,TABLE_NAME) \n -------- ------------------------------ ------------------------------------------------ \n 42 12-SEP-18 10.34.09.000000000 AM TRUE \n\n\n10:34:15 SQL> c\/dba_tab_statistics\/dba_tab_pending_stats\n 1* select num_rows,cast(last_analyzed as timestamp),dbms_stats.get_prefs('PUBLISH',owner,table_name) from dba_tab_pending_stats where owner='DEMO' and table_name in ('DEMO');\n10:34:15 SQL> \/\n\nno rows selected\n\n<\/code><\/pre>\nReport Differences<\/h3>\n
Then what if a citical regression is observed later? I still have the possibility to revert to the old statistics. First I can check in detail what has changed:<\/p>\n
\n\n10:34:16 SQL> select report from table(dbms_stats.diff_table_stats_in_history('DEMO','DEMO',sysdate-1,sysdate,0));\n\nREPORT\n------\n\n###############################################################################\n\nSTATISTICS DIFFERENCE REPORT FOR:\n.................................\n\nTABLE : DEMO\nOWNER : DEMO\nSOURCE A : Statistics as of 11-SEP-18 10.34.16.000000 AM EUROPE\/ZURICH\nSOURCE B : Statistics as of 12-SEP-18 10.34.16.000000 AM EUROPE\/ZURICH\nPCTTHRESHOLD : 0\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\n\nTABLE \/ (SUB)PARTITION STATISTICS DIFFERENCE:\n.............................................\n\nOBJECTNAME TYP SRC ROWS BLOCKS ROWLEN SAMPSIZE\n...............................................................................\n\nDEMO T A 1 4 3 1\n B 42 8 3 42\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\nCOLUMN STATISTICS DIFFERENCE:\n.............................\n\nCOLUMN_NAME SRC NDV DENSITY HIST NULLS LEN MIN MAX SAMPSIZ\n...............................................................................\n\nN A 1 1 NO 0 3 C102 C102 1\n B 41 .024390243 NO 0 3 C102 C12A 42\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\n\n\nNO DIFFERENCE IN INDEX \/ (SUB)PARTITION STATISTICS\n###############################################################################\n\n<\/code><\/pre>\nRestore Previous Statistics<\/h3>\n
If nothing is obvious and the regression is more critical than the original problem, I still have the possibility to revert back to the old statistics:<\/p>\n
\n\n10:34:17 SQL> exec dbms_stats.restore_table_stats('DEMO','DEMO',sysdate-1,no_invalidate=>false);\nPL\/SQL procedure successfully completed.\n\n<\/code><\/pre>\nAgain, invalidating all cursors immediately is probably required as I solve a critical problem here. Immediately, the same query uses the old statistics:<\/p>\n
\n\n10:34:17 SQL> select \/*+ gather_plan_statistics *\/ count(*) from DEMO;\n\n COUNT(*) \n -------- \n 42 \n\n\n10:34:17 SQL> select * from table(dbms_xplan.display_cursor(format=>'basic +rows +rowstats last'));\n\nPLAN_TABLE_OUTPUT \n----------------- \nEXPLAINED SQL STATEMENT: \n------------------------ \nselect \/*+ gather_plan_statistics *\/ count(*) from DEMO \n \nPlan hash value: 2180342005 \n \n-------------------------------------------------------------- \n| Id | Operation | Name | Starts | E-Rows | A-Rows | \n-------------------------------------------------------------- \n| 0 | SELECT STATEMENT | | 1 | | 1 | \n| 1 | SORT AGGREGATE | | 1 | 1 | 1 | \n| 2 | TABLE ACCESS FULL| DEMO | 1 | 1 | 42 |\n-------------------------------------------------------------- \n\n<\/code><\/pre>\nIf I want to see what happened recently on this table, I can query the history of operations (you can replace my ugly regexp_replace with XQuery):<\/p>\n
\n\n10:34:18 SQL> select end_time,end_time-start_time,operation,target,regexp_replace(regexp_replace(notes,'\" val=\"','=>'),'(||)',' '),status from DBA_OPTSTAT_OPERATIONS where regexp_like(target,'\"?'||'DEMO'||'\"?.\"?'||'DEMO'||'\"?') order by end_time desc fetch first 10 rows only;\n\nEND_TIME END_TIME-START_TIME OPERATION TARGET REGEXP_REPLACE(REGEXP_REPLACE(NOTES,'\"VAL=\"','=>'),'(||)','') STATUS \n-------- ------------------- --------- ------ ---------------------------------------------------------------------------------------------- ------ \n12-SEP-18 10.34.17.718800000 AM +02:00 +00 00:00:00.017215 restore_table_stats \"DEMO\".\"DEMO\" as_of_timestamp=>09-11-2018 10:34:17 force=>FALSE no_invalidate=>FALSE ownname=>DEMO restore_cluster_index=>FALSE tabname=>DEMO COMPLETED \n12-SEP-18 10.34.13.262234000 AM +02:00 +00 00:00:00.010021 restore_table_stats \"DEMO\".\"DEMO\" as_of_timestamp=>11-30-3000 01:00:00 force=>FALSE no_invalidate=>FALSE ownname=>DEMO restore_cluster_index=>FALSE tabname=>DEMO COMPLETED \n12-SEP-18 10.34.09.974873000 AM +02:00 +00 00:00:00.032513 gather_table_stats \"DEMO\".\"DEMO\" block_sample=>FALSE cascade=>NULL concurrent=>FALSE degree=>NULL estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE force=>FALSE granularity=>AUTO method_opt=>FOR ALL COLUMNS SIZE AUTO no_invalidate=>NULL ownname=>DEMO partname=> reporting_mode=>FALSE statid=> statown=> stattab=> stattype=>DATA tabname=>DEMO COMPLETED \n12-SEP-18 10.34.01.194735000 AM +02:00 +00 00:00:00.052087 gather_table_stats \"DEMO\".\"DEMO\" block_sample=>FALSE cascade=>NULL concurrent=>FALSE degree=>NULL estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE force=>FALSE granularity=>AUTO method_opt=>FOR ALL COLUMNS SIZE AUTO no_invalidate=>NULL ownname=>DEMO partname=> reporting_mode=>FALSE statid=> statown=> stattab=> stattype=>DATA tabname=>DEMO COMPLETED \n\n<\/code><\/pre>\nWe can see here that the publishing of pending stats was actually a restore of stats as of Nov 30th of Year 3000. This is probably because the pending status is hardcoded as a date in the future. Does that mean that all pending stats will become autonomously published at that time? I don’t think we have to worry about Y3K bugs for the moment…<\/p>\n
Here is the full receipe I’ve given to an application owner who needs to gather statistics on his tables on a highly critical database. Then he has all the info to limit the risks. My recommendation is to prepare this fallback scenario before doing any change, and test it as I did, on a test environment, in order to be ready to react on any unexpected side effect. Be careful, the pending statsitics do not work correctly with system statistics and can have very nasty side effects (Bug 21326597<\/a>), but restoring from history is possible.<\/p>\n","protected":false},"excerpt":{"rendered":"By Franck Pachot . This was initially posted to CERN Database blog on Wednesday, 12 September 2018 where it seems to be lost. Here is a copy thanks to web.archive.org Demo table I create a table for the demo. The CTAS gathers statistics (12c online statistics gathering) with one row and then I insert more […]<\/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":[59],"tags":[2216,96,255],"type_dbi":[],"class_list":["post-15433","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-cern","tag-oracle","tag-statistics"],"acf":[],"yoast_head":"\n
Optimizer Statistics Gathering - pending and history - dbi Blog<\/title>\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\/optimizer-statistics-gathering-pending-and-history\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Optimizer Statistics Gathering - pending and history\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . This was initially posted to CERN Database blog on Wednesday, 12 September 2018 where it seems to be lost. Here is a copy thanks to web.archive.org Demo table I create a table for the demo. The CTAS gathers statistics (12c online statistics gathering) with one row and then I insert more […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/optimizer-statistics-gathering-pending-and-history\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-12-29T14:01:44+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=\"9 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\/optimizer-statistics-gathering-pending-and-history\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/optimizer-statistics-gathering-pending-and-history\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Optimizer Statistics Gathering – pending and history\",\"datePublished\":\"2020-12-29T14:01:44+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/optimizer-statistics-gathering-pending-and-history\/\"},\"wordCount\":664,\"commentCount\":0,\"keywords\":[\"CERN\",\"Oracle\",\"Statistics\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/optimizer-statistics-gathering-pending-and-history\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/optimizer-statistics-gathering-pending-and-history\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/optimizer-statistics-gathering-pending-and-history\/\",\"name\":\"Optimizer Statistics Gathering - pending and history - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2020-12-29T14:01:44+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/optimizer-statistics-gathering-pending-and-history\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/optimizer-statistics-gathering-pending-and-history\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/optimizer-statistics-gathering-pending-and-history\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Optimizer Statistics Gathering – pending and history\"}]},{\"@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":"Optimizer Statistics Gathering - pending and history - dbi Blog","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\/optimizer-statistics-gathering-pending-and-history\/","og_locale":"en_US","og_type":"article","og_title":"Optimizer Statistics Gathering - pending and history","og_description":"By Franck Pachot . This was initially posted to CERN Database blog on Wednesday, 12 September 2018 where it seems to be lost. Here is a copy thanks to web.archive.org Demo table I create a table for the demo. The CTAS gathers statistics (12c online statistics gathering) with one row and then I insert more […]","og_url":"https:\/\/www.dbi-services.com\/blog\/optimizer-statistics-gathering-pending-and-history\/","og_site_name":"dbi Blog","article_published_time":"2020-12-29T14:01:44+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"9 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/optimizer-statistics-gathering-pending-and-history\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/optimizer-statistics-gathering-pending-and-history\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Optimizer Statistics Gathering – pending and history","datePublished":"2020-12-29T14:01:44+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/optimizer-statistics-gathering-pending-and-history\/"},"wordCount":664,"commentCount":0,"keywords":["CERN","Oracle","Statistics"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/optimizer-statistics-gathering-pending-and-history\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/optimizer-statistics-gathering-pending-and-history\/","url":"https:\/\/www.dbi-services.com\/blog\/optimizer-statistics-gathering-pending-and-history\/","name":"Optimizer Statistics Gathering - pending and history - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2020-12-29T14:01:44+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/optimizer-statistics-gathering-pending-and-history\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/optimizer-statistics-gathering-pending-and-history\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/optimizer-statistics-gathering-pending-and-history\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Optimizer Statistics Gathering – pending and history"}]},{"@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\/15433","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=15433"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/15433\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=15433"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=15433"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=15433"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=15433"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}