PLSQL: How to restore the stats on a table

Starting with Oracle 10g, if you need to modify/restore the statistics of a table to an older date statistics, because of a change in the execution plan or to check the performance, you could use dbms_stats package to restore the stats. You could also do an export and import, but for that, you should have exported the stats before the gather stats is run.

From Oracle 10g, when statistics are gathered for a table, the old statistics are retained for a pre-defined time, and you can restore the old ones.

Inorder to check for how long the stats are retained, the below queries would help.

The below query will give the number of days the stats are retained.

SELECT DBMS_STATS.get_stats_history_retention
FROM   dual;

The below query will return the date statistics have been purged. Only the stats from the dates after this date is available.

SELECT DBMS_STATS.get_stats_history_availability
FROM   dual;

The below query will give the statistics history for a given table.

SELECT table_name
      ,stats_update_time
FROM   dba_tab_stats_history
WHERE  table_name = 'XXAJ_TEST';
 

You can use  DBMS_STATS.RESTORE_TABLE_STATS ('<schema>','<table_name>',date) to restore the table stats.

BEGIN
 
  DBMS_STATS.restore_table_stats(ownname         => 'XXAJ'
                                ,tabname         => 'XXAJ_TEST'
                                ,as_of_timestamp => '18-JUL-19 12.01.20.766591 PM +08:00');
 
END;

Ref: How to Restore Oracle Optimizer Statistics (Doc ID 452011.1)




Feel free to point out if anything is wrong/missing in this post.