Oracle ASH cheat sheet: Active Session History queries for top SQL, waits, blockers, PGA and TEMP

#When to use ASH instead of AWR

AWR tells you what an hour looked like on average. ASH tells you what each active session was doing every second. Reach for it when the complaint is "between 10:12 and 10:20 checkout was slow" or "this one batch job took twice as long last night".

Warning

ASH, AWR and the DBA_HIST_* views are part of the Oracle Diagnostics Pack, a separately licensed option for Enterprise Edition. Check your licence before you query them. CONTROL_MANAGEMENT_PACK_ACCESS shows what is enabled.

#How the data works

So COUNT(*) in V$ is roughly seconds of DB time, and COUNT(*) * 10 in DBA_HIST is roughly seconds. Only active sessions are sampled: on CPU, or waiting on a non-idle event. An idle connection holding a lock appears only as the BLOCKING_SESSION of someone else's sample.

Check how far back the in-memory buffer goes:

SELECT MIN(sample_time) oldest, MAX(sample_time) newest
FROM v$active_session_history;

#Set the window once

Every query below uses these bind variables. In SQL*Plus:

VARIABLE t1 VARCHAR2(20)
VARIABLE t2 VARCHAR2(20)
EXEC :t1 := '2026-11-09 10:10'; :t2 := '2026-11-09 10:25';
-- predicate used below:
-- sample_time BETWEEN TO_TIMESTAMP(:t1,'YYYY-MM-DD HH24:MI') AND TO_TIMESTAMP(:t2,'YYYY-MM-DD HH24:MI')

On a multitenant database, run from the PDB to see only that PDB, or filter on con_id from the root.

#Load profile: what was the database doing, minute by minute?

SELECT TRUNC(sample_time, 'MI') AS minute,
       ROUND(COUNT(*) / 60, 1) AS avg_active_sessions,
       SUM(CASE WHEN session_state = 'ON CPU' THEN 1 ELSE 0 END) AS cpu,
       SUM(CASE WHEN wait_class = 'User I/O' THEN 1 ELSE 0 END) AS user_io,
       SUM(CASE WHEN wait_class = 'Concurrency' THEN 1 ELSE 0 END) AS concurrency,
       SUM(CASE WHEN wait_class = 'Application' THEN 1 ELSE 0 END) AS application,
       SUM(CASE WHEN wait_class = 'Cluster' THEN 1 ELSE 0 END) AS cluster_w
FROM v$active_session_history
WHERE sample_time BETWEEN TO_TIMESTAMP(:t1,'YYYY-MM-DD HH24:MI') AND TO_TIMESTAMP(:t2,'YYYY-MM-DD HH24:MI')
GROUP BY TRUNC(sample_time, 'MI')
ORDER BY 1;

Compare avg_active_sessions with the CPU count (SELECT value FROM v$parameter WHERE name = 'cpu_count'). If CPU samples alone exceed the core count, sessions were queueing for CPU, and the CPU figures include run-queue time.

#Top events

SELECT NVL(event, 'ON CPU') AS event, wait_class, COUNT(*) AS samples,
       ROUND(100 * RATIO_TO_REPORT(COUNT(*)) OVER (), 1) AS pct
FROM v$active_session_history
WHERE sample_time BETWEEN TO_TIMESTAMP(:t1,'YYYY-MM-DD HH24:MI') AND TO_TIMESTAMP(:t2,'YYYY-MM-DD HH24:MI')
GROUP BY event, wait_class
ORDER BY samples DESC
FETCH FIRST 15 ROWS ONLY;

#Top SQL, with the plan and what it waited on

SELECT sql_id, sql_plan_hash_value AS phv,
       COUNT(*) AS samples,
       SUM(CASE WHEN session_state = 'ON CPU' THEN 1 ELSE 0 END) AS cpu,
       MAX(event) KEEP (DENSE_RANK LAST ORDER BY cnt_evt) AS top_wait,
       COUNT(DISTINCT sql_exec_id) AS executions_seen
FROM (SELECT a.*, COUNT(*) OVER (PARTITION BY sql_id, event) AS cnt_evt
      FROM v$active_session_history a
      WHERE sample_time BETWEEN TO_TIMESTAMP(:t1,'YYYY-MM-DD HH24:MI') AND TO_TIMESTAMP(:t2,'YYYY-MM-DD HH24:MI')
        AND sql_id IS NOT NULL)
GROUP BY sql_id, sql_plan_hash_value
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;

Two rows for one sql_id with different phv is a plan change. Get the SQL text and the plan:

SELECT sql_fulltext FROM v$sql WHERE sql_id = '&sql_id' AND ROWNUM = 1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST +PEEKED_BINDS'));
-- From AWR if it has aged out of the shared pool:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_WORKLOAD_REPOSITORY('&sql_id', &phv));

DISPLAY_WORKLOAD_REPOSITORY exists in 19c and later. On older releases use DBMS_XPLAN.DISPLAY_AWR.

#Where in the plan was the time spent?

SELECT sql_plan_line_id, sql_plan_operation, sql_plan_options,
       NVL(event, 'ON CPU') AS event, COUNT(*) AS samples
FROM v$active_session_history
WHERE sql_id = '&sql_id'
  AND sample_time BETWEEN TO_TIMESTAMP(:t1,'YYYY-MM-DD HH24:MI') AND TO_TIMESTAMP(:t2,'YYYY-MM-DD HH24:MI')
GROUP BY sql_plan_line_id, sql_plan_operation, sql_plan_options, event
ORDER BY samples DESC;

This is often faster than reading a SQL Monitor report, and it works for queries that SQL Monitor didn't capture.

#PL/SQL callers

When the top sql_id is a small statement called millions of times, find the calling procedure with top_level_sql_id, plsql_entry_object_id and plsql_object_id (join to DBA_PROCEDURES on object_id and subprogram_id).

#Blocking chains

SELECT blocking_session, blocking_session_serial#, blocking_inst_id,
       event, COUNT(*) AS samples_blocked,
       COUNT(DISTINCT session_id) AS sessions_blocked
FROM v$active_session_history
WHERE sample_time BETWEEN TO_TIMESTAMP(:t1,'YYYY-MM-DD HH24:MI') AND TO_TIMESTAMP(:t2,'YYYY-MM-DD HH24:MI')
  AND blocking_session IS NOT NULL
GROUP BY blocking_session, blocking_session_serial#, blocking_inst_id, event
ORDER BY samples_blocked DESC;

Then see what the blocker itself was doing. If it has no samples at all, it was idle: an application holding an open transaction.

SELECT sample_time, session_state, event, sql_id, program, module, machine
FROM v$active_session_history
WHERE session_id = &blocker_sid AND session_serial# = &blocker_serial
  AND sample_time BETWEEN TO_TIMESTAMP(:t1,'YYYY-MM-DD HH24:MI') AND TO_TIMESTAMP(:t2,'YYYY-MM-DD HH24:MI')
ORDER BY sample_time;

#Hot objects and blocks

For db file sequential read, buffer busy waits, gc buffer busy acquire and row lock waits, current_obj# points at the segment:

SELECT o.owner, o.object_name, o.subobject_name, o.object_type,
       a.event, COUNT(*) AS samples
FROM v$active_session_history a
JOIN dba_objects o ON o.object_id = a.current_obj#
WHERE a.sample_time BETWEEN TO_TIMESTAMP(:t1,'YYYY-MM-DD HH24:MI') AND TO_TIMESTAMP(:t2,'YYYY-MM-DD HH24:MI')
  AND a.wait_class IN ('User I/O', 'Concurrency', 'Cluster', 'Application')
GROUP BY o.owner, o.object_name, o.subobject_name, o.object_type, a.event
ORDER BY samples DESC
FETCH FIRST 15 ROWS ONLY;

current_obj# is only meaningful for those wait classes. For CPU samples it can hold a stale value from the last wait.

#PGA and TEMP per session

SELECT session_id, session_serial#, sql_id,
       ROUND(MAX(pga_allocated) / 1048576) AS max_pga_mb,
       ROUND(MAX(temp_space_allocated) / 1048576) AS max_temp_mb
FROM v$active_session_history
WHERE sample_time BETWEEN TO_TIMESTAMP(:t1,'YYYY-MM-DD HH24:MI') AND TO_TIMESTAMP(:t2,'YYYY-MM-DD HH24:MI')
GROUP BY session_id, session_serial#, sql_id
ORDER BY max_temp_mb DESC NULLS LAST
FETCH FIRST 10 ROWS ONLY;

This answers "who filled TEMP at 03:00?" after the session has gone, which V$TEMPSEG_USAGE can't.

#Parsing storms

SELECT in_hard_parse, in_parse, COUNT(*) AS samples
FROM v$active_session_history
WHERE sample_time BETWEEN TO_TIMESTAMP(:t1,'YYYY-MM-DD HH24:MI') AND TO_TIMESTAMP(:t2,'YYYY-MM-DD HH24:MI')
GROUP BY in_hard_parse, in_parse;

A large share of in_hard_parse = 'Y' points at literals instead of binds. Group by force_matching_signature to find the statements that differ only in literals.

#Who and where

SELECT program, module, action, machine, COUNT(*) AS samples
FROM v$active_session_history
WHERE sample_time BETWEEN TO_TIMESTAMP(:t1,'YYYY-MM-DD HH24:MI') AND TO_TIMESTAMP(:t2,'YYYY-MM-DD HH24:MI')
GROUP BY program, module, action, machine
ORDER BY samples DESC
FETCH FIRST 15 ROWS ONLY;

Ask application teams to set MODULE and ACTION with DBMS_APPLICATION_INFO. It turns this query from "JDBC Thin Client" into a list of features.

#Last night: the same queries on DBA_HIST

Swap the view, filter on dbid and snap_id for partition pruning, and multiply counts by 10:

SELECT sql_id, COUNT(*) * 10 AS approx_db_time_s
FROM dba_hist_active_sess_history
WHERE dbid = (SELECT dbid FROM v$database)
  AND snap_id BETWEEN &begin_snap AND &end_snap
  AND sample_time BETWEEN TO_TIMESTAMP(:t1,'YYYY-MM-DD HH24:MI') AND TO_TIMESTAMP(:t2,'YYYY-MM-DD HH24:MI')
GROUP BY sql_id
ORDER BY 2 DESC
FETCH FIRST 10 ROWS ONLY;

Find snapshot IDs in DBA_HIST_SNAPSHOT. Short events (a 20-second spike) can be missed entirely in the 1-in-10 samples, so treat DBA_HIST as a trend, not a precise count.

#Built-in reports

-- Interactive: prompts for instance, time range and output format
@?/rdbms/admin/ashrpt.sql
-- RAC-wide
@?/rdbms/admin/ashrpti.sql

#Mistakes to avoid

#Prevent it next time

• Keep AWR retention long enough to cover your incident review cycle (for example, 30 days) with DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS, and check SYSAUX capacity first.
• Save the queries above as a script in your DBA toolkit with the bind variables at the top.
• Ask for MODULE/ACTION instrumentation in every new application.
• After each incident, export the ASH rows for the window to a table so the evidence outlives AWR retention.