Hi all!
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?
First, let’s take a look on KB:
Bug 18608261 – Slow MMON auto-purge task (Doc ID 18608261.8)
Bug 16903536 – ORA-12751 in MMON during regular AWR purge (Doc ID 16903536.8)
SYSAUX Grows Because Optimizer Stats History is Not Purged (Doc ID 1055547.1)
You can still follow the post Purge SYSAUX Tablespace. It solves the question and implement the “shrinks”.
But for an huge database it might take some time… And, occasionally you might to do it on maintenance windows in more than one part… So, this can help you:
Checking how old your stats are:
select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
Script to purge day by day (max 2.000 days ~5 years per execution :P):
set serveroutput on size unlimited
set time on
set timing on
spool purge_stats.log
declare
vRetentionLimit Date;
vOldestStat Date := to_date('13/02/2012 00:00','dd/mm/yyyy hh24:mi'); -- inform oldest stats date
vStopExecuting Date := to_date('29/04/2016 08:30','dd/mm/yyyy hh24:mi'); -- inform maintance windows ending
begin
select to_date(sysdate-dbms_stats.get_stats_history_retention) into vRetentionLimit from dual;
for i in 1..2000 loop
if sysdate>=vStopExecuting then
exit;
end if;
if vOldestStat <= vRetentionLimit then
dbms_output.put_line(to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') || ' - Purging from: ' || to_char(vOldestStat,'dd.mm.yyyy hh24:mi:ss'));
dbms_stats.purge_stats(vOldestStat);
dbms_output.put_line(to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') || ' - Purged from: ' || to_char(vOldestStat,'dd.mm.yyyy hh24:mi:ss')||chr(13)||chr(10) );
end if;
vOldestStat:=vOldestStat+1;
end loop;
end;
/
spool off
This way, the purge can be splitted on day-by-day windows. Now you can make the moves and rebuilds told on Purge SYSAUX Tablespace 🙂
Hope it helped you!
Chers!
Matheus.
