Summary:
In some of the database we noticed that SYSAUX tablespace is keep growing , the STATS history objects are the major contributors for the space consumption, Upon investigation, found that stats history is not getting purged from SYSAUX tablespace though it crossed the retention period.
Symptoms:
1. Run the below script to check the sysuax usage.
@?/rdbms/admin/awrinfo.sql
- SM/OPTSTAT is the top space consumer from the above scripts report
2. The below obejcts might be major space consumed segments in the sysaux tablespace
select segment_name,bytes/1024/1024 from dba_segments order by 2;
· WRI$_OPTSTAT_HISTHEAD_HISTORY
· wri$_optstat_histgrm_history
· I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST
· I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST
Why Purge is not running?
By default, the MMON performs the automatic purge that removes all history older than the following:
* current time - statistics history retention (by default 31 days)
* time of recent analyze in the system - 1
MMON performs the purge of the optimizer stats history automatically, but it has an internal limit of 5 minutes to perform this job.
If the operation takes more than 5 minutes, then it is aborted and stats not purged.
No trace or alert message is reported.
Versions Affected: 10g & 11g versions
Patch for this BUG:
REF: SYSAUX Grows Because Optimizer Stats History is Not Purged [ID 1055547.1]
For 11g, patch 14373728 is available (Ensure to apply POST Patch Installation script after the patch)
After applying this post patch, P_OLD partition will be created and all the old records will be moved to P_OLD partition and for each day one partition(SYS_) will be created
· WRI$_OPTSTAT_HISTHEAD_HISTORY table.
· SYS_IL0003716802C00022$$ - Partition index
· I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST – Global
· I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST – Global
P_OLD partition will not get dropped automatically even after the retention period is over so we may need to drop this partition after the retention period
If we want drop the partition immediately then backup this partition and drop the partitions and rebuild the global indexes
Ref "How to transfer statistics from one database to another [ID 333742.1]" for taking backup of the statistics and restoration from the backup
For 10g, this patch is not available, we may need to the below work-around
Work-Around to housekeep the old records .
I. Check the size of the WRI$_OPTSTAT_HISTHEAD_HISTORY table
select segment_name,bytes/1024/1024 from dba_segments where segment_name='WRI$_OPTSTAT_HISTHEAD_HISTORY';
II if the size of the WRI$_OPTSTAT_HISTHEAD_HISTORY table is in MBs
exec dbms_stats.purge_stats(sysdate-retention_periond)
for ex: exec dbms_stats.purge_stats(sysdate-31)
III If the size of the WRI$_OPTSTAT_HISTHEAD_HISTORY table is in GBs
if the patch is applied and partitions are created , Wait for 31days or retention period. Simply drop the partition P_OLD
Alter table sys. WRI$_OPTSTAT_HISTHEAD_HISTORY drop partition P_OLD;
alter index I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild;
alter index I_WRI$_OPTSTAT_HH_ST rebuild;
Ensure all the below indexes are usable by rebuild them.
select INDEX_NAME,PARTITION_NAME,STATUS from dba_ind_partitions where index_name in ( select index_name from dba_part_indexes where table_name='WRI$_OPTSTAT_HISTHEAD_HISTORY');
select INDEX_NAME,INDEX_TYPE,PARTITIONED,status from dba_indexes where table_name='WRI$_OPTSTAT_HISTHEAD_HISTORY';
if the patch is not applied or want to housekeep the stats records immediately
dbms_stats.purge_stats(days) will take ages to delete the old records so we could try dbms_stats.purge_stats(dbms_stats.purge_all) , it is equilavent to truncate the stats table.
It will be faster than purge days. This is available in 11.2.0.3. for 11.2.02. we need to apply patch 10279045 to get this option.
