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. In this article, we will see, How to restore optimizer statistics in Oracle, How to check the retention of the statistics
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
we can find the history of the statistics of a particular object using the below query in Oracle
select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name='&table_name' and owner='&owner';
Example
select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name='EMP' and owner='SCOTT';
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)
I hope you find this article on How to restore optimizer statistics in Oracle useful. Please do provide the feedback
Related Articles
How to check column statistics in Oracle: check out How to check column statistics in Oracle, what is the significance of the statistics generated and how it is being used
How to check Index statistics in Oracle: check out this post for How to check Index statistics for a table, How to check detailed index statistics for index used in sql hash value
How to check automatic statistics collection in Oracle : check out What is automatic statistics collection in Oracle?, how to enable,disable the automatic statistics collection in Oracle
How to transfer statistics between databases in Oracle: check out about How to transfer statistics between databases in Oracle for a [articular table or whole schema with detailed steps
How to check Stale statistics:visit this page on How to check Stale statistics in oracle, How does Oracle decide if the stats have become stale