The post Purge SYSAUX Tablespace, made on Fabruary 8this, is yet being high accessed. So, if you’re interested, here it goes another post about:
Last week I supported a database was not purging statistics through MMON job, because is timeouting. Worst than simply that, the database is not purging statistics since 2012 and SYSAUX was huge!
To understand: By default, the MMON performs the automatic purge that removes all history older than:
1) current time – statistics history retention (by default 31 days)
2) 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.
The problem was very clear in alert.log, through the entry:
Unexpected error from flashback database MMON timeout action Errors in file /oracle/diag/rdbms/oracle/trace/oracle_mmon_1234567.trc: ORA-12751: cpu time or run time policy violation
But it’s happening since 2012! How to address that?
More“Statistics not Being Auto Purged – Splitting Purge”