Purging statistics from the SYSAUX tablespace

In Oracle Database, Whenever statistics in the dictionary are modified, old versions of statistics are saved automatically for future restoring purpose. But this old statistics which are saved automatically,  are purged automatically at regular intervals based on the statistics history retention setting and the time of recent statistics gathering performed in the system.

Retention is configurable using the ALTER_STATS_HISTORY_RETENTION procedure. The default value of Retention is 31 days.

If the retention period is not managed then the SYSAUX tablespaces can grow very large.

This is only a small workout in a small database with less data. So the reclaim size of the SYSAUX tablespace will be less. There will be a huge difference in space reclaim of the SYSAUX tablespace on a big database with huge data.

This blog entry will provide the scripts and steps to diagnose and correct the excessive or rapid growth of the tablespace due to retained statistics.

1. Existing Details of the SYSAUX Tablespace

set lines 200 pages 1000
column tablespace_name format a30
column allocated format 999999.99
column free format 999999.99
column used format 999999.99
col Contiguous format 999999.99
break on report
compute sum of allocated on report
compute sum of used on report
compute sum of free on report
compute sum of contiguous on report

select rpad(a.tablespace_name,30,’.’)tablespace_name,
sum(a.bytes)/(1024*1024) Allocated,
sum(a.bytes)/(1024*1024) – max(nvl(b.space,0)) Used,
max(nvl(b.space,0)) Free,round(((max(nvl(b.space,0)))/(sum(a.bytes)/(1024*1024))),4)*100 perctfree,
max(nvl(b.cont,0))/(1024*1024)  Contiguous
from dba_data_files a,
(select tablespace_name,sum(bytes)/(1024*1024) space,max(bytes) cont
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
and a.tablespace_name=’SYSAUX’
group by a.tablespace_name
order by a.tablespace_name
/

From the output we can see that only 150 MB of free space is available in SYSAUX tablespace.

COLUMN “Item” FORMAT A25
COLUMN “Space Used (GB)” FORMAT 999.99
COLUMN “Schema” FORMAT A25
COLUMN “Move Procedure” FORMAT A40

SELECT  occupant_name “Item”,
space_usage_kbytes/1048576 “Space Used (GB)”,
schema_name “Schema”,
move_procedure “Move Procedure”
FROM v$sysaux_occupants
ORDER BY 1
/

2. Check the retention of the Stats.

select dbms_stats.get_stats_history_retention from dual;

3. Alter the retention period of the Stats to 10 Days.

exec dbms_stats.alter_stats_history_retention(10);

select dbms_stats.get_stats_history_retention from dual;

4. Now execute the below command to PURGE the Old Statistics. That is purge the statistics which are older than 10 Days. 

If there is huge data, then it is recommended to purge in stages like (sysdate-30,sydate-25 etc).

exec DBMS_STATS.PURGE_STATS(SYSDATE-10);

5. Now check the History of the Statistics availability.

select dbms_stats.get_stats_history_availability from dual;

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

col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6
set lines 120
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
/

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

col Mb form 9,999,999
col SEGMENT_NAME form a40
col SEGMENT_TYPE form a6
set lines 120
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
/

8. Now we have to rebuild the table. Here in this case, enable row movement and shrink the tables are not possible since the Indexes are function based.

So use the below script to generate the move table script.

select ‘alter table ‘||segment_name||’  move tablespace SYSAUX;’ from dba_segments where tablespace_name = ‘SYSAUX’
and segment_name like ‘%OPT%’ and segment_type=’TABLE’

/

9. Run the commands generated by the script.

10. Rebuild the indexes since the status of the Indexes will be in Unusable state. 

Use the below script to generate the rebuild statements.

select ‘alter index ‘||segment_name||’  rebuild online parallel (degree 14);’ from dba_segments where tablespace_name = ‘SYSAUX’
and segment_name like ‘%OPT%’ and segment_type=’INDEX’

/

11. Run the rebuild statements generated by the script.

12. Before rebuilding the indexes, the status of the indexes will be in Unusable state. After rebuilding the indexes check the status of the indexes.

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
/

13. Now check the size of the SYSAUX tablespace.

set lines 200 pages 1000
column tablespace_name format a30
column allocated format 999999.99
column free format 999999.99
column used format 999999.99
col Contiguous format 999999.99
break on report
compute sum of allocated on report
compute sum of used on report
compute sum of free on report
compute sum of contiguous on report

select rpad(a.tablespace_name,30,’.’)tablespace_name,
sum(a.bytes)/(1024*1024) Allocated,
sum(a.bytes)/(1024*1024) – max(nvl(b.space,0)) Used,
max(nvl(b.space,0)) Free,round(((max(nvl(b.space,0)))/(sum(a.bytes)/(1024*1024))),4)*100 perctfree,
max(nvl(b.cont,0))/(1024*1024)  Contiguous
from dba_data_files a,
(select tablespace_name,sum(bytes)/(1024*1024) space,max(bytes) cont
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
and a.tablespace_name=’SYSAUX’
group by a.tablespace_name
order by a.tablespace_name
/

From the output we can clearly see that there is space gain of nearly 250 MB from the Sysaux tablespace.

14. Now the Retention period is set to 10 Days. 

select dbms_stats.get_stats_history_retention from dual;

If required you can bring back the retention period to the default value 31 days.

exec dbms_stats.alter_stats_history_retention(31);

select dbms_stats.get_stats_history_retention from dual;