AWR Snapshot Retention: Missing Automatic Space‑Based Purge Trigger for SYSAUX

AWR Retention and SYSAUX Space

Oracle Automatic Workload Repository (AWR) does not support a native, space-based trigger to purge snapshots. Retention is governed exclusively by a time-based interval configured via DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS. There is no internal parameter or threshold within the AWR framework that monitors SYSAUX free space to automatically initiate a purge.

Analysis of Behavior

The behavior described—where snapshots fail to write as SYSAUX nears capacity—is a result of tablespace exhaustion rather than a failure of a retention mechanism. Because AWR is not the sole occupant of the SYSAUX tablespace (which also houses Optimizer statistics and other auxiliary components), Oracle does not allow AWR to unilaterally purge data based on space, as this could lead to non-deterministic data loss that conflicts with the administrator's defined retention policy.

Implementation Strategy for Space-Based Purging

To achieve automatic purging based on space, you must implement a custom monitoring and maintenance loop using DBMS_SCHEDULER. The logic should follow these steps:

1. Monitor: Query DBA_TABLESPACE_SPACE_USAGE or DBA_FREE_SPACE to determine the current percentage of free space in SYSAUX.
2. Evaluate: Compare the free space against your internal threshold (e.g., 10% free).
3. Execute: If the threshold is breached, identify the oldest snap_id range using DBA_HIST_SNAPSHOT and call DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE to remove the oldest snapshots.

Compliance and Audit Implications

Moving from a fixed-time retention to a space-based purge introduces several risks:

• Non-Deterministic Retention: You can no longer guarantee that performance data for a specific date (e.g., "the last 30 days") exists. This may violate internal SLAs or regulatory requirements for performance auditing.
• Baseline Corruption: Purging snapshots arbitrarily can create gaps in AWR baselines, rendering comparison reports inaccurate.
• Audit Trails: Since DROP_SNAPSHOT_RANGE is a manual DDL-like operation, every automated purge must be logged to a custom audit table to track exactly which time ranges were deleted and why.

Verification Commands

To verify your current settings and space pressure, use the following queries:

-- Check current AWR retention settings
SELECT * FROM DBA_HIST_WR_CONTROL;

-- Check current SYSAUX space usage
SELECT tablespace_name, used_percent 
FROM DBA_TABLESPACE_SPACE_USAGE 
WHERE tablespace_name = 'SYSAUX';

Diagnostic Detail Needed: Are you using AUTOEXTEND on the SYSAUX datafiles? If AUTOEXTEND is enabled but limited by a MAXSIZE, the recommendation shifts toward increasing the physical disk allocation before implementing an automated purge script.