DBA_HIST_ACTIVE_SESS_HISTORY - Historical ASH Queries

DBA_HIST_ACTIVE_SESS_HISTORY

Overview

DBA_HIST_ACTIVE_SESS_HISTORY (DASH) is the Automatic Workload Repository (AWR) persistent store of Active Session History samples. Every 10 seconds, the in-memory V$ACTIVE_SESSION_HISTORY ring buffer captures one row per active session. Approximately every hour, AWR flushes a representative subset of those samples — roughly one in ten — into DBA_HIST_ACTIVE_SESS_HISTORY, where they are retained for the AWR retention period (typically 8–31 days in standard configurations).

DASH is the primary view for diagnosing performance incidents that have already ended: a slow batch job from last night, a spike that occurred over the weekend, a gradual degradation that users reported during a maintenance window. It provides the same columns as V$ACTIVE_SESSION_HISTORY but spans the AWR retention window, enabling time-range analysis, wait event trending, top SQL identification, and blocking session history — all of which V$ACTIVE_SESSION_HISTORY loses as the ring buffer overwrites old data.

View Type: AWR Data Dictionary View Available Since: Oracle 10g Release 2 Required Privileges: SELECT on DBA_HIST_ACTIVE_SESS_HISTORY requires the DIAGNOSTICS PACK license (Oracle Enterprise Edition) plus SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY; accessing AWR data without the Diagnostics Pack license violates Oracle’s licensing terms

Key Columns

Column	Datatype	Description
SNAP_ID	NUMBER	AWR snapshot ID; join to DBA_HIST_SNAPSHOT for the snapshot begin and end times
DBID	NUMBER	Database identifier; necessary in multi-database AWR repositories
INSTANCE_NUMBER	NUMBER	RAC instance number the sample was collected from
SAMPLE_ID	NUMBER	Unique sequential identifier for each ASH sample collection cycle
SAMPLE_TIME	TIMESTAMP(3)	Exact timestamp of the ASH sample; primary column for time-range filtering
SESSION_ID	NUMBER	Session identifier (SID) at the time of the sample; equivalent to V$SESSION.SID
SESSION_SERIAL#	NUMBER	Session serial number; combined with SESSION_ID uniquely identifies a session even after SID reuse
USER_ID	NUMBER	Oracle user ID of the session; join to DBA_USERS.USER_ID for username
SQL_ID	VARCHAR2(13)	SQL_ID of the SQL statement the session was executing; NULL for idle sessions or sessions in PL/SQL without an active SQL call
TOP_LEVEL_SQL_ID	VARCHAR2(13)	SQL_ID of the top-level call (e.g., the PL/SQL block) even when a nested SQL statement is being executed
SQL_PLAN_HASH_VALUE	NUMBER	Execution plan hash for the current SQL_ID; used to detect plan changes over time
EVENT	VARCHAR2(64)	Wait event the session was waiting on; NULL or ‘ON CPU’ indicates the session was consuming CPU
WAIT_CLASS	VARCHAR2(64)	Wait class of the event (e.g., User I/O, Concurrency, Network, Application)
SESSION_STATE	VARCHAR2(7)	WAITING (on a wait event) or ON CPU
BLOCKING_SESSION	NUMBER	SID of the session that was blocking this session at the time of the sample
BLOCKING_SESSION_SERIAL#	NUMBER	Serial number of the blocking session; use with BLOCKING_SESSION for unique identification
CURRENT_OBJ#	NUMBER	Object ID of the object being accessed; join to DBA_OBJECTS.OBJECT_ID for the object name
CURRENT_FILE#	NUMBER	File number of the datafile being accessed; useful for I/O hot-spot analysis
CURRENT_BLOCK#	NUMBER	Block number within the file being accessed; combined with CURRENT_FILE# pinpoints hot blocks
MACHINE	VARCHAR2(64)	Client machine name from which the session connected
PROGRAM	VARCHAR2(48)	Client program name (e.g., JDBC Thin Client, sqlplus.exe)
MODULE	VARCHAR2(64)	Application module name set via DBMS_APPLICATION_INFO.SET_MODULE
ACTION	VARCHAR2(64)	Application action name set via DBMS_APPLICATION_INFO.SET_ACTION
TEMP_SPACE_ALLOCATED	NUMBER	Temp tablespace bytes allocated for this session at the time of the sample (Oracle 12c+)
PGA_ALLOCATED	NUMBER	PGA bytes allocated for this session at the sample time
CON_ID	NUMBER	Container ID in a CDB environment; 0 = CDB root, 1 = root, 2+ = specific PDB

Essential Queries

Basic Usage

Query DASH for all active sessions in a specific time range, ordered by sample time:

SELECT
    h.sample_time,
    h.instance_number,
    h.session_id,
    h.session_serial#,
    u.username,
    h.sql_id,
    h.session_state,
    h.event,
    h.wait_class,
    h.blocking_session,
    h.machine,
    h.program,
    h.module
FROM
    dba_hist_active_sess_history h
    LEFT JOIN dba_users u ON u.user_id = h.user_id
WHERE
    h.sample_time >= TO_TIMESTAMP('&start_time', 'YYYY-MM-DD HH24:MI:SS')
    AND h.sample_time <  TO_TIMESTAMP('&end_time',   'YYYY-MM-DD HH24:MI:SS')
    AND h.dbid = (SELECT dbid FROM v$database)
ORDER BY
    h.sample_time,
    h.session_id;

Top SQL by Active Session Count (Past 24 Hours)

Identify the SQL statements that were active in the most ASH samples over the past 24 hours — these are the largest contributors to database load:

SELECT
    h.sql_id,
    ROUND(COUNT(*) * 10 / 3600, 2)             AS est_active_hours,
    COUNT(*)                                    AS ash_samples,
    COUNT(CASE WHEN h.session_state = 'ON CPU'  THEN 1 END) AS cpu_samples,
    COUNT(CASE WHEN h.session_state = 'WAITING' THEN 1 END) AS wait_samples,
    ROUND(COUNT(CASE WHEN h.session_state = 'ON CPU'  THEN 1 END) * 100.0 / COUNT(*), 1) AS pct_cpu,
    MAX(h.machine)                              AS sample_machine,
    MAX(h.module)                               AS sample_module,
    SUBSTR(MAX(q.sql_text), 1, 150)             AS sql_text_sample
FROM
    dba_hist_active_sess_history h
    LEFT JOIN v$sql q ON q.sql_id = h.sql_id
WHERE
    h.sample_time >= SYSTIMESTAMP - INTERVAL '24' HOUR
    AND h.dbid          = (SELECT dbid FROM v$database)
    AND h.sql_id        IS NOT NULL
GROUP BY
    h.sql_id
ORDER BY
    ash_samples DESC
FETCH FIRST 20 ROWS ONLY;

Wait Event Trend Over Past 7 Days

Aggregate wait events by hour to detect recurring patterns — nightly spikes, morning rush-hour contention, or batch job interference:

SELECT
    TRUNC(h.sample_time, 'HH24')               AS sample_hour,
    h.wait_class,
    h.event,
    COUNT(*)                                    AS ash_samples,
    ROUND(COUNT(*) * 10 / 60, 1)               AS est_active_minutes
FROM
    dba_hist_active_sess_history h
WHERE
    h.sample_time >= SYSTIMESTAMP - INTERVAL '7' DAY
    AND h.dbid = (SELECT dbid FROM v$database)
    AND h.wait_class NOT IN ('Idle')
GROUP BY
    TRUNC(h.sample_time, 'HH24'),
    h.wait_class,
    h.event
ORDER BY
    sample_hour,
    ash_samples DESC;

Historical Blocking Session Analysis

Reconstruct blocking chains from historical ASH data, useful for post-incident analysis when the blocking was missed in real time:

SELECT
    h.sample_time,
    h.session_id                                AS blocked_sid,
    h.session_serial#                           AS blocked_serial,
    bu.username                                 AS blocked_user,
    h.sql_id                                    AS blocked_sql_id,
    h.event                                     AS wait_event,
    h.blocking_session                          AS blocker_sid,
    h.blocking_session_serial#                  AS blocker_serial,
    hb.sql_id                                   AS blocker_sql_id,
    hb.event                                    AS blocker_event,
    bbu.username                                AS blocker_user,
    h.machine                                   AS blocked_machine
FROM
    dba_hist_active_sess_history h
    LEFT JOIN dba_hist_active_sess_history hb
           ON hb.sample_id      = h.sample_id
          AND hb.session_id     = h.blocking_session
          AND hb.session_serial# = h.blocking_session_serial#
          AND hb.dbid           = h.dbid
    LEFT JOIN dba_users bu  ON bu.user_id  = h.user_id
    LEFT JOIN dba_users bbu ON bbu.user_id = hb.user_id
WHERE
    h.sample_time >= SYSTIMESTAMP - INTERVAL '24' HOUR
    AND h.dbid = (SELECT dbid FROM v$database)
    AND h.blocking_session IS NOT NULL
ORDER BY
    h.sample_time,
    h.session_id;

Top Objects by Wait Activity

Find the database objects that appeared most frequently in I/O and concurrency waits, pointing to hot tables or indexes:

SELECT
    h.current_obj#,
    o.owner,
    o.object_name,
    o.object_type,
    h.wait_class,
    COUNT(*)                                    AS ash_samples,
    ROUND(COUNT(*) * 10 / 60, 1)               AS est_wait_minutes,
    COUNT(DISTINCT h.session_id)                AS distinct_sessions
FROM
    dba_hist_active_sess_history h
    LEFT JOIN dba_objects o ON o.object_id = h.current_obj#
WHERE
    h.sample_time >= SYSTIMESTAMP - INTERVAL '24' HOUR
    AND h.dbid            = (SELECT dbid FROM v$database)
    AND h.current_obj#    > 0
    AND h.session_state   = 'WAITING'
    AND h.wait_class NOT IN ('Idle', 'Network')
GROUP BY
    h.current_obj#,
    o.owner,
    o.object_name,
    o.object_type,
    h.wait_class
ORDER BY
    ash_samples DESC
FETCH FIRST 25 ROWS ONLY;

V$ACTIVE_SESSION_HISTORY vs DBA_HIST_ACTIVE_SESS_HISTORY Union

Combine current in-memory ASH with historical DASH for seamless queries spanning from now back through the AWR retention window:

-- Combined real-time + historical ASH spanning the last 48 hours
SELECT
    'HISTORICAL'                                AS source,
    TRUNC(h.sample_time, 'HH24')               AS sample_hour,
    h.wait_class,
    COUNT(*)                                    AS ash_samples
FROM
    dba_hist_active_sess_history h
WHERE
    h.sample_time >= SYSTIMESTAMP - INTERVAL '48' HOUR
    AND h.sample_time <  SYSTIMESTAMP - INTERVAL '1'  HOUR
    AND h.dbid = (SELECT dbid FROM v$database)
    AND h.wait_class NOT IN ('Idle')
GROUP BY
    TRUNC(h.sample_time, 'HH24'),
    h.wait_class
UNION ALL
SELECT
    'REAL-TIME',
    TRUNC(ash.sample_time, 'HH24'),
    ash.wait_class,
    COUNT(*)
FROM
    v$active_session_history ash
WHERE
    ash.sample_time >= SYSTIMESTAMP - INTERVAL '1' HOUR
    AND ash.wait_class NOT IN ('Idle')
GROUP BY
    TRUNC(ash.sample_time, 'HH24'),
    ash.wait_class
ORDER BY
    sample_hour,
    ash_samples DESC;

Common Use Cases

Post-incident root cause analysis — When users report slowness at 2 AM that has since resolved, DASH is the only view that preserves the evidence; filter by sample_time around the incident window, sort by ash_samples per sql_id to find the top offenders, then drill into their execution plans in DBA_HIST_SQL_PLAN
Top SQL identification for AWR baseline — Aggregate ash_samples by sql_id over the AWR retention window to produce a heat map of database load; this is more granular than the top SQL from a standard AWR report because it preserves per-hour resolution
Wait event trending and regression detection — A sudden increase in ‘db file sequential read’ samples after a deployment often indicates a plan change or missing index; the per-hour wait class aggregation query makes regressions immediately visible
Historical blocking chain reconstruction — Lock contention incidents that resolved before a DBA could investigate are preserved in DASH with BLOCKING_SESSION and BLOCKING_SESSION_SERIAL# populated; reconstruct the full chain to identify the root blocker and the object being contended
PGA and temp space trend analysis — PGA_ALLOCATED and TEMP_SPACE_ALLOCATED columns allow trending of memory consumption over time; queries that periodically exceed PGA limits and spill to temp space are visible even after the sessions end
RAC node imbalance investigation — Filter by INSTANCE_NUMBER and aggregate by sample_hour to compare load distribution across RAC nodes; persistent imbalance suggests a misconfigured service, an affinity issue, or a hot-block that only one node is caching

V$ACTIVE_SESSION_HISTORY — The in-memory ASH ring buffer; same columns, real-time data, typically covering the last 30–60 minutes depending on activity level
V$SESSION — Current session state; use alongside DASH for real-time correlation with historical patterns
V$SQL — Join on SQL_ID to retrieve current SQL text and execution statistics for top SQL identified in DASH
V$SQL_PLAN — Current cached execution plans; for historical plans, join DBA_HIST_ACTIVE_SESS_HISTORY to DBA_HIST_SQL_PLAN on SQL_ID and SQL_PLAN_HASH_VALUE
DBA_HIST_SNAPSHOT — Maps SNAP_ID values to their begin and end timestamps; use to convert snap IDs to time ranges when analysing DASH by snapshot boundary
DBA_HIST_SQL_PLAN — Historical execution plans preserved in AWR; join on SQL_ID and SQL_PLAN_HASH_VALUE to retrieve the plan that was in effect during a historical incident

Version Notes

Oracle 10g R2: DBA_HIST_ACTIVE_SESS_HISTORY introduced as part of the Diagnostics Pack; core columns through BLOCKING_SESSION available
Oracle 11g: TOP_LEVEL_SQL_ID column added to distinguish nested PL/SQL execution from top-level calls; BLOCKING_SESSION_SERIAL# added for more reliable blocker identification
Oracle 12c: TEMP_SPACE_ALLOCATED and PGA_ALLOCATED columns added; CON_ID column added for Multitenant support; queries from the CDB root can aggregate DASH across all PDBs by filtering or grouping on CON_ID
Oracle 12c R2: SQL_PLAN_HASH_VALUE populated more consistently; adaptive plans resolved at first execution now produce stable hash values for DASH trending
Oracle 19c: No structural changes; Automatic SQL Plan Management integration means SQL_PLAN_HASH_VALUE in DASH reflects accepted baselines; useful for confirming when a plan baseline was pinned
Oracle 21c / 23ai: No structural column changes; In-Memory Hybrid Scans and Automatic In-Memory promotions may reduce I/O wait samples in DASH compared to earlier versions on the same workload; account for this when comparing trending data across version upgrades