Yuan Yao, An Oracle ACE's Blog

The view V$ASH_INFO contains information of ASH sampling process, this view is not documented in the official Oracle documentation.

SQL>  desc v$ash_info;
Name                        Null?   Type
TOTAL_SIZE                          NUMBER
FIXED_SIZE                          NUMBER
SAMPLING_INTERVAL                   NUMBER
OLDEST_SAMPLE_ID                    NUMBER
OLDEST_SAMPLE_TIME                  TIMESTAMP(9)
LATEST_SAMPLE_ID                    NUMBER
LATEST_SAMPLE_TIME                  TIMESTAMP(9)
SAMPLE_COUNT                        NUMBER
DROPPED_SAMPLE_COUNT                NUMBER
SAMPLED_BYTES                       NUMBER
SAMPLER_ELAPSED_TIME                NUMBER
DISK_FILTER_RATIO                   NUMBER
AWR_FLUSH_BYTES                     NUMBER
AWR_FLUSH_ELAPSED_TIME              NUMBER
AWR_FLUSH_COUNT                     NUMBER
AWR_FLUSH_EMERGENCY_COUNT           NUMBER
CON_ID                              NUMBER

The LATEST_SAMPLE_TIME is very close to current time (within 1 second) , and the OLDEST_SAMPLE_TIME indicates the earliest ASH data available in Oracle ASH buffer. These two columns define the time window in the ASH buffer.

Given the following SQL query and its output:

SELECT 
    oldest_sample_time, 
    latest_sample_time 
FROM 
    v$ash_info
UNION
SELECT 
    MIN(sample_time), 
    MAX(sample_time) 
FROM 
    v$active_session_history;

Output:

OLDEST_SAMPLE_TIME                LATEST_SAMPLE_TIME
20-AUG-24 01.44.16.102000000 PM   27-AUG-24 02.54.56.038000000 PM
20-AUG-24 01.44.16.102000000 PM   27-AUG-24 02.54.57.062000000 PM

We can see the OLDEST_SAMPLE_TIME equals the earliest sample time in V$ACTIVE_SESSION_HISTORY , and the LATEST_SAMPLE_TIME is very close to the maximum sample time in V$ACTIVE_SESSION_HISTORY.

Active Session History (ASH) data is kept in a circular buffer within the SGA, specifically in the V$ACTIVE_SESSION_HISTORY view. While ASH is designed to retain data for about one hour, the actual retention period can vary from tens of minutes to several days, depending on the database’s activity level rather than the number of sessions connected. The more active the database, the shorter the data retention period.

Since V$ACTIVE_SESSION_HISTORY is a memory-based view with limited space, Oracle samples this view every ten seconds. The sampled data is then written to DBA_HIST_ACTIVE_SESS_HISTORY for historical access, making the latter a “sample of a sample.” The availability of data in DBA_HIST_ACTIVE_SESS_HISTORY depends on the AWR snapshot retention and purge policies. When performing performance analysis, we choose data source:

1. Prefer V$ACTIVE_SESSION_HISTORY if the data is available, as it offers higher resolution and more accurate insights. Remember to verify the availability of ASH data in V$ACTIVE_SESSION_HISTORY rather than assuming its presence.
2. Use DBA_HIST_ACTIVE_SESS_HISTORY for historical analysis when the data in V$ACTIVE_SESSION_HISTORY is no longer available due to the limited retention period.