Restoring the stats

Sometimes it is required to restore the stats from history. Oracle 10g provides automatic retention of stats in dictionary. By default it keeps it for 31 days. And you can get it back anytime.


History information is available in dba_tab_stats_history.

Two functions can tell the retention and availability of the history

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
1 row selected.

SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
GET_STATS_HISTORY_AVAILABILITY
---------------------------------------------------------------------------
08-JAN-09 10.01.52.927345000 PM -05:00
1 row selected.

****************
Now to restore the stats for any day, we just have to use the restore_schema_stats, restore_table_stats etc functions


Case Study

13:22:42 SYS@ptdev SQL> create table maktest.a (id int);
Table created.
Elapsed: 00:00:00.14

13:22:54 SYS@ptdev SQL> insert into maktest.a values(1);
1 row created.
Elapsed: 00:00:00.00
13:23:06 SYS@ptdev SQL> /
1 row created.
Elapsed: 00:00:00.00
13:23:06 SYS@ptdev SQL> /
1 row created.
Elapsed: 00:00:00.00
13:23:07 SYS@ptdev SQL> commit;
Commit complete.
Elapsed: 00:00:00.01

13:23:14 SYS@ptdev SQL> exec dbms_stats.gather_schema_stats('MAKTEST');
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.83

13:24:28 SYS@ptdev SQL> select table_name, num_rows from dba_tables where owner = 'MAKTEST';
TABLE_NAME NUM_ROWS
------------------------------ ----------
ORDER_DETAILS 0
A 3
2 rows selected.
Elapsed: 00:00:00.12

13:24:46 SYS@ptdev SQL> select table_name, stats_update_time from dba_tab_stats_history where owner = 'MAKTEST' and table_name = 'A';
TABLE_NAME STATS_UPDATE_TIME
------------------------------ ---------------------------------------------------------------------------
A 09-FEB-09 01.24.28.094433 PM -05:00
1 row selected.
Elapsed: 00:00:00.03

13:25:46 SYS@ptdev SQL> truncate table maktest.a;
Table truncated.
Elapsed: 00:00:00.05

13:25:58 SYS@ptdev SQL> exec dbms_stats.gather_schema_stats('MAKTEST');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.42

13:26:05 SYS@ptdev SQL> select table_name, num_rows from dba_tables where owner = 'MAKTEST';
TABLE_NAME NUM_ROWS
------------------------------ ----------
ORDER_DETAILS 0
A 0
2 rows selected.
Elapsed: 00:00:00.15

13:26:09 SYS@ptdev SQL> select table_name, stats_update_time from dba_tab_stats_history where owner = 'MAKTEST' and table_name = 'A';
TABLE_NAME STATS_UPDATE_TIME
------------------------------ ---------------------------------------------------------------------------
A 09-FEB-09 01.24.28.094433 PM -05:00
A 09-FEB-09 01.26.05.034477 PM -05:00
2 rows selected.
Elapsed: 00:00:00.00

13:26:20 SYS@ptdev SQL> exec dbms_stats.restore_schema_stats(ownname=>'MAKTEST', as_of_timestamp=>sysdate-2/1400);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.27

13:27:27 SYS@ptdev SQL> select table_name, num_rows from dba_tables where owner = 'MAKTEST';
TABLE_NAME NUM_ROWS
------------------------------ ----------
ORDER_DETAILS 0
A 3
2 rows selected.
Elapsed: 00:00:00.06



Conclusion
We do not need to enable any manual script to cature the stats history. Its
present by default in 10g for 31 days and can be restored at anytime.