How to clean SYSAUX Optimizer Statistics History


Many times we come across the SYSAUX tablespace getting full. We can find what is consuming the most space and can clean up.

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

This feature is used for DBMS_STATS.RESTORE  procedures.

The default retention for Optimizer Statistics History is 31 days. 
Old information is automatically deleted by Oracle Database, Without any manual intervention based on the retention.

1) Find SYSAUX components and their sizes.
select * from v$sysaux_occupants order by space_usage_kbytes desc;


2)Find 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; 

3)Find current retention
select dbms_stats.get_stats_history_retention from dual;

4) Find available stats that have not been purged
select dbms_stats.get_stats_history_availability from dual;

5)Change retention
exec dbms_stats.alter_stats_history_retention(10);

6)Disable retention. (Turn off SYSAUX Optimizer Statistics History feature)
exec dbms_stats.alter_stats_history_retention(0);


7)Completely remove old statistics (the fastest option works as a truncate command)
EXEC DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL);

8)Partially remove old statistics
exec DBMS_STATS.PURGE_STATS(SYSDATE-10);

But this will generate huge undo


Show how big the tables are and rebuild after stats have been purged

select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where  tablespace_name = 'SYSAUX'
and segment_name like 'WRI$_OPTSTAT%'
and segment_type='TABLE'
group by segment_name,segment_type order by 1 asc;


Show how big the indexes are ready for a rebuild after stats have been purged

select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments
where  tablespace_name = 'SYSAUX'
and segment_name like '%OPT%'
and segment_type='INDEX'
group by segment_name,segment_type order by 1 asc;
/

Note that you cannot enable row movement and shrink the tables as the indexes are function-based

Optional: Rebuild %OPT% tables and indexes in SYSAUX.

1. 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;

2. 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';

3. 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';

4. Find 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;




If you like please follow and comment