Oracle’s Active Session History (ASH) is a powerful performance diagnostic tool that enables real-time and historical analysis of session-level activity. However, one common question DBAs and performance engineers often ask is: “How long is ASH data retained?” The answer depends on whether you’re referring to in-memory ASH or persisted ASH stored in AWR.

This article explains the architecture behind ASH data retention, how long data is kept in memory and on disk, and how to check retention duration using SQL.

In-Memory ASH Retention

The primary store for ASH data is a circular buffer in the System Global Area (SGA). This buffer is maintained in memory and queried through the V$ACTIVE_SESSION_HISTORY view. Every second, the Manageability Monitor Lite (MMNL) background process samples all active sessions and writes metadata about those sessions into the ASH buffer.

The size of the ASH buffer is determined by the number of CPUs, with each CPU contributing 2MB to the buffer’s size. The rationale behind this scaling is that the activity an Oracle database can produce is directly related to the number of CPUs available. More CPUs mean the database can handle more sessions and generate more activity, necessitating a larger ASH buffer to hold session activity data.

Although the ASH buffer is designed to retain one hour’s worth of data in memory, the actual duration depends on workload intensity. To check the time range of data in the ASH buffer, query V$ACTIVE_SESSION_HISTORY for sample times or V$ASH_INFO for metadata of ASH:

select oldest_sample_time,latest_sample_time from v$ash_info
union all
select min(sample_time),max(sample_time) from v$active_session_history;

Sample output:

                OLDEST_SAMPLE_TIME                 LATEST_SAMPLE_TIME
__________________________________ __________________________________
21-MAY-25 10.33.44.835000000 AM    21-MAY-25 02.08.49.276000000 PM
21-MAY-25 10.33.44.835000000 AM    21-MAY-25 02.08.48.276000000 PM

The one-second discrepancy between v$ash_info (02:08:49.276) and v$active_session_history (02:08:48.276) arises from sampling timing. v$ash_info reflects the latest buffer update, including in-progress samples, while v$active_session_history shows only fully committed samples, which may lag slightly due to processing delays.

Historical ASH Retention in AWR

For longer-term storage, Oracle persists ASH data in the Automatic Workload Repository (AWR) through the dba_hist_active_sess_history view. During an AWR snapshot (typically every hour), Oracle selects one out of every 10 ASH samples from the buffer and stores them on disk. If the ASH buffer nears capacity before a snapshot, Oracle triggers a preemptive flush to prevent data loss.

The retention of ASH data in dba_hist_active_sess_history is governed by the AWR snapshot retention policy, it is 8 days by default and is configurable.