{"id":147,"date":"2017-08-14T01:56:15","date_gmt":"2017-08-14T01:56:15","guid":{"rendered":"http:\/\/www.mrmarkyoung.com\/oracle\/?p=147"},"modified":"2017-08-14T01:56:15","modified_gmt":"2017-08-14T01:56:15","slug":"missing-optimizer-statistics","status":"publish","type":"post","link":"http:\/\/www.mrmarkyoung.com\/oracle\/2017\/08\/14\/missing-optimizer-statistics\/","title":{"rendered":"Missing Optimizer Statistics"},"content":{"rendered":"\n<pre class=\"lang:default decode:true \" >ELECT 'TABLE' object_type,\r\n       owner,\r\n       table_name object_name,\r\n       last_analyzed,\r\n       stattype_locked,\r\n       stale_stats\r\n  FROM all_tab_statistics\r\n WHERE     (last_analyzed IS NULL OR stale_stats = 'YES')\r\n       AND stattype_locked IS NULL\r\n       AND owner NOT IN ('ANONYMOUS',\r\n                         'CTXSYS',\r\n                         'DBSNMP',\r\n                         'EXFSYS',\r\n                         'LBACSYS',\r\n                         'MDSYS',\r\n                         'MGMT_VIEW',\r\n                         'OLAPSYS',\r\n                         'OWBSYS',\r\n                         'ORDPLUGINS',\r\n                         'ORDSYS',\r\n                         'OUTLN',\r\n                         'SI_INFORMTN_SCHEMA',\r\n                         'SYS',\r\n                         'SYSMAN',\r\n                         'SYSTEM',\r\n                         'TSMSYS',\r\n                         'WK_TEST',\r\n                         'WKSYS',\r\n                         'WKPROXY',\r\n                         'WMSYS',\r\n                         'XDB')\r\n       AND owner NOT LIKE 'FLOW%'\r\nUNION ALL\r\nSELECT 'INDEX' object_type,\r\n       owner,\r\n       index_name object_name,\r\n       last_analyzed,\r\n       stattype_locked,\r\n       stale_stats\r\n  FROM all_ind_statistics\r\n WHERE     (last_analyzed IS NULL OR stale_stats = 'YES')\r\n       AND stattype_locked IS NULL\r\n       AND owner NOT IN ('ANONYMOUS',\r\n                         'CTXSYS',\r\n                         'DBSNMP',\r\n                         'EXFSYS',\r\n                         'LBACSYS',\r\n                         'MDSYS',\r\n                         'MGMT_VIEW',\r\n                         'OLAPSYS',\r\n                         'OWBSYS',\r\n                         'ORDPLUGINS',\r\n                         'ORDSYS',\r\n                         'OUTLN',\r\n                         'SI_INFORMTN_SCHEMA',\r\n                         'SYS',\r\n                         'SYSMAN',\r\n                         'SYSTEM',\r\n                         'TSMSYS',\r\n                         'WK_TEST',\r\n                         'WKSYS',\r\n                         'WKPROXY',\r\n                         'WMSYS',\r\n                         'XDB')\r\n       AND owner NOT LIKE 'FLOW%'\r\nORDER BY object_type DESC, owner, object_name;<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>ELECT &#8216;TABLE&#8217; object_type, owner, table_name object_name, last_analyzed, stattype_locked, stale_stats FROM all_tab_statistics WHERE (last_analyzed IS NULL OR stale_stats = &#8216;YES&#8217;) AND stattype_locked IS NULL AND owner NOT IN (&#8216;ANONYMOUS&#8217;, &#8216;CTXSYS&#8217;, &#8216;DBSNMP&#8217;, &#8216;EXFSYS&#8217;, &#8216;LBACSYS&#8217;, &#8216;MDSYS&#8217;, &#8216;MGMT_VIEW&#8217;, &#8216;OLAPSYS&#8217;, &#8216;OWBSYS&#8217;, &#8216;ORDPLUGINS&#8217;, &#8216;ORDSYS&#8217;, &#8216;OUTLN&#8217;, &#8216;SI_INFORMTN_SCHEMA&#8217;, &#8216;SYS&#8217;, &#8216;SYSMAN&#8217;, &#8216;SYSTEM&#8217;, &#8216;TSMSYS&#8217;, &#8216;WK_TEST&#8217;, &#8216;WKSYS&#8217;, &#8216;WKPROXY&#8217;, &#8216;WMSYS&#8217;, &#8216;XDB&#8217;) AND owner NOT LIKE &#8216;FLOW%&#8217; UNION &#8230; <a title=\"Missing Optimizer Statistics\" class=\"read-more\" href=\"http:\/\/www.mrmarkyoung.com\/oracle\/2017\/08\/14\/missing-optimizer-statistics\/\" aria-label=\"Read more about Missing Optimizer Statistics\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[5],"class_list":["post-147","post","type-post","status-publish","format-standard","hentry","category-oracle-database","tag-statistics"],"_links":{"self":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/147","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/comments?post=147"}],"version-history":[{"count":1,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/147\/revisions"}],"predecessor-version":[{"id":148,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/147\/revisions\/148"}],"wp:attachment":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/media?parent=147"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/categories?post=147"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/tags?post=147"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}