Archive

I’ve recently been monitoring two databases where a high amount of import/exports are taking place. The SYSAUX and SYSTEM tablespaces have been continually growing.

To resolve this I set the stats retention period to 7 days.

SQL> exec dbms_stats.alter_stats_history_retention(7);

I then continued to monitor the database and found that the SYSAUX tablespace was still continuing to grow. When checking the retention period it showed it to be as set, so I reduced it further to 3 days.

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.