Query Historical ASH Beyond Memory with DBA_HIST_ACTIVE_SESS_HISTORY

Query Historical ASH Beyond Memory with DBA_HIST_ACTIVE_SESS_HISTORY

Purpose

Troubleshooting a performance spike after the fact is a different job than watching one happen live. By the time anyone opens a session the next morning to ask what was running during last night's batch window, the in-memory V$ACTIVE_SESSION_HISTORY buffer may already have cycled past that exact window. DBA_HIST_ACTIVE_SESS_HISTORY is where that same sampled activity keeps existing — Oracle's own reference documentation describes it as the view that "displays the history of the contents of the in-memory active session history of recent system activity," built from snapshots of the live view taken before the buffer discards them.

V$ACTIVE_SESSION_HISTORY samples active session activity once a second into a circular buffer that lives in the SGA — a session counts as active if it "was on the CPU or was waiting for an event that didn't belong to the Idle wait class" at the moment of the sample. Busy databases churn through that buffer fast: the more activity there is to sample, the shorter the window of history the buffer can hold before older rows are overwritten. A detailed technical breakdown of Active Session History states the retention mechanism for the historical version plainly: "one in ten samples are persisted to disk and made available using the DBA_HIST_ACTIVE_SESS_HISTORY view. So this is a sample of a sample." That ten-to-one ratio changes the arithmetic for anyone measuring elapsed time from row counts — a row count from the live view represents roughly one second each, while the same row count from the AWR-persisted view represents roughly ten seconds each.

This post covers pulling a baseline history window straight from DBA_HIST_ACTIVE_SESS_HISTORY, correcting the row-count-to-time math for its ten-second sampling interval, confirming on the live view whether a given sample has already been flushed to AWR, scoping a query to a specific range of AWR snapshots instead of a clock window, finding historical blocking sessions and the SQL driving the load, and restricting the search to one pluggable database in a multitenant environment. It also covers the ASH Report — the formatted alternative to querying either view directly.

Code

 1-- Query 1: baseline pull -- everything DBA_HIST_ACTIVE_SESS_HISTORY holds for the last 24 hours
 2SELECT sample_time, session_id, session_serial#, sql_id, event,
 3       wait_class, session_state
 4FROM   dba_hist_active_sess_history
 5WHERE  sample_time > SYSDATE - 1
 6ORDER  BY sample_time DESC;
 7
 8-- Query 2: correct the time math -- count*10, not count*1, against the AWR-persisted view
 9SELECT NVL(event, 'ON CPU') AS event,
10       COUNT(*) * 10 AS approx_seconds
11FROM   dba_hist_active_sess_history
12WHERE  sample_time > SYSDATE - 1
13GROUP  BY event
14ORDER  BY approx_seconds DESC;
15
16-- Query 3: on the live view, confirm whether a sample has been (or will be) flushed to AWR
17SELECT sample_time, session_id, event, is_awr_sample
18FROM   v$active_session_history
19WHERE  sample_time > SYSDATE - 10/(24*60)
20ORDER  BY sample_time DESC;
21
22-- Query 4: scope to a specific range of AWR snapshots instead of a clock-time window
23SELECT snap_id, sample_time, session_id, sql_id, event
24FROM   dba_hist_active_sess_history
25WHERE  snap_id BETWEEN 1450 AND 1453
26ORDER  BY snap_id, sample_time;
27
28-- Query 5: which SQL_IDs accumulated the most sampled time in that snapshot range
29SELECT sql_id, session_state,
30       COUNT(*) * 10 AS approx_seconds
31FROM   dba_hist_active_sess_history
32WHERE  snap_id BETWEEN 1450 AND 1453
33AND    sql_id IS NOT NULL
34GROUP  BY sql_id, session_state
35ORDER  BY approx_seconds DESC;
36
37-- Query 6: historical blocking sessions over the last week
38SELECT sample_time, session_id, blocking_session,
39       blocking_session_status, event
40FROM   dba_hist_active_sess_history
41WHERE  blocking_session IS NOT NULL
42AND    sample_time > SYSDATE - 7
43ORDER  BY sample_time DESC;
44
45-- Query 7: restrict historical ASH to one pluggable database's container
46SELECT sample_time, session_id, sql_id, event, con_id
47FROM   dba_hist_active_sess_history
48WHERE  con_id = 3
49AND    sample_time > SYSDATE - 1
50ORDER  BY sample_time DESC;
51
52-- Query 8: generate a formatted ASH Report instead of querying either view directly.
53-- Run from SQL*Plus, connected as a privileged user; prompts for report type,
54-- instance number, begin time, duration, and report name.
55@$ORACLE_HOME/rdbms/admin/ashrpt.sql

Code Breakdown

Query 1: the baseline pull

No filtering beyond a 24-hour window — this is the query to run first to confirm the historical view actually holds the window being investigated. SESSION_STATE reports either WAITING or ON CPU for each sampled row, and EVENT is populated only for the waiting rows.

Query 2: fixing the sampling-interval arithmetic

COUNT(*) alone undercounts elapsed time against DBA_HIST_ACTIVE_SESS_HISTORY by a factor of ten, because only one in ten of the underlying one-second samples survives into the AWR-persisted view. Multiplying the row count by 10 restores an approximate seconds-waited figure comparable to what the same query would produce against the live view with a multiplier of 1. Grouping by EVENT with NVL folding NULL into 'ON CPU' reproduces the same event-ranking approach documented for the live view, applied here to the historical one.

Query 3: checking IS_AWR_SAMPLE before assuming persistence

IS_AWR_SAMPLE exists only on V$ACTIVE_SESSION_HISTORY, where it "indicates whether this sample has been flushed or will be flushed to the Automatic Workload Repository (DBA_HIST_ACTIVE_SESS_HISTORY) (Y) or not (N)." A row most recently sampled live may show Y before the background flush has actually happened, which is why a query against DBA_HIST_ACTIVE_SESS_HISTORY for the last few minutes can legitimately come back short — the flush simply hasn't run yet.

Query 4: scoping by SNAP_ID instead of clock time

SNAP_ID, DBID, and INSTANCE_NUMBER are the three columns the reference documentation calls out as not sharing their interpretation with the live view — they exist specifically because the historical view is organized around AWR snapshots, not just sample timestamps. Filtering on a SNAP_ID range is the more precise way to bound a query to "the window this particular AWR snapshot pair covers," rather than guessing the clock boundaries. One documented caveat worth flagging here even though it isn't shown in the query above: Oracle's own reference note for this view states that a join back to snapshot metadata should use the dedicated DBA_HIST_ASH_SNAPSHOT view, not the general DBA_HIST_SNAPSHOT view used elsewhere in AWR.

Query 5: finding which SQL drove the load

Grouping by SQL_ID and SESSION_STATE with the same COUNT(*) * 10 approximation surfaces which statements accounted for the most sampled activity across a known snapshot range — useful once Query 4 has already narrowed the investigation to a specific AWR window.

Query 6: blocking sessions across history

In DBA_HIST_ACTIVE_SESS_HISTORY, Oracle 19c defines BLOCKING_SESSION as the "Session identifier of the blocking session. Populated only when the session was waiting for enqueues or a "buffer busy" wait." A blocked session waiting on some other wait class will show no value here even though it was genuinely waiting on another session. The in-memory view is stricter still: the V$ACTIVE_SESSION_HISTORY reference says the column is "Populated only if the blocker is on the same instance," so on RAC a blocker on another node leaves it empty. BLOCKING_SESSION_STATUS carries its own status label alongside it (VALID, NO HOLDER, and other documented values), which is worth checking before treating a populated BLOCKING_SESSION column as a confirmed, currently-valid blocker.

Query 7: CON_ID scoping in a multitenant database

CON_ID identifies which container a sampled row belongs to, the same per-row container scoping used throughout the multitenant dictionary views — useful for isolating one pluggable database's historical activity when several PDBs' sessions are all landing in the same AWR repository.

Query 8: the ASH Report as a packaged alternative

Rather than writing ad hoc queries against either view, ashrpt.sql (found under $ORACLE_HOME/rdbms/admin) produces a formatted report interactively. It prompts for a report type (html or text), an instance number (all or a specific number — on a single-instance database this defaults to 1), a begin time (an explicit date or an offset from the current time, defaulting to -15 minutes), a duration in minutes (defaulting to the gap between the begin time and the current time), and a report name (a default is supplied). Underneath, the script calls one of several table functions from the DBMS_WORKLOAD_REPOSITORY package — ASH_REPORT_TEXT, ASH_REPORT_HTML, ASH_GLOBAL_REPORT_TEXT, or ASH_GLOBAL_REPORT_HTML — depending on the options chosen.

Key Points

• DBA_HIST_ACTIVE_SESS_HISTORY is a sample of a sample. Only one in ten of the live view's once-per-second samples survives into the AWR-persisted view.
• Row counts, not WAIT_TIME or TIME_WAITED, are the correct time measure — and the multiplier changes depending on which view is being queried: roughly 1 second per row live, roughly 10 seconds per row historical.
• IS_AWR_SAMPLE is a live-only column. It exists on V$ACTIVE_SESSION_HISTORY to flag whether a sample has or will be flushed to AWR; DBA_HIST_ACTIVE_SESS_HISTORY doesn't need the equivalent, since every row there already made that cut.
• Most column interpretations carry over between the two views, with three named exceptions. Oracle's reference documentation says to read DBA_HIST_ACTIVE_SESS_HISTORY's columns the same way as V$ACTIVE_SESSION_HISTORY's, "except SNAP_ID, DBID, and INSTANCE_NUMBER."
• A join to snapshot metadata has a specific documented partner. The reference note calls for DBA_HIST_ASH_SNAPSHOT rather than the general DBA_HIST_SNAPSHOT view when resolving a snapshot's clock-time boundaries.
• BLOCKING_SESSION is conditionally populated. It is only set "when the session was waiting for enqueues or a 'buffer busy' wait" — not for every wait class a blocked session might be stuck on.

Insights and Best Practices

Reach for the live view first, the historical view second

If the window under investigation is recent enough that it might still be sitting in the SGA's circular buffer, V$ACTIVE_SESSION_HISTORY is the faster and more granular read — one row per second, versus one row per ten in the AWR-persisted copy. DBA_HIST_ACTIVE_SESS_HISTORY becomes the only option once the live buffer has already cycled past the window, which on a busy database can happen within hours rather than days.

Don't sum WAIT_TIME or TIME_WAITED to estimate elapsed time

Because ASH is sample-based rather than event-based, summing a wait's recorded time across every sample it appears in produces a falsely high total — a single multi-second wait gets counted once per sample it was caught in, not once overall. Counting rows per event (and multiplying by the sampling interval) is the documented workaround, and it's the same technique whether the query targets the live view or the historical one.

Use the built-in reporting options before hand-rolling a dashboard

Beyond raw queries against either view, Oracle ships several ready-made ways into the same data: Enterprise Manager's performance pages are built directly on ASH, SQL Developer 4 and later expose an ASH Reports Viewer under the DBA pane's Performance node, and the third-party ASH Viewer tool gives a graphical view that supports Oracle 8i onward — connecting in "Standard" mode to approximate ASH behavior without a license, or in "Enterprise" mode to use real ASH data where the license is in place.

Confirm licensing before building a monitoring program around this

Active Session History, including the AWR-persisted history this post queries, was introduced as part of the Diagnostics and Tuning Pack — "a paid option on top of Oracle Database Enterprise Edition." A site standardizing a monitoring program on DBA_HIST_ACTIVE_SESS_HISTORY should confirm that licensing is actually in place for the environment before depending on it.

When to Use This

• Investigating a spike from last night, or further back, after the live buffer has already rolled past that window.
• Confirming whether a specific live-view row has actually made it into AWR before assuming it will still be queryable tomorrow.
• Comparing which SQL_IDs or session states accumulated the most sampled activity across a known, bounded AWR snapshot range.
• Reviewing historical blocking chains across a week or more, rather than only the live-only picture V$SESSION offers.
• Isolating one pluggable database's historical session activity in a multitenant environment with several PDBs sharing one AWR repository.
• Producing a shareable, formatted report instead of handing someone raw query output.

Troubleshooting Common Issues

A query against DBA_HIST_ACTIVE_SESS_HISTORY for the last few minutes returns nothing, even though activity clearly happened. Check V$ACTIVE_SESSION_HISTORY's IS_AWR_SAMPLE column for the same window first — a sample can be flagged as destined for AWR without the background flush having actually written it yet, which means the historical view genuinely has no row there yet.

Elapsed-time estimates from DBA_HIST_ACTIVE_SESS_HISTORY look too low compared to what actually happened. This is the ten-to-one sampling ratio showing up in the arithmetic — multiply the row count by 10, not 1, when estimating seconds from the historical view.

Summing WAIT_TIME or TIME_WAITED across many rows produces an implausibly large total. Sample-based accounting means the same ongoing wait gets reflected in every sample it was caught in. Count rows per event instead of summing the time columns.

A query joining DBA_HIST_ACTIVE_SESS_HISTORY back to snapshot metadata returns unexpected results. Confirm the join target is DBA_HIST_ASH_SNAPSHOT, not the general DBA_HIST_SNAPSHOT view — Oracle's reference documentation calls this out specifically for this view.

BLOCKING_SESSION is NULL on a row that was clearly blocked. The column is only populated for enqueue waits or a "buffer busy" wait; a session blocked on a different wait class will not carry a value here even though it was genuinely waiting on another session.

References

• DBA_HIST_ACTIVE_SESS_HISTORY — Database Reference 19c - canonical column reference, including the SNAP_ID/DBID/INSTANCE_NUMBER interpretation note and the DBA_HIST_ASH_SNAPSHOT join guidance
• V$ACTIVE_SESSION_HISTORY — Database Reference 19c - the live view's column reference, including IS_AWR_SAMPLE and the active-session definition
• Active Session History (ASH) — oracle-base.com - Tim Hall's walkthrough covering the sampling mechanism, the one-in-ten historical retention ratio, the row-count-versus-WAIT_TIME arithmetic, the ASH Report script, and the Diagnostics and Tuning Pack licensing