{"id":1000,"date":"2006-06-18T12:00:00","date_gmt":"2006-06-18T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1000"},"modified":"2006-06-18T12:00:00","modified_gmt":"2006-06-18T12:00:00","slug":"saving-optimiser-stats-10g","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2006\/06\/18\/saving-optimiser-stats-10g\/","title":{"rendered":"Saving Optimiser Stats – 10g"},"content":{"rendered":"

In my previous blog I showed how you can save your current optimiser stats into a seperate table whenever you refresh them, so that they can be restored should the new statistics lead to poor SQL execution plans. Oracle have added an improved version of this facility to 10g (which I take as a sign that this is a significant issue for their customers). There’s more information in the documentation, here<\/a> for example, but here’s how it looks in action.<\/p>\n

SQL> select table_name, num_rows from user_tables  2  \/
<\/p>TABLE_NAME                       NUM_ROWS------------------------------ ----------TEST_TAB1                        65453000TEST_TAB2                        65490100DUDE                              1599372TEMP                              1000000
<\/p>SQL> select count(*) from temp;  
<\/p>COUNT(*)----------   1000000
<\/p>SQL> select * from user_tab_stats_history;
<\/p>no rows selected<\/pre>\n
The user_tab_stats_history view shows the different versions of table statistics that have been saved. In this case, there aren’t any yet. However, the current statistics will be saved when I regenerate the schema statistics next. (Note that there is no need for me to specify a stats table in the call to gather_schema_stats and that this call should all be one one line.)<\/p>\n
SQL> exec dbms_stats.gather_schema_stats (                ownname=>'TESTUSER',                estimate_percent=>10)
<\/p>PL\/SQL procedure successfully completed.
<\/p>SQL> select table_name, stats_update_time      from user_tab_stats_history;
<\/p>TABLE_NAME                     ------------------------------ STATS_UPDATE_TIME------------------------------------------------------TEMP18-JUN-06 04.49.10.036992 PM +01:00TEST_TAB118-JUN-06 05.05.55.447281 PM +01:00TEST_TAB218-JUN-06 05.20.04.831991 PM +01:00DUDE18-JUN-06 04.49.06.665160 PM +01:00<\/pre>\n
Now I’ll truncate a couple of tables and regenerate the schema statistics.<\/p>\n
SQL> truncate table test_tab1;
<\/p>Table truncated.
<\/p>SQL> truncate table test_tab2;
<\/p>Table truncated.
<\/p>SQL> exec dbms_stats.gather_schema_stats(\townname=>'TESTUSER',\testimate_percent=>10)
<\/p>PL\/SQL procedure successfully completed.
<\/p>SQL> select table_name, stats_update_time from user_tab_stats_history;
<\/p>TABLE_NAME                    ------------------------------STATS_UPDATE_TIME---------------------------------------------------------------------------TEMP18-JUN-06 04.49.10.036992 PM +01:00TEST_TAB118-JUN-06 05.05.55.447281 PM +01:00TEST_TAB218-JUN-06 05.20.04.831991 PM +01:00DUDE18-JUN-06 05.22.41.565053 PM +01:00TEMP18-JUN-06 05.22.49.773888 PM +01:00TEST_TAB118-JUN-06 05.22.49.798122 PM +01:00TEST_TAB218-JUN-06 05.22.49.823022 PM +01:00DUDE18-JUN-06 04.49.06.665160 PM +01:00
<\/p>8 rows selected.<\/pre>\n<\/p>\n
There are two saved versions now and the current statistics show zero rows in test_tab1 and test_tab2.<\/p>\n<\/p>\n<\/p>\n
SQL> select table_name, num_rows from user_tables;
<\/p>TABLE_NAME                       NUM_ROWS------------------------------ ----------DUDE                              1598840TEMP                              1004070TEST_TAB2                               0TEST_TAB1                               0<\/pre>\n
If I want to go back to the previous version of the statistics, I can use the restore_schema_stats procedure (the documentation describes various other restore procedures). Here, I’ll just say ‘restore the statistics as they looked yesterday’.<\/p>\n<\/p>\n
SQL> exec dbms_stats.restore_schema_stats(          ownname=>'TESTUSER',           as_of_timestamp=>sysdate-1);
<\/p>PL\/SQL procedure successfully completed.
<\/p>SQL> select table_name, num_rows from user_tables;
<\/p>TABLE_NAME                       NUM_ROWS------------------------------ ----------DUDE                              1599372TEMP                              1000000TEST_TAB2                        65490100TEST_TAB1                        65453000<\/pre>\n
The statistics suggest that there are 65 million rows in test_tab1 and test_tab2 (of course, the tables are actually empty – but Oracle is just following my instructions). To keep everything neat and tidy, there are new entries in the history table to reflect the fact that I just changed the statistics again.<\/p>\n
SQL> select table_name, stats_update_time from user_tab_stats_history;
<\/p>TABLE_NAME ------------------------------ STATS_UPDATE_TIME--------------------------------------------------------------TEMP18-JUN-06 04.49.10.036992 PM +01:00TEST_TAB118-JUN-06 05.05.55.447281 PM +01:00TEST_TAB218-JUN-06 05.20.04.831991 PM +01:00DUDE18-JUN-06 05.22.41.565053 PM +01:00TEMP18-JUN-06 05.22.49.773888 PM +01:00TEST_TAB118-JUN-06 05.22.49.798122 PM +01:00TEST_TAB218-JUN-06 05.22.49.823022 PM +01:00DUDE18-JUN-06 05.49.06.966851 PM +01:00TEMP18-JUN-06 05.49.07.230811 PM +01:00TEST_TAB118-JUN-06 05.49.07.272036 PM +01:00TEST_TAB218-JUN-06 05.49.07.290720 PM +01:00DUDE18-JUN-06 04.49.06.665160 PM +01:00
<\/p>12 rows selected.<\/pre><\/p>\n","protected":false},"excerpt":{"rendered":"
In my previous blog I showed how you can save your current optimiser stats into a seperate table whenever you refresh them, so that they can be restored should the new statistics lead to poor SQL execution plans. Oracle have added an improved version of this facility to 10g (which I take as a sign… Continue reading Saving Optimiser Stats – 10g<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1000","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1019,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/14\/alter-table-move-and-table-stats-part-ii\/","url_meta":{"origin":1000,"position":0},"title":"alter table … move and table stats (part II)","date":"July 14, 2006","format":false,"excerpt":"Following up on another comment from Howard, I took the initrans change off the alter table ... move so that it was the most basic variation and then ran it on 8i, 9i and 10g. I've trimmed lots of the output this time, but I have the log files if\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":999,"url":"http:\/\/orcldoug.com\/blog\/2012\/06\/18\/saving-optimiser-stats-9i\/","url_meta":{"origin":1000,"position":1},"title":"Saving Optimiser Stats – 9i","date":"June 18, 2012","format":false,"excerpt":"In a recent blog I described how a mis-timed optimiser statistics collection job led to a bad execution plan for one of the SQL statements in a regular batch jobIt's no coincidence that we were already in the process of implementing a change to our stats collection period to retain\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1596,"url":"http:\/\/orcldoug.com\/blog\/2010\/04\/22\/statistics-on-partitioned-tables-part-6a-copy_table_stats-intro\/","url_meta":{"origin":1000,"position":2},"title":"Statistics on Partitioned Tables – Part 6a – COPY_TABLE_STATS – Intro","date":"April 22, 2010","format":false,"excerpt":"[Phew. At last. The first draft of this was dated more than two weeks ago .... One of the problems with blogging about copying stats was the balance between explaining it and pointing out some of the problems I've encountered. So I've broken up this post, with a little explanation\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1017,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/13\/alter-table-move-and-table-stats\/","url_meta":{"origin":1000,"position":3},"title":"alter table … move and table stats","date":"July 13, 2006","format":false,"excerpt":"Howard Rogers left a comment on my last blog, showing an example of using alter table ... move on a 10gR2 database on Linux. In his example, unlike mine, the table rebuild did not nullify the table's statistics. I admit I was surprised myself when I ran my example yesterday\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1565,"url":"http:\/\/orcldoug.com\/blog\/2010\/02\/23\/statistics-on-partitioned-tables-part-3\/","url_meta":{"origin":1000,"position":4},"title":"Statistics on Partitioned Tables – Part 3","date":"February 23, 2010","format":false,"excerpt":"As soon as I'd committed my last post, I knew it wasn't what I'd hoped for and said as much to a couple of people before they'd read it. I knew it would probably just add to any confusion people already had about this subject (something I'm particularly keen to\u2026","rel":"","context":"With 11 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1562,"url":"http:\/\/orcldoug.com\/blog\/2010\/02\/17\/statistics-on-partitioned-tables-part-1\/","url_meta":{"origin":1000,"position":5},"title":"Statistics on Partitioned Tables – Part 1","date":"February 17, 2010","format":false,"excerpt":"If you've ever worked on large databases that use partitioned and subpartitioned tables, you'll be aware that there are significant challenges in maintaining up-to-date\/appropriate statistics. We've encountered a few problems at work recently and I decided it would be an idea to put together a series of posts covering the\u2026","rel":"","context":"With 11 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1000","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=1000"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1000\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1000"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1000"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1000"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}