tag:blogger.com,1999:blog-5124641802818980374.post3881475610523798578..comments2015-02-17T20:53:30.305+01:00Comments on Oracle related stuff: Locked table statistics and subsequent create indexRandolfnoreply@blogger.comBlogger5125tag:blogger.com,1999:blog-5124641802818980374.post-9729091182845268172009-06-26T21:24:28.947+02:002009-06-26T21:24:28.947+02:00Martin,
fortunately this is a gross underestimate...Martin,<br /><br />fortunately this is a gross underestimate of the feature.<br /><br />Actually it is supposed to reflect EVERY change to statistics that is done via DBMS_STATS, no matter if performed automatically or manually. Even manual manipulations using SET_*_STATS will be reflected.<br /><br />However, modifying statistics using the (deprecated for ages now ) ANALYZE command doesn&#39;t get picked up.<br /><br />It&#39;s only tracked on table level but applies also to changes to partition level statistics.<br /><br />A simple testcase to demonstrate:<br /><br />drop table test_hist_stats purge;<br /><br />create table test_hist_stats<br />as<br />select<br /> *<br />from<br /> all_objects<br />where<br /> rownum &lt;= 1000;<br /><br />exec dbms_stats.gather_table_stats(null, &#39;test_hist_stats&#39;)<br /><br />-- the baseline<br />select<br /> table_name<br /> , stats_update_time<br />from<br /> user_tab_stats_history<br />where<br /> table_name = &#39;TEST_HIST_STATS&#39;;<br /><br />select<br /> num_rows<br />from<br /> user_tab_statistics<br />where<br /> table_name = &#39;TEST_HIST_STATS&#39;;<br /><br />variable v_date varchar2(30)<br /><br />exec :v_date := to_char(systimestamp, &#39;DD.MM.YYYY HH24:MI:SS.FF&#39;)<br /><br />print v_date<br /><br />exec dbms_lock.sleep(5)<br /><br />exec dbms_stats.set_table_stats(null, &#39;test_hist_stats&#39;, numrows=&gt;500)<br /><br />-- modified manually via set_*_stats<br />select<br /> table_name<br /> , stats_update_time<br />from<br /> user_tab_stats_history<br />where<br /> table_name = &#39;TEST_HIST_STATS&#39;;<br /><br />select<br /> num_rows<br />from<br /> user_tab_statistics<br />where<br /> table_name = &#39;TEST_HIST_STATS&#39;;<br /><br />exec dbms_stats.restore_table_stats(null, &#39;test_hist_stats&#39;, to_timestamp(:v_date, &#39;DD.MM.YYYY HH24:MI:SS.FF&#39;))<br /><br />select<br /> num_rows<br />from<br /> user_tab_statistics<br />where<br /> table_name = &#39;TEST_HIST_STATS&#39;;<br /><br />-- restored to baseline<br />select<br /> table_name<br /> , stats_update_time<br />from<br /> user_tab_stats_history<br />where<br /> table_name = &#39;TEST_HIST_STATS&#39;;<br /><br />delete from<br /> test_hist_stats<br />where<br /> rownum &lt;= 500;<br /><br />commit;<br /><br />analyze table test_hist_stats compute statistics;<br /><br />select<br /> num_rows<br />from<br /> user_tab_statistics<br />where<br /> table_name = &#39;TEST_HIST_STATS&#39;;<br /><br />-- statistics change not reflected in history<br />select<br /> table_name<br /> , stats_update_time<br />from<br /> user_tab_stats_history<br />where<br /> table_name = &#39;TEST_HIST_STATS&#39;;<br /><br />Regards,<br />RandolfRandolfhttp://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-76586029115164681702009-06-26T00:19:32.499+02:002009-06-26T00:19:32.499+02:00Hi Randolf,
Yes, if the stats have been gathered ...Hi Randolf,<br /><br />Yes, if the stats have been gathered by the automated job it very nicely keeps the previous set (for 31 days by default, I think). It is a feature most people are unaware of and can be very useful if a query goes rogue on you over night.<br />Some sites though have replaced the automated job (usually for something put in place under V9), so may not save their old stats by default.Martinhttp://www.blogger.com/profile/08355862675766269563noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-9473111181658562262009-06-25T22:20:43.182+02:002009-06-25T22:20:43.182+02:00Martin,
thanks for your comment and the link.
Fo...Martin,<br /><br />thanks for your comment and the link.<br /><br />Fortunately from 10g on even if you don&#39;t have saved the statistics explicitly via a stats table you can restore the statistics via the DBMS_STATS.RESTORE_*_STATS procedures. You can use e.g. the DBA/ALL/USER_TAB_STATS_HISTORY view to browse the automatically retained history of the table statistics. The retention can be controlled by DBMS_STATS.ALTER_STATS_HISTORY_RETENTION.<br /><br />See <a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#i41814" rel="nofollow">the documentation</a> for more information.<br /><br />Note that from 11g on the default staleness of 10% can be configured using the DBMS_STATS.SET_GLOBAL/DATABASE/SCHEMA/TABLE_PREFS with the STALE_PERCENT option.<br /><br />Regards,<br />RandolfRandolfhttp://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-30456378971885415202009-06-25T18:20:36.241+02:002009-06-25T18:20:36.241+02:00If you decided to unlock the table stats during th...If you decided to unlock the table stats during the index create and are concerned that the table stats may get changed (either because it takes so long to create the index or you might get distracted and forget to re-lock the table stats) you can save the table stats out to a stats table first.<br /><br />Also, you can check if the automated stats job is likely to regenerate stats on the table by looking at sys.dba_tab_modifications. If the number of changes (total of inserts, updates and deletes) is more than 10% the number of rows in the table, the automated stats package will re-analyze that table on the next run.<br /><br />On 10.2 (and 11 I think) you will have to use dbms_stats.flush_database_monitoring_info to push any changes that day into the sys.dba_tab_modifications table.<br /><br />If you want to know more about dba_tab_modifications I blogged about it a few weeks ago:-<br />http://mwidlake.wordpress.com/2009/05/26/counting-the-cost-4-the-speedy-way/Martinhttp://www.blogger.com/profile/08355862675766269563noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-77951017828982364412009-06-23T00:31:30.683+02:002009-06-23T00:31:30.683+02:00Thank you Randolf, this was very useful informatio...Thank you Randolf, this was very useful information, as I just recently made use of locked stats in an ongoing project. This might just save my day in the near future. ;-)<br />Regards,<br />UweUwe Küchlerhttp://www.blogger.com/profile/08199596117280621443noreply@blogger.com