Oracle ASH and AWR: a field guide for the rest of us

Oracle gives you two of the best performance views any database has ever shipped — Active Session History (ASH) and Automatic Workload Repository (AWR). The catch: both live behind the Diagnostics Pack, which is licensed separately. This guide is about getting 90% of the value without paying for either.

ASH: a live tail of every session

ASH is the cheapest powerful telemetry in Oracle. Every second, the kernel samplesv$session and writes one row per active session into a circular in-memory buffer (v$active_session_history). It costs almost nothing — sampling is on the SGA path — and it captures things the SQL trace can’t: blocked sessions, wait events, the SQL text and module they were running.

• Sample rate: 1 Hz from v$session, only the rows where the session was on CPU or in a wait.
• Retention: usually a few hours in memory before AWR persists samples to dba_hist_active_sess_history.
• What it captures: session_id, sql_id, module, action, event, wait_class, blocking_session — enough to reconstruct any incident.

AWR: snapshots over time

AWR is the long-form version. Every hour, Oracle takes a snapshot of every dynamic performance view and writes a delta into the SYSAUX tablespace. The famous awrrpt.sql report is just a formatted diff between two snapshots.

The most useful AWR sections are Top Timed Events (where time was spent),SQL Ordered by Elapsed Time (which signature burned it), and Instance Activity Stats (raw counters per second). If you only read three sections, those.

Without the Diagnostics Pack

Oracle’s licensing splits ASH and AWR behind the Diagnostics Pack. If you don’t have it, querying v$active_session_history is technically a license violation. There are two open alternatives:

• Statspack: the pre-AWR ancestor. Free, ships with Oracle, lacks ASH but covers most of the AWR snapshot story. Install with @?/rdbms/admin/spcreate.sql, schedule snapshots, generate reports with spreport.sql.
• S-ASH (open ASH simulation): a community project that polls v$session at 1 Hz from outside the database, writes to a separate schema, and approximates ASH closely enough for incident analysis.
• Direct sampling from a sidecar: the path the Obsfly Oracle agent takes — sample gv$session at 1 Hz over a regular SELECT (which is licensed), ship the rows out for storage and analysis. Same telemetry, different storage.

Queries you should run today

These three queries cover most incident triage on an Oracle box. The first works in Statspack; the second and third need ASH (or a sidecar equivalent).

-- 1. Top wait events in the last hour (Statspack OK)
SELECT event, total_waits, time_waited_micro/1e6 AS time_waited_s
FROM   v$system_event
WHERE  wait_class != 'Idle'
ORDER  BY time_waited_micro DESC
FETCH  FIRST 10 ROWS ONLY;

-- 2. Top SQL by ASH-sampled time (Diag Pack)
SELECT sql_id, COUNT(*) AS samples, COUNT(DISTINCT session_id) AS sessions
FROM   v$active_session_history
WHERE  sample_time > SYSTIMESTAMP - INTERVAL '1' HOUR
GROUP  BY sql_id
ORDER  BY samples DESC
FETCH  FIRST 10 ROWS ONLY;

-- 3. Blocked sessions right now (no Diag Pack needed)
SELECT s.sid, s.serial#, s.username, s.event, s.blocking_session,
       s.sql_id, s.last_call_et AS seconds_in_event
FROM   v$session s
WHERE  s.blocking_session IS NOT NULL
ORDER  BY s.last_call_et DESC;

FAQ