How to clean SYSAUX Optimizer Statistics History

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