How to restore optimizer statistics

As we know that DBMS_STATS takes a backup of the statistics every time dbms_stats is run on the table. So we can restore the statistics from the backup. Let’s see a few important things in this feature

Table of Contents

How far back in time we can go to restore statistics?

We can find this using the below query

select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

How to check the retention of the statistics

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

The default is 31 days

How to change the retention of the statistics

execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (14);
select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual

How to check the history of stats for a particular object

select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name='&table_name' and owner='&owner';

How to restore the optimizer statistics for the particular table

Here we need to give the table name, owner, and STATS_UPDATE_TIME we get from the above query

execute dbms_stats.restore_table_stats('&table_owner','&table_name','&STATS_UPDATE_TIME');

How to restore the optimizer statistics for the particular schema

execute DBMS_STATS.RESTORE_SCHEMA_STATS('&owner',&date)

How to restore database statistics

execute DBMS_STATS.RESTORE_DATABASE_STATS(&date)