SLOW HISTORICAL STATISTICS PURGING AND SYSAUX GROWS

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.