ASH, Session-Level History, Blocking Chains, Time-Series Diagnosis, and Incident Reconstruction

Learning outcomes

A 30-second ServiceHub freeze is over before the DBA connects. Live V$SESSION no longer shows the blocking chain. Active Session History (ASH) samples active database sessions and preserves recent session state in memory; AWR periodically persists a subset into historical ASH. ASH can reconstruct “who was active/waiting on what,” but it is sampled evidence, not a transaction log.

1. ASH samples only active sessions

V$ACTIVE_SESSION_HISTORY contains sampled rows for sessions that were active—on CPU or waiting in a non-idle database call—at sample time. Very short calls can start and finish between samples and never appear. Long waits are naturally overrepresented because they remain active across many sample points.

2. Build the blocking incident

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE sh22_ash_case PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE sh22_ash_case (  case_id NUMBER PRIMARY KEY,  status_code VARCHAR2(12) NOT NULL,  amount NUMBER(10,2) NOT NULL);INSERT INTO sh22_ash_case VALUES(1,'OPEN',100);COMMIT;

BEGIN  DBMS_APPLICATION_INFO.SET_MODULE('SH22_ASH_LAB','BLOCKER');  DBMS_SESSION.SET_IDENTIFIER('ash-blocker');END;/UPDATE sh22_ash_caseSET amount=amount+25WHERE case_id=1;-- Leave transaction open for ~15 seconds.

BEGIN  DBMS_APPLICATION_INFO.SET_MODULE('SH22_ASH_LAB','WAITER');  DBMS_SESSION.SET_IDENTIFIER('ash-waiter');END;/UPDATE sh22_ash_caseSET status_code='HOLD'WHERE case_id=1;-- Waits on Session A.

3. First capture the ground truth live

SELECT  sid,  serial#,  module,  action,  client_identifier,  event,  wait_class,  blocking_session,  sql_idFROM v$sessionWHERE module='SH22_ASH_LAB'ORDER BY sid;

This live view is the most direct evidence while the incident is happening. Save the blocker/waiter SID/serial/SQL IDs and timestamps; then release the lock.

-- Session A:ROLLBACK;-- Session B after it resumes:ROLLBACK;

4. Reconstruct the recent timeline from V$ACTIVE_SESSION_HISTORY

SELECT  sample_time,  session_id,  session_serial#,  session_state,  event,  wait_class,  sql_id,  blocking_session,  blocking_session_serial#,  module,  action,  client_id,  con_idFROM v$active_session_historyWHERE sample_time >= SYSTIMESTAMP - INTERVAL '5' MINUTE  AND module='SH22_ASH_LAB'ORDER BY sample_time,session_id;

Expected samples for the waiter show WAITING plus enq: TX - row lock contention and the blocker SID. The blocker can appear ON CPU while executing or disappear from ASH while idle with the transaction open—another reason ASH must be combined with live/transaction context.

5. Aggregate samples carefully

SELECT  session_id,  module,  session_state,  NVL(event,'ON CPU') AS activity,  COUNT(*) AS ash_samplesFROM v$active_session_historyWHERE sample_time >= SYSTIMESTAMP - INTERVAL '5' MINUTE  AND module='SH22_ASH_LAB'GROUP BY  session_id,  module,  session_state,  NVL(event,'ON CPU')ORDER BY ash_samples DESC;

Sample counts approximate relative active time for sufficiently long/stationary activity; they are not exact execution counts or milliseconds. One five-second wait may produce roughly several samples, but scheduler timing and sampling behavior mean you should not convert a tiny sample set into precise percentages.

6. Historical ASH extends farther through AWR

DBA_HIST_ACTIVE_SESS_HISTORY stores historical ASH samples captured into AWR. It is also Diagnostics Pack. Use it for older incidents only when licensed/included and remember that AWR historical ASH is a subset of in-memory ASH, so the sampling/retention model changes.

SELECT  sample_time,  session_id,  session_serial#,  sql_id,  event,  blocking_session,  module,  actionFROM dba_hist_active_sess_historyWHERE sample_time BETWEEN :begin_time AND :end_time  AND module='SH22_ASH_LAB'ORDER BY sample_time,session_id;

7. Deliberately wrong: “ASH has no row for the 150 ms request, therefore it never ran”

ASH is a sample of active sessions, not a complete request ledger. A sub-second request can occur entirely between samples. For rare/short requests, application telemetry, unified audit (when appropriate), targeted SQL trace, service metrics and cursor statistics can be better evidence.

8. Blocking-chain query for real incidents

SELECT  blocking_session,  sql_id,  event,  COUNT(*) AS samplesFROM v$active_session_historyWHERE sample_time >= SYSTIMESTAMP - INTERVAL '15' MINUTE  AND blocking_session IS NOT NULLGROUP BY blocking_session,sql_id,eventORDER BY samples DESC;

This prioritizes repeatedly sampled blockers but can attribute many waiters to one blocker that is no longer itself sampled. Use SID/serial/session timeline and SQL/application identity to verify causality.

9. Free/no-pack fallback decision

10. Cleanup

DROP TABLE sh22_ash_case PURGE;

11. Production judgment

ASH is strongest for “what were active sessions doing over this short time window?” and blocker/SQL/service/module correlation. Filter by the tightest time window and contextual attributes first; broad ASH scans create noise and can be expensive. Preserve incident timelines externally.

On Free, Diagnostics Pack is included. On EE/EE-ES, ASH is extra-cost through Diagnostics Pack. CONTROL_MANAGEMENT_PACK_ACCESS is a functional gate, not license evidence. No restart or COMPATIBLE change is required. Lesson 5 finishes the escalation ladder with exact per-call tracing, SQL Monitor and ADDM.

Authoritative references

• V$ACTIVE_SESSION_HISTORY — sample columns/session state/blocker data
• Active Session History Statistics — ASH sampling/interpretation
• DBA_HIST_ACTIVE_SESS_HISTORY — historical ASH
• Licensing Information — ASH/AWR Diagnostics Pack boundary
• CONTROL_MANAGEMENT_PACK_ACCESS — functional pack gate