[EN] Cleaning SYSAUX Optimizer Statistics History

When new optimizer statistics collected for a database object, old statisitcs are sent to SYSAUX tablespace. This information is stored in "Server Manageability - Optimizer Statistics History" component of SYSAUX tablespace.

By this feature we can use DBMS_STATS.RESTORE... procedures.

The default retention for Optimizer Statistics History is 31 days. Old information are automaticly deleted by Oracle Database. Without any manual intervention.

But sometimes this 31 day retention may be too much. In one of my database this component was 75 GB.

--Check SYSAUX components and their sizes.

select * from v$sysaux_occupants order by space_usage_kbytes desc;

--Check biggest segments in SYSAUX

select tablespace_name,owner,segment_name,partition_name,segment_type,trunc((bytes/1024/1024/1024),2) "GB" 

from dba_segments where tablespace_name = 'SYSAUX' order by bytes desc; 

--Check current retention

select dbms_stats.get_stats_history_retention from dual;

--Check how far in past optimizer stats go

select dbms_stats.get_stats_history_availability from dual;

--Change retention

exec dbms_stats.alter_stats_history_retention(10);

--Disable retention. (Turn off SYSAUX Optimizer Statistics History feature)

exec dbms_stats.alter_stats_history_retention(0);

--Completely remove old statistics (This is the fastest option. Acts like a truncate command)
EXEC DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL);

--Partially remove old statistics

exec DBMS_STATS.PURGE_STATS(SYSDATE-10);

Above command can take too long and also consume too much UNDO space. So i use the following series of commands;

exec DBMS_STATS.PURGE_STATS(SYSDATE-31);

exec DBMS_STATS.PURGE_STATS(SYSDATE-30);

exec DBMS_STATS.PURGE_STATS(SYSDATE-29);

exec DBMS_STATS.PURGE_STATS(SYSDATE-28);

exec DBMS_STATS.PURGE_STATS(SYSDATE-27);

exec DBMS_STATS.PURGE_STATS(SYSDATE-26);

exec DBMS_STATS.PURGE_STATS(SYSDATE-25);

exec DBMS_STATS.PURGE_STATS(SYSDATE-24);

exec DBMS_STATS.PURGE_STATS(SYSDATE-23);

exec DBMS_STATS.PURGE_STATS(SYSDATE-22);

exec DBMS_STATS.PURGE_STATS(SYSDATE-21);

exec DBMS_STATS.PURGE_STATS(SYSDATE-20);

exec DBMS_STATS.PURGE_STATS(SYSDATE-19);

exec DBMS_STATS.PURGE_STATS(SYSDATE-18);

exec DBMS_STATS.PURGE_STATS(SYSDATE-17);

exec DBMS_STATS.PURGE_STATS(SYSDATE-16);

exec DBMS_STATS.PURGE_STATS(SYSDATE-15);

exec DBMS_STATS.PURGE_STATS(SYSDATE-14);

exec DBMS_STATS.PURGE_STATS(SYSDATE-13);

exec DBMS_STATS.PURGE_STATS(SYSDATE-12);

exec DBMS_STATS.PURGE_STATS(SYSDATE-11);

exec DBMS_STATS.PURGE_STATS(SYSDATE-10);

exec DBMS_STATS.PURGE_STATS(SYSDATE-9);

exec DBMS_STATS.PURGE_STATS(SYSDATE-8);

exec DBMS_STATS.PURGE_STATS(SYSDATE-7);

exec DBMS_STATS.PURGE_STATS(SYSDATE-6);

exec DBMS_STATS.PURGE_STATS(SYSDATE-5);

exec DBMS_STATS.PURGE_STATS(SYSDATE-4);

exec DBMS_STATS.PURGE_STATS(SYSDATE-3);

exec DBMS_STATS.PURGE_STATS(SYSDATE-2);

exec DBMS_STATS.PURGE_STATS(SYSDATE-1);

After we clear statistics history, we can optionally rebuild %OPT% tables and indexes in SYSAUX.

--First check status of all %OPT% indexes. They must be VALID before rebuild.

select  di.index_name,di.index_type,di.status  from  dba_indexes di , dba_tables dt

where  di.tablespace_name = 'SYSAUX' and dt.table_name = di.table_name

and di.table_name like '%OPT%' order by 1 asc;

--Rebuild tables.

select 'alter table '||segment_name||'  move tablespace SYSAUX parallel 8;' from dba_segments where tablespace_name = 'SYSAUX'

and segment_name like '%OPT%' and segment_type='TABLE';

--Rebuild indexes.

select 'alter index '||segment_name||'  rebuild online parallel 8;' from dba_segments where tablespace_name = 'SYSAUX'

and segment_name like '%OPT%' and segment_type='INDEX';

--Check status of all %OPT% indexes. They must be VALID after rebuild.

select  di.index_name,di.index_type,di.status  from  dba_indexes di , dba_tables dt

where  di.tablespace_name = 'SYSAUX' and dt.table_name = di.table_name

and di.table_name like '%OPT%' order by 1 asc;