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
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)
Leave a Reply