Saving Optimiser Stats - 9i

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 stats.

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.

Saving optimiser statistics seems like a good idea. That's probably why 10g can do so automatically.

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.

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

First of all the test_schema - note that TEST_TAB doesn't have any statistics on it at the moment.

SQL> select table_name, num_rows from user_tables;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
TEST_TAB
TEST_TAB1                        65536600
TEST_TAB2                        65443100

SQL> select count(*) from test_tab;

  COUNT(*)
----------
   1000000

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.

SQL> exec dbms_stats.create_stat_table('TESTUSER', 'SAVED_STATS');

PL/SQL procedure successfully completed.

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)

SQL>  exec dbms_stats.gather_schema_stats(ownname=>'TESTUSER',
        estimate_percent=>10,
        statown=>'TESTUSER',
        stattab=>'SAVED_STATS',
        statid=>'PREVIOUS');

PL/SQL procedure successfully completed.

Let's check that we now have some stats on TEST_TAB. Note that NUM_ROWS is not exactly one million - I estimated.

SQL> select table_name, num_rows from user_tables;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
SAVED_STATS                             0
TEST_TAB                           998780
TEST_TAB1                        65543840
TEST_TAB2                        65532810

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)

SQL> select statid, type, count(*)
  2  from saved_stats
  3  group by statid, type;

STATID                         T   COUNT(*)
------------------------------ - ----------
PREVIOUS                       C          6
PREVIOUS                       T          2

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.

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.

SQL> print my_num_rows

MY_NUM_ROWS
-----------
   65536600

Now I'll truncate it so when I next gather stats, they'll reflect that.

SQL> truncate table test_tab1;

Table truncated.

SQL>  exec dbms_stats.gather_schema_stats(ownname=>'TESTUSER',
        estimate_percent=>10,
        statown=>'TESTUSER',
        stattab=>'SAVED_STATS',
        statid=>'PREVIOUS');

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows from user_tables;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
SAVED_STATS                            34
TEST_TAB                           999480
TEST_TAB1                               0
TEST_TAB2                        65554250

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 )

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.

SQL> select statid, type, count(*)
  2  from saved_stats
  3  group by statid, type;

STATID                         T   COUNT(*)
------------------------------ - ----------
PREVIOUS                       C         34
PREVIOUS                       T          4

So hopefully, we'll be able to look at the old stats for TEST_TAB1

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.

SQL> print my_num_rows

MY_NUM_ROWS
-----------
          0

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.

SQL> exec dbms_stats.import_schema_stats(ownname=>'TESTUSER',
    stattab=>'SAVED_STATS', 
    statown=>'TESTUSER',
    statid=>'PREVIOUS');

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows from user_tables;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
SAVED_STATS                             0
TEST_TAB                           998780
TEST_TAB1                        65543840
TEST_TAB2                        65532810

More information