How to restore statistics and view statistics history?

Shows in this scenrio there are backup of stats that exists for SCOTT.TEST table
SQL> select * FROM DBA_TAB_STATS_HISTORY where owner = 'SCOTT' and table_name = 'TEST';

no rows selected

Display current timestamp of last time when the table and index was analyzed
SQL> select last_analyzed from dba_indexes where table_name = 'TEST' and table_owner = 'SCOTT';

LAST_ANALYZED
——————–
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25

SQL> select last_analyzed from dba_tables where table_name = 'TEST' and owner = 'SCOTT';

LAST_ANALYZED
——————–
2011-JUL-21 00:20:34

Gather/Update stats on the table
SQL> exec dbms_stats.gather_table_stats('SCOTT', 'TEST', cascade=>true);

Check stats on the table by checking last_analyzed date, shows date updated
SQL> select last_analyzed from dba_indexes where table_name = 'TEST' and table_owner = 'SCOTT';

LAST_ANALYZED
——————–
2011-NOV-26 10:38:57
2011-NOV-26 10:39:14
2011-NOV-26 10:39:18
2011-NOV-26 10:39:19
2011-NOV-26 10:39:28
2011-NOV-26 10:39:28
2011-NOV-26 10:39:28

SQL> select last_analyzed from dba_tables where table_name = 'TEST' and owner = 'SCOTT';

LAST_ANALYZED
——————–
2011-NOV-26 10:38:55

When stats are gathered dbms_stats backups the stats are backup and viewed in DB_TAB_STATS_HISTORY which can be used to restore stats
SQL> select * FROM DBA_TAB_STATS_HISTORY where owner = 'SCOTT' and table_name = 'TEST';

OWNER TABLE_NAME STATS_UPDATE_TIME
————— —————————— —————————-
SCOTT TEST 11-11-26 10:38:56,021074 -07

Restore stats as of yesterday i.e. to time before the stats were run
SQL> exec dbms_stats.restore_table_stats('SCOTT', 'TEST', TO_DATE('26-NOV-2011 11:00', 'DD-MON-YYYY HH24:MI') – 1));

Check last analyzed date it indicates the stats were restored back
SQL> select last_analyzed from dba_indexes where table_name = 'TEST' and table_owner = 'SCOTT';

LAST_ANALYZED
——————–
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25
2006-MAY-09 00:00:25

SQL> select last_analyzed from dba_tables where table_name = 'TEST' and owner = 'SCOTT';

LAST_ANALYZED
——————–
2011-JUL-21 00:20:34

dbms_stats.restore_table_stats backs up the stats again to restore the stats that was changed
SQL> select * FROM DBA_TAB_STATS_HISTORY where owner = 'SCOTT' and table_name = 'TEST';

OWNER TABLE_NAME STATS_UPDATE_TIME
————— —————————— —————————-
SCOTT TEST 11-11-26 11:53:24,517857 -07
SCOTT TEST 11-11-26 10:38:56,021074 -07

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s