{"id":1027,"date":"2016-09-18T12:31:09","date_gmt":"2016-09-18T12:31:09","guid":{"rendered":"http:\/\/nenadnoveljic.com\/blog\/?p=1027"},"modified":"2016-09-18T12:31:09","modified_gmt":"2016-09-18T12:31:09","slug":"gathering-statistics-gather-auto","status":"publish","type":"post","link":"https:\/\/nenadnoveljic.com\/blog\/gathering-statistics-gather-auto\/","title":{"rendered":"Gathering Statistics with Option &#8216;gather auto&#8217;"},"content":{"rendered":"<h1>Introduction<\/h1>\n<p><em>dbms_stats.gather_schema_stats<\/em> is equiped with the option <em>gather auto<\/em>. The <a href=\"https:\/\/docs.oracle.com\/database\/121\/ARPLS\/d_stats.htm#ARPLS68577\" target=\"_blank\">Oracle documentation<\/a> says that &#8220;Oracle implicitly determines which objects need new statistics&#8221; without exactly exposing the criteria for selecting objects. However, <em>gather auto<\/em> seems to be the concatenation of <em>gather stale<\/em> and <em>gather empty<\/em>, which can be verified by running the test case below.<\/p>\n<p>In 12c Oracle introduced the <em>options<\/em> parameter for <em>gather_table_stats<\/em> as well. Unfortunately, it turned out that this parameter doesn&#8217;t behave in a consistent manner.<\/p>\n<h1>Schema Statistics<\/h1>\n<p>First, I&#8217;ll create a schema and a test table:<\/p>\n<pre><code>create user u identified by Temp_1234 ;\r\ngrant dba to u ;\r\nconnect u\/Temp_1234\r\n\r\ncreate table t (a number) ;\r\n\r\nalter session set nls_date_format='dd.mm.yy hh24:mi:ss' ;\r\n\r\nset NULL NULL\r\ncolumn stale_stats format a4\r\n<\/code><\/pre>\n<p>As expected, there is no statistics for the new table <em>t<\/em>:<\/p>\n<pre><code>select stale_stats,last_analyzed \r\n  from user_tab_statistics \r\n  where table_name='T' \r\n;\r\n\r\nSTAL LAST_ANALYZED\r\n---- -----------------\r\n<span style=\"color: #ff0000;\">NULL<\/span> NULL\r\n<\/code><\/pre>\n<p><em>gather_schema_stats<\/em> with options <em>gather auto<\/em> will gather the empty statistics &#8211; no surprise there:<code> <\/code><\/p>\n<pre><code>BEGIN\r\n  SYS.DBMS_STATS.GATHER_SCHEMA_STATS (\r\n     OwnName           =&gt; 'U'\r\n    ,Options           =&gt; 'GATHER auto'\r\n);\r\nEND;\r\n\/\r\n\r\nselect stale_stats,last_analyzed \r\n  from user_tab_statistics \r\n  where table_name='T' \r\n;\r\n\r\nSTAL LAST_ANALYZED\r\n---- -----------------\r\n<span style=\"color: #ff0000;\">NO<\/span>   14.09.16 20:22:31\r\n<\/code><\/pre>\n<p>Inserting a row will render statistics stale:<\/p>\n<pre><code>insert into t values (1) ;\r\ncommit ;\r\n\r\nexec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO ;\r\n\r\nselect stale_stats,last_analyzed \r\n  from user_tab_statistics \r\n  where table_name='T' ;\r\n\r\nSTAL LAST_ANALYZED\r\n---- -----------------\r\n<span style=\"color: #ff0000;\">YES<\/span>  14.09.16 20:22:31<\/code><\/pre>\n<p>Finally, <em>gather auto<\/em> in <em>gather_schema_stats<\/em> will also\u00a0trigger\u00a0the statistics are stale:<\/p>\n<pre><code>exec DBMS_LOCK.SLEEP(1);\r\n\r\nBEGIN\r\n  SYS.DBMS_STATS.GATHER_SCHEMA_STATS (\r\n     OwnName           =&gt; 'U'\r\n    ,Options           =&gt; 'GATHER auto'\r\n);\r\nEND;\r\n\/\r\n\r\nselect stale_stats,last_analyzed \r\n  from user_tab_statistics \r\n  where table_name='T' ;\r\n\r\nSTAL LAST_ANALYZED\r\n---- -----------------\r\n<span style=\"color: #ff0000;\">NO<\/span>   14.09.16 <span style=\"color: #ff0000;\">20:23:51\r\n<\/span><\/code><\/pre>\n<p>In conclusion, <em>gather_schema_stats<\/em> gathers statistics for all the objects with stale and empty statistics in case <em>gather auto<\/em> is specified.<\/p>\n<h2>Table Statistics<\/h2>\n<p>In Oracle 12c the option <em>gather auto<\/em> was introduced for <a href=\"https:\/\/docs.oracle.com\/database\/121\/ARPLS\/d_stats.htm#ARPLS68582\" target=\"_blank\">gather_table_stats<\/a> as well. However, the table statistics will be gathered even though the statistics are neither stale nor empty.<\/p>\n<pre><code>exec DBMS_LOCK.SLEEP(1);\r\n\r\nbegin\r\n  SYS.DBMS_STATS.GATHER_<span style=\"color: #ff0000;\">TABLE<\/span>_STATS (\r\n     OwnName            =&gt; 'U'\r\n    ,TabName            =&gt; 'T'\r\n    ,Options            =&gt; 'GATHER auto'\r\n);\r\nend;\r\n\/\r\n\r\nselect stale_stats,last_analyzed \r\n  from user_tab_statistics \r\n  where table_name='T' ;\r\n\r\nSTAL LAST_ANALYZED\r\n---- -----------------\r\nNO   14.09.16 <span style=\"color: #ff0000;\">20:25:38\r\n\r\n<\/span><\/code><\/pre>\n<h1>Summary<\/h1>\n<p>Gathering schema statistics with <em>gather auto<\/em> is a convenient way to selectively gather statistics for the specified schema. In particular, the statistics will be gathered for the objects with stale and empty statistics. In 12c the parameter options was introduced for <em>gather_table_stats<\/em> as well. However, this parameter doesn&#8217;t seem to have any impact there. As a workaround, the column <em>user_tab_statistics.stale_stats<\/em> has to be queried for making the decision whether to gather table statistics.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In 12c the parameter options was introduced in gather_table_stats. Unfortunately, it doesn&#8217;t work as expected. <a href=\"https:\/\/nenadnoveljic.com\/blog\/gathering-statistics-gather-auto\/\" class=\"more-link\">Continue Reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[8,5,18],"tags":[],"class_list":["post-1027","post","type-post","status-publish","format-standard","hentry","category-12c","category-oracle","category-statistics"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Gathering Statistics with Option &#039;gather auto&#039; - All-round Database Topics<\/title>\n<meta name=\"description\" content=\"In 12c the parameter options (gather auto) was introduced in gather_table_stats. Unfortunately, it doesn&#039;t work as expected.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/nenadnoveljic.com\/blog\/gathering-statistics-gather-auto\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Gathering Statistics with Option &#039;gather auto&#039; - All-round Database Topics\" \/>\n<meta property=\"og:description\" content=\"In 12c the parameter options (gather auto) was introduced in gather_table_stats. Unfortunately, it doesn&#039;t work as expected.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/nenadnoveljic.com\/blog\/gathering-statistics-gather-auto\/\" \/>\n<meta property=\"og:site_name\" content=\"All-round Database Topics\" \/>\n<meta property=\"article:published_time\" content=\"2016-09-18T12:31:09+00:00\" \/>\n<meta name=\"author\" content=\"Nenad Noveljic\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@NenadNoveljic\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nenad Noveljic\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/gathering-statistics-gather-auto\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/gathering-statistics-gather-auto\\\/\"},\"author\":{\"name\":\"Nenad Noveljic\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"headline\":\"Gathering Statistics with Option &#8216;gather auto&#8217;\",\"datePublished\":\"2016-09-18T12:31:09+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/gathering-statistics-gather-auto\\\/\"},\"wordCount\":287,\"commentCount\":2,\"articleSection\":[\"12c\",\"Oracle\",\"Statistics\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/gathering-statistics-gather-auto\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/gathering-statistics-gather-auto\\\/\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/gathering-statistics-gather-auto\\\/\",\"name\":\"Gathering Statistics with Option 'gather auto' - All-round Database Topics\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\"},\"datePublished\":\"2016-09-18T12:31:09+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"description\":\"In 12c the parameter options (gather auto) was introduced in gather_table_stats. Unfortunately, it doesn't work as expected.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/gathering-statistics-gather-auto\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/gathering-statistics-gather-auto\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/gathering-statistics-gather-auto\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Gathering Statistics with Option &#8216;gather auto&#8217;\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\",\"name\":\"All-round Database Topics\",\"description\":\"Nenad Noveljic\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\",\"name\":\"Nenad Noveljic\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"caption\":\"Nenad Noveljic\"},\"sameAs\":[\"nenad-noveljic-9b746a6\",\"https:\\\/\\\/x.com\\\/NenadNoveljic\"],\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/author\\\/nenad\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Gathering Statistics with Option 'gather auto' - All-round Database Topics","description":"In 12c the parameter options (gather auto) was introduced in gather_table_stats. Unfortunately, it doesn't work as expected.","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:\/\/nenadnoveljic.com\/blog\/gathering-statistics-gather-auto\/","og_locale":"en_US","og_type":"article","og_title":"Gathering Statistics with Option 'gather auto' - All-round Database Topics","og_description":"In 12c the parameter options (gather auto) was introduced in gather_table_stats. Unfortunately, it doesn't work as expected.","og_url":"https:\/\/nenadnoveljic.com\/blog\/gathering-statistics-gather-auto\/","og_site_name":"All-round Database Topics","article_published_time":"2016-09-18T12:31:09+00:00","author":"Nenad Noveljic","twitter_card":"summary_large_image","twitter_creator":"@NenadNoveljic","twitter_misc":{"Written by":"Nenad Noveljic","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/nenadnoveljic.com\/blog\/gathering-statistics-gather-auto\/#article","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/gathering-statistics-gather-auto\/"},"author":{"name":"Nenad Noveljic","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"headline":"Gathering Statistics with Option &#8216;gather auto&#8217;","datePublished":"2016-09-18T12:31:09+00:00","mainEntityOfPage":{"@id":"https:\/\/nenadnoveljic.com\/blog\/gathering-statistics-gather-auto\/"},"wordCount":287,"commentCount":2,"articleSection":["12c","Oracle","Statistics"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/nenadnoveljic.com\/blog\/gathering-statistics-gather-auto\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/nenadnoveljic.com\/blog\/gathering-statistics-gather-auto\/","url":"https:\/\/nenadnoveljic.com\/blog\/gathering-statistics-gather-auto\/","name":"Gathering Statistics with Option 'gather auto' - All-round Database Topics","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#website"},"datePublished":"2016-09-18T12:31:09+00:00","author":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"description":"In 12c the parameter options (gather auto) was introduced in gather_table_stats. Unfortunately, it doesn't work as expected.","breadcrumb":{"@id":"https:\/\/nenadnoveljic.com\/blog\/gathering-statistics-gather-auto\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/nenadnoveljic.com\/blog\/gathering-statistics-gather-auto\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/nenadnoveljic.com\/blog\/gathering-statistics-gather-auto\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/nenadnoveljic.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Gathering Statistics with Option &#8216;gather auto&#8217;"}]},{"@type":"WebSite","@id":"https:\/\/nenadnoveljic.com\/blog\/#website","url":"https:\/\/nenadnoveljic.com\/blog\/","name":"All-round Database Topics","description":"Nenad Noveljic","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/nenadnoveljic.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa","name":"Nenad Noveljic","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","caption":"Nenad Noveljic"},"sameAs":["nenad-noveljic-9b746a6","https:\/\/x.com\/NenadNoveljic"],"url":"https:\/\/nenadnoveljic.com\/blog\/author\/nenad\/"}]}},"_links":{"self":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/1027","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/comments?post=1027"}],"version-history":[{"count":1,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/1027\/revisions"}],"predecessor-version":[{"id":1049,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/1027\/revisions\/1049"}],"wp:attachment":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/media?parent=1027"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/categories?post=1027"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/tags?post=1027"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}