Tuesday, July 24, 2012

AWR data is not purged - SYSAUX keeps growing

Today noticed SYSAUX tablespace crossed 80% usage limit. First few things came in mind was
1] SYSAUX is used by some DBA collegues.
2] New database features are installed
3] AWR retention might be too big
4] Snapshot frequency is altered/increased

On examiniation none of above was true. In fact, AWR retention was just 8 days.

Found link to metalink document
WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged [ID 387914.1]
Folowed this document but setting event as per doc never errored out and it was always successful. So this document scenerio is different from the one I am facing

AWR Retention

select snap_interval, retention, most_recent_purge_time from sys.wrm$_wr_control;
SNAP_INTERVAL        RETENTION            MOST_RECENT_PURGE_TIME
-------------------- -------------------- ---------------------------------
+00000 00:30:00.0    +00008 00:00:00.0    23-JUL-12 10.06.25.744 PM


select INSTANCE_NUMBER,SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME
from DBA_HIST_SNAPSHOT
where SNAP_ID=(select min(SNAP_ID) from DBA_HIST_SNAPSHOT);

INSTANCE_NUMBER    SNAP_ID BEGIN_INTERVAL_TIME       END_INTERVAL_TIME
--------------- ---------- ------------------------- -------------------------
              1      25385 03-MAY-12 01.45.05.264 PM 03-MAY-12 02.00.00.499 PM
              2      25385 03-MAY-12 01.45.05.144 PM 03-MAY-12 02.00.00.653 PM
              3      25385 03-MAY-12 01.45.05.268 PM 03-MAY-12 02.00.00.863 PM
              4      25385 03-MAY-12 01.45.05.262 PM 03-MAY-12 02.00.00.630 PM


Strange observation

As per DBA_HIST_SNAPSHOT, DBA_HIST_ASH_SNAPSHOT oldest snapshot was of 03-MAY-12 but as per below query oldest retained data was of 07-APR-12

select INSTANCE_NUMBER,min(snap_id),min(SAMPLE_TIME) from sys.WRH$_ACTIVE_SESSION_HISTORY group by INSTANCE_NUMBER;
INSTANCE_NUMBER MIN(SNAP_ID) MIN(SAMPLE_TIME)
--------------- ------------ -------------------------------------------------
              3        22913 07-APR-12 07.59.59.976 PM
              2        22913 07-APR-12 08.00.05.535 PM
              1        22913 07-APR-12 08.00.04.182 PM
              4        22913 07-APR-12 08.00.00.830 PM

Action Taken

exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(22913,22913);

It took very long to delete this snapshot but along with it, it also started purging old data.

SQL>select INSTANCE_NUMBER,min(SAMPLE_TIME) from sys.WRH$_ACTIVE_SESSION_HISTORY
  2  group by INSTANCE_NUMBER
  3  order by 1;


INSTANCE_NUMBER MIN(SAMPLE_TIME)
--------------- --------------------------------
              1 10-APR-12 03.30.07.620 PM
              2 10-APR-12 03.30.13.158 PM
              3 10-APR-12 03.15.01.995 PM
              4 10-APR-12 03.30.07.117 PM

SQL>/
INSTANCE_NUMBER MIN(SAMPLE_TIME)
--------------- --------------------------------
              1 11-APR-12 04.59.56.218 PM
              2 13-APR-12 05.30.30.874 AM
              3 13-APR-12 06.59.07.517 AM
              4 11-APR-12 04.54.07.145 PM

SQL>/
INSTANCE_NUMBER MIN(SAMPLE_TIME)
--------------- --------------------------------
              1 13-APR-12 06.31.06.346 PM
              2 13-APR-12 06.44.59.087 PM
              3 13-APR-12 07.01.01.946 PM
              4 13-APR-12 09.16.29.905 PM

SQL>/
INSTANCE_NUMBER MIN(SAMPLE_TIME)
--------------- --------------------------------
              1 14-APR-12 07.14.42.125 AM
              2 14-APR-12 06.14.46.841 AM
              3 14-APR-12 06.00.13.102 AM
              4 14-APR-12 06.15.20.638 AM

SQL>/
INSTANCE_NUMBER MIN(SAMPLE_TIME)
--------------- --------------------------------
              1 03-MAY-12 01.44.56.561 PM
              2 03-MAY-12 01.45.01.572 PM
              3 03-MAY-12 01.44.59.825 PM
              4 03-MAY-12 01.45.03.027 PM