Saving Optimiser Stats - 10g

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 for example, but here’s how it looks in action.

SQL> select table_name, num_rows from user_tables
2 /

TABLE_NAME                       NUM_ROWS

------------------------------  ----------

TEST_TAB1                          5453000

TEST_TAB2                         65490100

DUDE                               1599372

TEMP                               1000000

SQL> select count(*) from temp;
COUNT(*)
———-
1000000
SQL> select * from user_tab_stats_history;
no rows selected

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

SQL> exec dbms_stats.gather_schema_stats ( ownname=>’TESTUSER’, estimate_percent=>10)
PL/SQL procedure successfully completed.
SQL> select table_name, stats_update_time
from user_tab_stats_history;
TABLE_NAME
——————————
STATS_UPDATE_TIME
——————————————————
TEMP
18-JUN-06 04.49.10.036992 PM +01:00
TEST_TAB1
18-JUN-06 05.05.55.447281 PM +01:00
TEST_TAB2
18-JUN-06 05.20.04.831991 PM +01:00
DUDE
18-JUN-06 04.49.06.665160 PM +01:00

Now I’ll truncate a couple of tables and regenerate the schema statistics.

SQL> truncate table test_tab1;
Table truncated.
SQL> truncate table test_tab2;
Table truncated.
SQL> exec dbms_stats.gather_schema_stats( ownname=>’TESTUSER’, estimate_percent=>10)
PL/SQL procedure successfully completed.
SQL> select table_name, stats_update_time from user_tab_stats_history;
TABLE_NAME
——————————
STATS_UPDATE_TIME
—————————————————————-
TEMP
18-JUN-06 04.49.10.036992 PM +01:00
TEST_TAB1
18-JUN-06 05.05.55.447281 PM +01:00
TEST_TAB2
18-JUN-06 05.20.04.831991 PM +01:00
DUDE
18-JUN-06 05.22.41.565053 PM +01:00
TEMP
18-JUN-06 05.22.49.773888 PM +01:00
TEST_TAB1
18-JUN-06 05.22.49.798122 PM +01:00
TEST_TAB2
18-JUN-06 05.22.49.823022 PM +01:00
DUDE
18-JUN-06 04.49.06.665160 PM +01:00
8 rows selected.

There are two saved versions now and the current statistics show zero rows in test_tab1 and test_tab2.

SQL> select table_name, num_rows from user_tables;

TABLE_NAME                       NUM_ROWS

------------------------------ ----------

DUDE                              1598840

TEMP                              1004070

TEST_TAB2                               0

TEST_TAB1                               0

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

SQL> exec dbms_stats.restore_schema_stats( ownname=>’TESTUSER’, as_of_timestamp=>sysdate-1);
PL/SQL procedure successfully completed.
SQL> select table_name, num_rows from user_tables;

TABLE_NAME                       NUM_ROWS

------------------------------ ----------

DUDE                              1599372

TEMP                              1000000

TEST_TAB2                        65490100

TEST_TAB1                        65453000

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.

SQL> select table_name, stats_update_time from user_tab_stats_history;
TABLE_NAME
——————————
STATS_UPDATE_TIME
————————————————————–
TEMP
18-JUN-06 04.49.10.036992 PM +01:00
TEST_TAB1
18-JUN-06 05.05.55.447281 PM +01:00
TEST_TAB2
18-JUN-06 05.20.04.831991 PM +01:00
DUDE
18-JUN-06 05.22.41.565053 PM +01:00
TEMP
18-JUN-06 05.22.49.773888 PM +01:00
TEST_TAB1
18-JUN-06 05.22.49.798122 PM +01:00
TEST_TAB2
18-JUN-06 05.22.49.823022 PM +01:00
DUDE
18-JUN-06 05.49.06.966851 PM +01:00
TEMP
18-JUN-06 05.49.07.230811 PM +01:00
TEST_TAB1
18-JUN-06 05.49.07.272036 PM +01:00
TEST_TAB2
18-JUN-06 05.49.07.290720 PM +01:00
DUDE
18-JUN-06 04.49.06.665160 PM +01:00
12 rows selected.