Restore old stats in Oracle from history of stats updated due to automatic job
In Performance issue, some time automatic job of gather stats in Oracle update the stats which caused the performance degradation. you can also restore the stats with the following commands in Oracle:
Check and Restore the Oracle table old stats
--Check old stats present in Database
select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history;
--Restore the table stats to old stats:
execute dbms_stats.restore_table_stats ('SCOTT','EMP','25-JUL-07 12.01.20.766591 PM +02:00');
-- For table Stats
execute DBMS_STATS.RESTORE_TABLE_STATS ('owner','table',date)
-- For Database stats
execute DBMS_STATS.RESTORE_DATABASE_STATS(date)
-- For Dictionary Stats
execute DBMS_STATS.RESTORE_DICTIONARY_STATS(date)
--For Fixed object stats
execute DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(date)
--For Schema stats
execute DBMS_STATS.RESTORE_SCHEMA_STATS('owner',date)
--For System stats
execute DBMS_STATS.RESTORE_SYSTEM_STATS(date)
Check and change the retention of stats
By default, Oracle will keep it around 31 days of stats history. you can change it to any value. In example, we make it to 60 days for holding history.
--Check
select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
--Change to 60 days
execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (60);
Smart way of Technology 