Restore table old Statistics from history tables in Oracle

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);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.