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 65453000
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.
11g