Oracle Statistics History

Check the current retention value.

select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

For alter this retention period to 90 days

execute dbms_stats.alter_stats_history_retention(90);

For check the old record present in database (upto you restore the stats)

select dbms_stats.get_stats_history_availability from dual;

Check out the statistics history

SELECT owner, table_name, stats_update_time
FROM dba_tab_stats_history
WHERE owner='OWNER_APP'
AND table_name='ORDERS'
ORDER BY stats_update_time;

Check out the differences

select * from table(dbms_stats.diff_table_stats_in_history(
ownname => 'OWNER_APP',
tabname => 'ORDERS',
time1 => systimestamp,
time2 => systimestamp-30,
pctthreshold => 0));

Restore the previous statistics for the whole database

execute dbms_stats.restore_database_stats(sysdate-1);

Restore the previous statistics for a schema

execute dbms_stats.restore_schema_stats (ownname=>'OWNER_APP',AS_OF_TIMESTAMP=>sysdate-1);

Restore the previous statistics for a table

execute dbms_stats.restore_table_stats (ownname=>'OWNER_APP', tabname=>'ORDERS', AS_OF_TIMESTAMP=>sysdate-1);

Flush the shared pool to see the new execute plan after restore

alter system flush shared_pool