Statistics History restore table old stats in Oracle

Restore old table statistics in Oracle

Check and Restore the old statistics of tables in Oracle
Oracle Stats will help the optimizer to choose better execution plan.Oracle has scheduled automatic job for gather stats.

Query for a particular table caused performance issue. Then we need to check stats for that table and try to execute the query with old stats. May it will fix the query performance issue. For every major activity its good to takes backup of stats like upgrade,patching,schema export/import etc,application deployment etc

DBMS_STATS.RESTORE_TABLE_STATS package is used to restore the previous stats present in database.

Syntax:

DBMS_STATS.RESTORE_TABLE_STATS(
ownname => 'SCOTT',
tabname => 'TEST',
as_of_timestamp => SYSTIMESTAMP-1,
force=> FALSE,
no_invalidate => FALSE);
No_invalidate parameter means cursor referencing to table will be invalidated or not.

Note: Automatic backup for stats is done by oracle having default retention policy is 31 days.

Check the old statistics in oracle
Suppose we have oracle SCOTT as user and TEST as Table name

1. For checking old history available for restore from oracle database.

select dbms_stats.get_stats_history_availability from dual;
Note:GET_STATS_HISTORY_AVAILABILITY: Function returns the oldest time-stamp that can be used to restore statistics

2. Check old stats present for the particular table.

select table_name,stats_update_time from DBA_TAB_STATS_HISTORY where table_name='TEST';

TABLE_NAME         STATS_UPDATE_TIME
----------------   -----------------------
TEST               2010-05-10:09:47:16
TEST               2010-05-15:21:47:38
TEST               2010-05-16:10:50:30

OR

select to_char(last_analyzed,'DD.MM.YYYY HH24:MI:SS') from dba_tables where table_name='TEST' and owner='SCOTT';

3. Restore Old Table stats

exec dbms_stats.restore_table_stats('SCOTT', 'TEST', to_timestamp('2010-05-16:10:50:30','yyyy-mm-dd:hh24:mi:ss'));

OR

exec dbms_stats.restore_table_stats(ownname=>'SCOTT', tabname=>'TEST', AS_OF_TIMESTAMP=>'16/05/10 10:50:30');

Change the default retention policy for statistics in oracle:
1. Check the current retention value.

select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

2. For alter this retention period to 20 days

execute dbms_stats.alter_stats_history_retention(60);

3. For check the old record present in database (upto you restore the stats)

select dbms_stats.get_stats_history_availability from dual;

4. Manually purge stats by following command:

execute dbms_stats.purge_stats('06-OCT-09 09.52.45.351000000');

5.