Friday, 10 January 2014

Optimizer statistics do not get automatically purged (object lock of WRI$_OPTSTAT_HISTHEAD_HISTORY) /// SYSAUX and SYSTEM tablespaces have been continually growing

object lock of WRI$_OPTSTAT_HISTHEAD_HISTORY

The old statistics 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 is 31 days.

The SYSAUX and SYSTEM tablespaces have been continually growing due tothe databases where a high amount of
import/exports and RMAN are taking place.SELECT occupant_name "Item", space_usage_kbytes/1048576 "Space Used (GB)", schema_name "Schema", move_procedure "Move Procedure"FROM v$sysaux_occupantsORDER BY 1/Item Space Used (GB) Schema Move ProcedureSM/OPTSTAT 12.45 SYSTo resolve this I set the stats retention period to 5 days.SQL> exec dbms_stats.alter_stats_history_retention(5);

To find out the oldest available stats you can issue the following:SQL> select dbms_stats.get_stats_history_availability from dual;GET_STATS_HISTORY_AVAILABILITY---------------------------------------------------------------------------28-SEP-13 00.00.00.000000000 +04:00To find out a list of how many stats are gathered for each day between the retention the current date and the oldest stats history issue the following:SQL> select trunc(SAVTIME),count(1) from WRI$_OPTSTAT_HISTHEAD_HISTORY group by trunc(SAVTIME) order by 1;TRUNC(SAV COUNT(1)--------- ----------28-SEP-13 292014029-SEP-13 84368330-SEP-13 51983401-OCT-13 95883602-OCT-13 315805203-OCT-13 28704-OCT-13 125395205-OCT-13 73236106-OCT-13 50718607-OCT-13 18941608-OCT-13 261909-OCT-13 149110-OCT-13 28711-OCT-13 12632412-OCT-13 13955613-OCT-13 18106814-OCT-13 483215-OCT-13 25802716-OCT-13 115217-OCT-13 28718-OCT-13 2783921 rows selected.What has happened here is that the job run by MMON every 24hrs has checked the retention period and tried to run a purge of all stats older than the retention period. As the job has not compeleted within 5 minutes because of the high number of stats collected on each day, the job has given up and rolled back. Therefore the stats are not being purged.As each day continues the SYSAUX table is continuing to fill up because the job fails each night and cannot purge old stats.To resolve this we have to issue a manual purge to clear down the old statistics. This can be UNDO tablespace extensive so it’s best to keep an eye on the amount of UNDO being generated. I suggest starting with the oldest and working fowards.To manually purge the stats issue the following:SQL> exec dbms_stats.purge_stats(to_date('28-SEP-13','DD-MON-YY'));PL/SQL procedure successfully completed.OR

exec DBMS_STATS.PURGE_STATS(SYSDATE-5);

Purge stats older than 5 days

(best to do this in stages if there is

a lot of data (sysdate-30,sydate-25 etc)

Then I tried rebuilding the stats indexes and tables as they would now be fragmented.