Issue Description.
The SYSAUX tablespace is growing continuously and SM/OPTSTAT component occupies users more that 95% of the space.This can be identified by the below query.
SET LINESIZE 130 COL OCCUPANT_NAME FOR a30 COL "Schema" FOR A25 COL "Move Procedure" FOR A40 SELECT Round(space_usage_kbytes / 1024, 0) SPACE_USAGE_MB , occupant_name , schema_name "Schema" , move_procedure "Move Procedure" FROM v$sysaux_occupants ORDER BY 1 DESC;
The query will output components using SYSAUX tablespace and we can see that SM/OPTSTAT component is using largest amount of space.
Cause:
From 10gR1 onwards, Oracle will maintain the backup of the optimizer statistics automatically,and the information is stored in SYSAUX.Purging of this information is handled by MMON process. This will happen once in 24 hrs and if it cannot complete this in 5 mins, it leaves the task,no messages will be written in alert log or anywhere else to indicate that this purge did not happen and due to this the utilization will be increasing.
Solution
1. Identify the retention in the system using the below query. Most common value is 30 days.
select dbms_stats.get_stats_history_retention from dual;
2. Find out the oldest statistics history using below statement. select dbms_stats.get_stats_history_availability from dual;
3. If the date from above step is older than your retention values,Start purging with oldest date and invoke below command
exec dbms_stats.purge_stats(to_date('01-JAN-2010','DD-MON-YYYY'));
NOTE: Please note that this process is resource intensive. Do not perform in business hours.
