Restoring Table Statistics

How long does Oracle retain the statistics for ?

The default period for which statistics are retained is 31 days but this can be altered with:-

execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (xx)

– where xx is the number of days to retain them

NOTE: There is an overhead of storage in the SYSAUX tablespace with statistics so care should be taken not to cause the tablespace to fill with the statistics

How do I know how many days the statistics are available for ?

SQL>select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
 31
- will return the number of days stats are currently retained for.
SQL>select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
GET_STATS_HISTORY_AVAILABILITY
23/08/12 03:14:37.685878000 AM +04:00
- will return the oldest possible date stats can be restored from

How do I find the statistics history for a given table ?

select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history;

SQL>select table_name, STATS_UPDATE_TIME from dba_tab_stats_history where owner='SCOTT' and table_name='EMP';
TABLE_NAME             STATS_UPDATE_TIME
EMP                    23/09/12 11:44:30.445776 AM +04:00
Will show the times statistics were regathered for a given table.

How do I restore the statistics ?

Having decided what date you know the statistics were good for, you can use:-

execute DBMS_STATS.RESTORE_TABLE_STATS ('owner','table',date)
execute DBMS_STATS.RESTORE_DATABASE_STATS(date)
execute DBMS_STATS.RESTORE_DICTIONARY_STATS(date)
execute DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(date)
execute DBMS_STATS.RESTORE_SCHEMA_STATS('owner',date)
execute DBMS_STATS.RESTORE_SYSTEM_STATS(date)
ie
execute dbms_stats.restore_table_stats ('SCOTT','EMP','20-SEP-12 11:44:30.445776 AM +04:00');


Advertisements

About Sher khan

Senior Oracle DBA, Oracle 10g , Oracle 11g OCE ,Oracle 12C and Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g , 11g and 12C. I am working in Oracle technology since 2004. Currently working in U.A.E Email: sher487@hotmail.com
This entry was posted in Performance and Tuning. Bookmark the permalink.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s