To check how far back in time we can go to restore statistics
select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
--------------------------------------------------------------------
24-AUG-17 13.57.46.586233000 +01:00
To check the retention period is for the statistics
select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
To change retention
execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (14);
To check different statistics for the specified object
select TABLE_NAME, STATS_UPDATE_TIME
from dba_tab_stats_history
where table_name='MYOBJECTS' and owner='SYSTEM';
To restore statistics of specified object
execute dbms_stats.restore_table_stats(‘SYSTEM’,’MYOBJECTS’,’18-MAR-11 10.00.19.496616 PM +08:00′);
To purge the specified statistics of history table
execute dbms_stats.purge_stats('06-OCT-09 09.52.45.351000000 PM -05:00');
To see Number of rows of specified table with statistics information.
select num_rows
from dba_tables
where table_name='MYOBJECTS' and owner='SYSTEM';
To see date of last analyzed
select last_analyzed
from dba_indexes
where table_name = 'TEST' and table_owner = 'SCOTT';
To gather statistics of a table
exec dbms_stats.gather_table_stats('SCOTT', 'TEST', cascade=>true);
