V$ACTIVE_SESSION_HISTORY - ASH Queries for Performance Troubleshooting

V$ACTIVE_SESSION_HISTORY

Overview

V$ACTIVE_SESSION_HISTORY (ASH) contains a rolling in-memory buffer of active session samples collected every second by the Active Session History background process. Each row represents one session that was active (on CPU or waiting) at the moment of sampling. This view is the foundation of Oracle’s workload repository diagnostics and is invaluable for identifying transient performance problems that have already cleared by the time an alert fires.

View Type: Dynamic Performance View Available Since: Oracle 10g Required Privileges: SELECT on V_$ACTIVE_SESSION_HISTORY or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
SAMPLE_ID	NUMBER	Unique identifier for the sample
SAMPLE_TIME	TIMESTAMP(3)	Timestamp when the sample was taken
SESSION_ID	NUMBER	SID of the sampled session
SESSION_SERIAL#	NUMBER	Serial number of the sampled session
USER_ID	NUMBER	Oracle user ID (join to DBA_USERS)
SQL_ID	VARCHAR2(13)	SQL identifier of the statement being executed
SQL_EXEC_ID	NUMBER	Execution ID — distinguishes multiple executions of the same SQL_ID
TOP_LEVEL_SQL_ID	VARCHAR2(13)	SQL_ID of the top-level call (useful in PL/SQL workloads)
SQL_PLAN_HASH_VALUE	NUMBER	Plan hash value at the time of the sample
EVENT	VARCHAR2(64)	Wait event name (NULL when on CPU)
WAIT_CLASS	VARCHAR2(64)	Wait class (e.g., User I/O, Concurrency, Idle)
SESSION_STATE	VARCHAR2(7)	ON CPU or WAITING
BLOCKING_SESSION	NUMBER	SID of session this session is blocked by
BLOCKING_SESSION_SERIAL#	NUMBER	Serial number of the blocking session
CURRENT_OBJ#	NUMBER	Object ID of the segment being accessed
CURRENT_FILE#	NUMBER	Data file number being accessed
CURRENT_BLOCK#	NUMBER	Block number being accessed
P1	NUMBER	First wait event parameter
P2	NUMBER	Second wait event parameter
P3	NUMBER	Third wait event parameter
IN_PARSE	VARCHAR2(1)	Y if session was parsing at sample time
IN_HARD_PARSE	VARCHAR2(1)	Y if session was hard parsing at sample time
IN_SQL_EXECUTION	VARCHAR2(1)	Y if session was executing SQL
PGA_ALLOCATED	NUMBER	PGA memory allocated to this session (bytes)
TEMP_SPACE_ALLOCATED	NUMBER	Temp tablespace allocated to this session (bytes)
PROGRAM	VARCHAR2(48)	Client program name
MODULE	VARCHAR2(48)	Application module name (set via DBMS_APPLICATION_INFO)
ACTION	VARCHAR2(32)	Application action name
MACHINE	VARCHAR2(64)	Client machine name

Essential Queries

Basic Usage

-- Active session activity for the last 30 minutes
SELECT
    TO_CHAR(TRUNC(ash.sample_time, 'MI'), 'HH24:MI')  AS sample_minute,
    ash.session_state,
    ash.event,
    ash.wait_class,
    COUNT(*)                                            AS sample_count
FROM
    v$active_session_history ash
WHERE
    ash.sample_time >= SYSDATE - (30/1440)
    AND ash.session_state != 'IDLE'
GROUP BY
    TRUNC(ash.sample_time, 'MI'),
    ash.session_state,
    ash.event,
    ash.wait_class
ORDER BY
    sample_minute,
    sample_count DESC;

Monitoring Query

-- Top SQL by DB time in the last hour (equivalent to Top SQL in AWR)
SELECT
    ash.sql_id,
    COUNT(*)                                        AS db_time_secs,
    ROUND(COUNT(*) / SUM(COUNT(*)) OVER () * 100, 1) AS pct_db_time,
    SUM(CASE WHEN ash.session_state = 'ON CPU'  THEN 1 ELSE 0 END) AS cpu_secs,
    SUM(CASE WHEN ash.wait_class = 'User I/O'  THEN 1 ELSE 0 END) AS io_secs,
    SUM(CASE WHEN ash.wait_class = 'Concurrency' THEN 1 ELSE 0 END) AS concurrency_secs,
    SUBSTR(MAX(sq.sql_text), 1, 80)                AS sql_text_snippet
FROM
    v$active_session_history ash
    LEFT JOIN v$sql sq ON sq.sql_id = ash.sql_id
                      AND sq.child_number = 0
WHERE
    ash.sample_time >= SYSDATE - (60/1440)
    AND ash.sql_id IS NOT NULL
GROUP BY
    ash.sql_id
ORDER BY
    db_time_secs DESC
FETCH FIRST 20 ROWS ONLY;

Combined with Other Views

-- Top wait events with object-level detail (last hour)
SELECT
    ash.event,
    ash.wait_class,
    obj.object_name,
    obj.object_type,
    obj.owner,
    COUNT(*)                                         AS waits,
    ROUND(COUNT(*) / SUM(COUNT(*)) OVER () * 100, 1) AS pct_total
FROM
    v$active_session_history ash
    LEFT JOIN dba_objects obj ON obj.object_id = ash.current_obj#
WHERE
    ash.sample_time  >= SYSDATE - (60/1440)
    AND ash.session_state = 'WAITING'
    AND ash.wait_class   != 'Idle'
GROUP BY
    ash.event,
    ash.wait_class,
    obj.object_name,
    obj.object_type,
    obj.owner
ORDER BY
    waits DESC
FETCH FIRST 25 ROWS ONLY;

Advanced Analysis

-- Blocking session history — identify chronic lock holders in the last 2 hours
SELECT
    ash.blocking_session                            AS blocker_sid,
    ash.blocking_session_serial#                   AS blocker_serial,
    bu.username                                     AS blocker_user,
    ash.session_id                                  AS waiter_sid,
    wu.username                                     AS waiter_user,
    ash.sql_id                                      AS waiter_sql_id,
    ash.event                                       AS wait_event,
    COUNT(*)                                        AS seconds_blocked,
    MIN(ash.sample_time)                            AS block_start,
    MAX(ash.sample_time)                            AS block_end
FROM
    v$active_session_history ash
    LEFT JOIN dba_users wu ON wu.user_id = ash.user_id
    LEFT JOIN v$session   bs ON bs.sid     = ash.blocking_session
                             AND bs.serial# = ash.blocking_session_serial#
    LEFT JOIN dba_users bu ON bu.username  = bs.username
WHERE
    ash.sample_time       >= SYSDATE - (120/1440)
    AND ash.blocking_session IS NOT NULL
GROUP BY
    ash.blocking_session,
    ash.blocking_session_serial#,
    bu.username,
    ash.session_id,
    wu.username,
    ash.sql_id,
    ash.event
ORDER BY
    seconds_blocked DESC
FETCH FIRST 20 ROWS ONLY;

-- PGA and temp space trending by SQL (last hour) — catch memory-hungry queries
SELECT
    ash.sql_id,
    COUNT(*)                                                   AS samples,
    ROUND(MAX(ash.pga_allocated)      / 1048576, 1)           AS max_pga_mb,
    ROUND(AVG(ash.pga_allocated)      / 1048576, 1)           AS avg_pga_mb,
    ROUND(MAX(ash.temp_space_allocated) / 1048576, 1)         AS max_temp_mb,
    ROUND(AVG(ash.temp_space_allocated) / 1048576, 1)         AS avg_temp_mb,
    SUM(CASE WHEN ash.in_hard_parse = 'Y' THEN 1 ELSE 0 END)  AS hard_parse_samples
FROM
    v$active_session_history ash
WHERE
    ash.sample_time >= SYSDATE - (60/1440)
    AND ash.sql_id IS NOT NULL
GROUP BY
    ash.sql_id
HAVING
    MAX(ash.pga_allocated) > 52428800   -- sessions using more than 50 MB PGA
    OR MAX(ash.temp_space_allocated) > 52428800
ORDER BY
    max_pga_mb DESC
FETCH FIRST 15 ROWS ONLY;

Common Use Cases

• Post-incident triage — ASH data covers the last 30–60 minutes in memory; query it immediately after a slowdown before the buffer wraps, or rely on DBA_HIST_ACTIVE_SESS_HISTORY for older data.
• Identifying transient contention — Blocking locks, hot blocks, and latch contention that clear before AWR snapshots are captured are visible at the per-second granularity of ASH.
• Top SQL analysis without AWR license — For licensed environments, V$ACTIVE_SESSION_HISTORY provides SQL-level DB time breakdowns in real time.
• Parse pressure detection — IN_HARD_PARSE = ‘Y’ samples expose applications generating excessive hard parses before the parse count becomes obvious in V$SYSSTAT.
• Module and action profiling — Aggregate by MODULE and ACTION to measure the Oracle DB time cost of specific application components without code instrumentation.
• PGA and temp runaway detection — PGA_ALLOCATED and TEMP_SPACE_ALLOCATED enable identifying sort- or hash-heavy SQL before it causes ORA-04036 or ORA-01652.

Related Views

• V$SESSION — Join on SESSION_ID / SESSION_SERIAL# to get current session details for active samples
• V$SQL — Join on SQL_ID to retrieve full SQL text and execution statistics
• V$LOCK — Correlate BLOCKING_SESSION with V$LOCK to understand lock types during contention events
• V$PROCESS — Join through V$SESSION to get the OS process ID for sampled sessions
• V$SQL_MONITOR — For statements currently being monitored, ASH SQL_EXEC_ID links directly to V$SQL_MONITOR

Version Notes

• Oracle 10g: View introduced as part of the Automatic Workload Repository (AWR) diagnostics pack. Requires the Diagnostics Pack license for production use.
• Oracle 11g: Added TOP_LEVEL_SQL_ID to support better PL/SQL workload attribution; added IN_PARSE and IN_HARD_PARSE flags.
• Oracle 12c: PGA_ALLOCATED and TEMP_SPACE_ALLOCATED columns added, enabling memory pressure analysis directly from ASH. SQL_EXEC_ID added to correlate with V$SQL_MONITOR.
• Oracle 19c: ASH sampling rate can be tuned; improvements to multitenant (CDB/PDB) visibility — CON_ID column added to identify which PDB the session belongs to.
• Oracle 21c / 23ai: Enhanced blockchain and graph workload attributes captured. In 23ai, ASH data is also surfaced through SQL Analysis Report for autonomous workload tuning.