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

In a recent blog<\/a> 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 job<\/p>\n

It’s no coincidence that we were already in the process of implementing a change to our stats collection period to retain a short history of stats.<\/p>\n

The problem with collecting stats regularly is that the execution plans for SQL statements will probably change at some point. Well, it’s not really a problem, is it? That’s the whole point of collecting statistics – so that the optimiser can choose an execution plan that makes more sense for your changing data volumes and values. However, what happens when the new execution plan isn’t as ‘good’ as the old one? What happens when (as happened in our case), there was something wrong with the statistics? Well, you’ll be in the situation where, not only is a SQL statement running more slowly than expected, but you have no idea why! The solution is to be able to see the ‘good’ plan before the change and compare it to the ‘bad’ one. Which is pretty difficult unless you save execution plans across the entire system for later review. However, if we had the previous set of statistics, we could simply reinstate them or restore them into another environment so that we can investigate the plan changes.<\/p>\n

Saving optimiser statistics seems like a good idea. That’s probably why 10g can do so automatically<\/a>.<\/p>\n

The particular approach we’re taking at work is to incorporate a simple bit of code into our stats collection procedure that saves the existing stats first, for a system-specific period of time. For the OLTP systems, that might only be seven days. For a marketing system where some jobs are only run once a month, we might want to keep 5 weeks of statistics.<\/p>\n

However, Jonathan Lewis left a comment on the original blog pointing out that there’s a simple way of saving one set of statistics if that’s sufficient for your needs. It works in all versions of DBMS_STATS and that’s what I’m going to show you here – in Oracle 9.2.0.4<\/p>\n

First of all the test_schema – note that TEST_TAB doesn’t have any statistics on it at the moment. <\/p>\n

SQL> select table_name, num_rows from user_tables;
TABLE_NAME                       NUM_ROWS------------------------------ ----------TEST_TABTEST_TAB1                        65536600TEST_TAB2                        65443100<\/p>
SQL> select count(*) from test_tab;<\/p>
  COUNT(*)----------   1000000<\/p><\/pre>\n
So there are really a million rows in that table. If we’re going to save stats, we’ll need to create a table to save them into. This is a one-time requirement.<\/p>\n
SQL> exec dbms_stats.create_stat_table('TESTUSER', 'SAVED_STATS');
PL\/SQL procedure successfully completed.<\/p><\/pre>\n
<\/p>\n
Now, when we gather our schema stats, we just tell Oracle about our stats table so that it can save the existing stats. (Note that any calls to DBMS_STATS should be on one line, but I’ve broken it up here for readability)<\/p>\n
SQL>  exec dbms_stats.gather_schema_stats(ownname=>'TESTUSER',        estimate_percent=>10,        statown=>'TESTUSER',        stattab=>'SAVED_STATS',        statid=>'PREVIOUS');
PL\/SQL procedure successfully completed.<\/p><\/pre>\n
Let’s check that we now have some stats on TEST_TAB. Note that NUM_ROWS is not exactly one million – I estimated.<\/p>\n
SQL> select table_name, num_rows from user_tables;
TABLE_NAME                       NUM_ROWS------------------------------ ----------SAVED_STATS                             0TEST_TAB                           998780TEST_TAB1                        65543840TEST_TAB2                        65532810<\/p><\/pre>\n
You can look at the stats table as a normal table, but you need to deciper the information a little. We can see here that there are saved stats for two tables (TEST_TAB1 and TEST_TAB2 that originally had stats)<\/p>\n
SQL> select statid, type, count(*)  2  from saved_stats  3  group by statid, type;
STATID                         T   COUNT(*)------------------------------ - ----------PREVIOUS                       C          6PREVIOUS                       T          2<\/p><\/pre>\n
It’s not rocket science to view the contents of the stats table directly, but Oracle could change the format over time, so it’s probably best to use the supplied procedures for accessing the information. Here, I’m going to look at the saved stats for TEST_TAB1.<\/p>\n
SQL> variable my_num_rows number;SQL> variable my_num_blks number;SQL> variable my_avg_row_len number;SQL> exec dbms_stats.get_table_stats(ownname=>'TESTUSER',     tabname=>'TEST_TAB1',        stattab=>'SAVED_STATS',    statown=>'TESTUSER',    statid=>'PREVIOUS',        numrows=>:my_num_rows,    numblks=>:my_num_blks,    avgrlen=>:my_avg_row_len);
PL\/SQL procedure successfully completed.<\/p>
SQL> print my_num_rows<\/p>
MY_NUM_ROWS-----------   65536600<\/p><\/pre>\n
Now I’ll truncate it so when I next gather stats, they’ll reflect that.<\/p>\n
SQL> truncate table test_tab1;
Table truncated.<\/p>
SQL>  exec dbms_stats.gather_schema_stats(ownname=>'TESTUSER',        estimate_percent=>10,        statown=>'TESTUSER',        stattab=>'SAVED_STATS',        statid=>'PREVIOUS');<\/p>
PL\/SQL procedure successfully completed.<\/p>
SQL> select table_name, num_rows from user_tables;<\/p>
TABLE_NAME                       NUM_ROWS------------------------------ ----------SAVED_STATS                            34TEST_TAB                           999480TEST_TAB1                               0TEST_TAB2                        65554250<\/p><\/pre>\n
So now the stats on TEST_TAB1 show that there are zero rows in that table, but we know it used to have 65 million rows in it (but we might not have known that if we were called out in the middle of the night 😉 )<\/p>\n
Lets have another look at the stats table. Note that there are saved stats for four tables now, saved_stats, test_tab, test_tab1 and test_tab2.<\/p>\n<\/p>\n
SQL> select statid, type, count(*)  2  from saved_stats  3  group by statid, type;
STATID                         T   COUNT(*)------------------------------ - ----------PREVIOUS                       C         34PREVIOUS                       T          4<\/p><\/pre>\n
So hopefully, we’ll be able to look at the old stats for TEST_TAB1<\/p>\n
SQL> exec dbms_stats.get_table_stats(ownname=>'TESTUSER',     tabname=>'TEST_TAB1',        stattab=>'SAVED_STATS',    statown=>'TESTUSER',    statid=>'PREVIOUS',        numrows=>:my_num_rows,    numblks=>:my_num_blks,    avgrlen=>:my_avg_row_len);
PL\/SQL procedure successfully completed.<\/p>
SQL> print my_num_rows<\/p>
MY_NUM_ROWS-----------          0<\/p><\/pre>\n
Even though that we know the current stats are an accurate reflection of test_tab1 in this forced example, let’s say that we want to put the previous stats back again so that we get the previous execution plan. We just need to import them.<\/p>\n
SQL> exec dbms_stats.import_schema_stats(ownname=>'TESTUSER',    stattab=>'SAVED_STATS',     statown=>'TESTUSER',    statid=>'PREVIOUS');
PL\/SQL procedure successfully completed.<\/p>
SQL> select table_name, num_rows from user_tables;<\/p>
TABLE_NAME                       NUM_ROWS------------------------------ ----------SAVED_STATS                             0TEST_TAB                           998780TEST_TAB1                        65543840TEST_TAB2                        65532810<\/p><\/pre>\n
<\/p>\n
More information<\/a><\/p>\n
<\/a><\/p>\n
<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
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 job It’s no coincidence that we were already in the process of implementing a change to our stats collection period to retain a short history of… Continue reading Saving Optimiser Stats – 9i<\/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-999","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1000,"url":"http:\/\/orcldoug.com\/blog\/2006\/06\/18\/saving-optimiser-stats-10g\/","url_meta":{"origin":999,"position":0},"title":"Saving Optimiser Stats – 10g","date":"June 18, 2006","format":false,"excerpt":"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\u2026","rel":"","context":"Similar post","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":999,"position":1},"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":1562,"url":"http:\/\/orcldoug.com\/blog\/2010\/02\/17\/statistics-on-partitioned-tables-part-1\/","url_meta":{"origin":999,"position":2},"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":[]},{"id":992,"url":"http:\/\/orcldoug.com\/blog\/2006\/06\/13\/production-call-out\/","url_meta":{"origin":999,"position":3},"title":"Production Call-out","date":"June 13, 2006","format":false,"excerpt":"On Saturday morning, we ran into a problem on one of our 9.2.0.7 Production databases during an Extract, Transform and Load (ETL) batch process.I was called at 5:15am to look at a job that normally runs in a few minutes but had failed after 30 or 40 minutes when trying\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1055,"url":"http:\/\/orcldoug.com\/blog\/2006\/08\/18\/historical-perspective-part-3\/","url_meta":{"origin":999,"position":4},"title":"Historical Perspective (Part 3)","date":"August 18, 2006","format":false,"excerpt":"Previously I talked about the importance of historical perspective to server performance tuning, but it applies to many areas of working with databases. For the last part of this mini-series, I thought I'd pull together a few more examples.Execution Plans and Optimiser StatisticsI've written several blogs recently about the value\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1568,"url":"http:\/\/orcldoug.com\/blog\/2010\/02\/28\/statistics-on-partitioned-tables-part-4\/","url_meta":{"origin":999,"position":5},"title":"Statistics on Partitioned Tables – Part 4","date":"February 28, 2010","format":false,"excerpt":"In the last post I illustrated the problems you can run into when you rely on Oracle to aggregate statistics on partitions or subpartitions to generate estimated Global Statistics at higher levels of the table. Until there are statistics for all of the relevant structures then aggregation won't take place\u2026","rel":"","context":"With 3 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/999","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=999"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/999\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=999"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=999"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=999"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}