How to view/change statistics retention and space used to store stats history?

See the oldest statistics history available SQL> SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL; GET_STATS_HISTORY_AVAILABILITY ————————————————————————— 25-OCT-11 10.00.55.093000000 PM -07:00 See the stats retention SQL> select dbms_stats.get_stats_history_retention from dual; GET_STATS_HISTORY_RETENTION ————————— 31 Modify retention SQL> exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(60); Space currently used to store statistics in SYSAUX in KBytes, so increasing the retention would affect it’s size SQL> select occupant_desc, space_usage_kbytes from v$sysaux_occupants where OCCUPANT_DESC like '%Statistics%'; OCCUPANT_DESC SPACE_USAGE_KBYTES —————————————————————- … Continue reading How to view/change statistics retention and space used to store stats history?

How to restore statistics and view statistics history?

Shows in this scenrio there are backup of stats that exists for SCOTT.TEST table SQL> select * FROM DBA_TAB_STATS_HISTORY where owner = 'SCOTT' and table_name = 'TEST'; no rows selected Display current timestamp of last time when the table and index was analyzed SQL> select last_analyzed from dba_indexes where table_name = 'TEST' and table_owner = 'SCOTT'; LAST_ANALYZED ——————– 2006-MAY-09 00:00:25 2006-MAY-09 00:00:25 2006-MAY-09 00:00:25 2006-MAY-09 … Continue reading How to restore statistics and view statistics history?

How to restore table statistics (STATS)?

When using DBMS_STATS to gather table statics Oracle backups up the statistics before overwriting it. The following example shows how dbms_stats.gather_*_stats works and how to restore statistics Example: — stats was gathered on a table SQL> exec dbms_Stats.gather_table_stats('SCOTT', 'TEST'); PL/SQL procedure successfully completed. — verified stats by checking last_analyzed date SQL> select to_char(last_analyzed, 'DD-MON-YYYY HH24:MI:SS') from user_tables where table_name = 'TEST'; TO_CHAR(LAST_ANALYZE ——————– 06-OCT-2010 00:08:05 … Continue reading How to restore table statistics (STATS)?

How to view and change STATS retention?

Way of changing retention SQL> select dbms_stats.get_stats_history_retention from dual; GET_STATS_HISTORY_RETENTION ————————— 31 — value passed is in days — If 0 is passed – old statistics are never saved. The automatic purge will delete all statistics history — If 1 is passed – statistics history is never purged by automatic purge. SQL> exec dbms_stats.alter_stats_history_retention(30); PL/SQL procedure successfully completed. SQL> select dbms_stats.get_stats_history_retention from dual; GET_STATS_HISTORY_RETENTION ————————— … Continue reading How to view and change STATS retention?

How to find oldest STATS information?

Using dbms_stats.get_stats_history_availability one can find date of oldest STATS information. STATS older than this timestamp cannot be restored. SQL> select dbms_stats.get_stats_history_availability from dual; GET_STATS_HISTORY_AVAILABILITY ————————————————————————— 09-FEB-10 10.13.18.954562000 PM -07:00 Continue reading How to find oldest STATS information?