Oracle Active Session History (ASH) :

The Sampling Model for Transient Performance Problems :

Introduction :

AWR and ADDM operate on snapshots. By default, AWR captures a snapshot once per hour. ADDM analyzes the interval between snapshots and reports findings with an impact percentage. This model works well for problems that persist long enough to leave a statistical footprint across an entire snapshot interval.

It fails completely for problems that last seconds or minutes.

A blocking chain that stalls the application for 90 seconds, a latch contention spike triggered by a specific batch job, a log file sync storm during a commit burst, these events can occur entirely between two AWR snapshots. ADDM may see the aftermath in aggregate statistics, but it cannot isolate the cause because the cause is buried inside a one-hour average. The transient problem is statistically invisible.

ASH solves this by changing the sampling model entirely. Instead of aggregating over an hour, ASH samples active sessions once per second and records what each session was doing at that instant. A problem that lasts 90 seconds produces roughly 90 samples per active session involved. That is enough to reconstruct the incident with second-level precision, even if ADDM never reports it .

This is the theoretical distinction that separates ASH from everything covered so far: AWR measures intervals, ASH measures moments.

Theoretical Foundation :

The Sampling Model :

ASH does not trace every session continuously. It samples only active sessions, sessions that are either on CPU or waiting on a non-idle event. Idle sessions are filtered out. Every second, the database records one row per active session containing the session identifier, the wait event (or “ON CPU”), the SQL ID, the object being waited on, and the blocking session if applicable .

The number of samples is therefore proportional to active session seconds, not wall-clock time. If 10 sessions are active for 60 seconds, ASH produces approximately 600 samples. If 100 sessions are active for the same period, it produces 6,000. This makes ASH naturally focused on workload intensity: the more sessions are doing work (or waiting), the more data ASH collects about them.

The critical implication is that ASH is a statistical sample, not a complete record. A wait event that lasts 0.3 seconds may be missed entirely if the sampling instant falls outside that window. For problems that last multiple seconds, the sample count is high enough that the proportions are accurate. For sub-second events, ASH cannot be trusted to capture every occurrence .

What ASH Captures That AWR Cannot :

AWR stores instance-level statistics: total DB time, total CPU, total physical reads, and so on, aggregated across the snapshot interval. It does not store per-session, per-second detail. If a single session consumed 80% of DB time for 30 seconds in the middle of an hour, AWR records the hour’s totals. The 30-second spike is diluted into the hour’s average.

ASH stores session-level samples: for every second, which session, doing what, waiting on which resource. The 30-second spike appears as 30 consecutive samples for that session, all pointing to the same wait event or SQL statement. The incident is directly visible without inference .

This is why ASH is the correct tool for transient problems and AWR Compare Periods (covered earlier) is the correct tool for gradual degradation. They answer different questions.

ASH and the Response Time Model :

Oracle’s performance diagnosis rests on the response time model:

Response Time = Service Time + Wait Time

Service Time is CPU consumption. Wait Time is everything else, I/O, locks, latches, network. ASH directly samples both: a session is either “ON CPU” (service time) or waiting on a specific event (wait time). By counting samples by category, ASH answers the fundamental question: where is the time going?

If 70% of samples are “ON CPU” and 30% are “db file sequential read,” the system is CPU-bound with moderate I/O. If 80% of samples are “enq: TX – row lock contention,” the system is blocked, and the SQL tuning is irrelevant until the blocking is resolved. ASH makes this visible at the second level, not the hour level .

Step 1 – Access the In-Memory ASH Data :

Current ASH (Last ~30 Minutes) :

SELECT sample_time, session_id, session_serial#, user_id, sql_id, event, wait_class, time_waited, blocking_session, current_obj#
FROM v$active_session_history WHERE sample_time > SYSDATE - 30/1440 ORDER BY sample_time DESC FETCH FIRST 100 ROWS ONLY;

Theoretical Note :

V$ACTIVE_SESSION_HISTORY is a circular buffer in memory. Its depth depends on the number of active sessions and the _ash_size parameter. On a busy system, the buffer may hold only the last 15–20 minutes of data. If the incident occurred earlier, the data has been overwritten and you must use DBA_HIST_ACTIVE_SESS_HISTORY (the AWR-persisted copy) instead .

Step 2 – Top Wait Events by Sample Count :

This is the first query to run when diagnosing a transient problem. It tells you what the sessions were actually doing during the incident window.

SELECT event, wait_class, COUNT(*) AS samples, ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER (), 2) AS pct FROM v$active_session_history WHERE
sample_time BETWEEN TO_TIMESTAMP('2024-10-01 14:30:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2024-10-01 14:35:00', 'YYYY-MM-DD HH24:MI:SS')
GROUP BY event, wait_class ORDER BY samples DESC;

What the Output Reveals :

The dominant wait event is the bottleneck. In this example, 68% of active session time was spent waiting on row locks. This is a blocking problem, not a SQL tuning problem. The next step is to identify the blocker, not to run SQL Tuning Advisor .

Step 3 – Top SQL by ASH Samples :

Once the wait event category is known, identify which SQL statements were active during those waits.

SELECT sql_id, COUNT(*) AS samples, ROUND(COUNT(*) * 100 / SUM(COUNT(*)) OVER (), 2) AS pct, MIN(event) AS top_event FROM v$active_session_history
WHERE sample_time BETWEEN TO_TIMESTAMP('2024-10-01 14:30:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2024-10-01 14:35:00', 'YYYY-MM-DD HH24:MI:SS')
AND sql_id IS NOT NULL GROUP BY sql_id ORDER BY samples DESC FETCH FIRST 10 ROWS ONLY;

Theoretical Note :

A SQL statement can appear in ASH samples for two very different reasons: it is consuming CPU (service time), or it is waiting on a resource (wait time). The EVENT column distinguishes these. A SQL statement with 5,000 samples all on “ON CPU” needs optimization. A SQL statement with 5,000 samples all on “enq: TX” is a victim of blocking and may be perfectly tuned .

Step 4 – Identify the Blocking Session from ASH :

If the top wait event is an enqueue (lock), the BLOCKING_SESSION column in ASH directly identifies the blocker.

SELECT blocking_session, COUNT(*) AS samples, MIN(event) AS wait_event, MIN(sql_id) AS blocking_sql FROM v$active_session_history
WHERE sample_time BETWEEN TO_TIMESTAMP('2024-10-01 14:30:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2024-10-01 14:35:00', 'YYYY-MM-DD HH24:MI:SS')
AND blocking_session IS NOT NULL GROUP BY blocking_session ORDER BY samples DESC;

Theoretical Note :

This is where ASH demonstrates its unique value for blocking diagnosis. In the blocking session topic covered earlier, you used V$SESSION to find the current blocker. That works only while the blocking is happening. ASH records the BLOCKING_SESSION value in every sample, so even after the incident has ended and the sessions have disconnected, you can reconstruct who blocked whom, for how long, and with which SQL statement. This is impossible with V$SESSION alone .

Step 5 – ASH Report Generation :

Generate the ASH Report :

@?/rdbms/admin/ashrpt.sql

The script prompts for:

1. Report type (HTML or text)

2. Begin time

3. End time

4. Report name

Key Sections of the ASH Report :

The Activity Over Time section is particularly useful for transient problems. It breaks the analysis window into 10-second buckets and shows the sample count in each bucket. A spike that lasts 30 seconds appears as three consecutive buckets with high sample counts, allowing you to pinpoint the exact start and end of the incident .

Step 6 – ASH and the Active Session History Graph :

In Enterprise Manager Cloud Control, the ASH data is rendered as the Active Session History graph, which is the primary visual diagnostic tool for real-time performance problems.

What the Graph Shows :

• Y-axis: Active sessions count

• X-axis: Time

• Color bands: Wait classes (CPU, Application, Commit, User I/O, etc.)

A flat band of “Application” waits at 50 sessions means 50 sessions were consistently blocked on application-level locks. A sudden spike in “Commit” waits correlates with a commit burst. A thin band of “User I/O” rising while “CPU” falls suggests the workload shifted from computation to I/O .

This graph is the first thing to check when a user reports “the system was slow for a few minutes.” If the spike is visible in the ASH graph but ADDM reported nothing for that hour, ASH is the correct diagnostic path.

Step 7 – Persisted ASH (DBA_HIST_ACTIVE_SESS_HISTORY) :

If the incident occurred beyond the in-memory ASH buffer, query the AWR-persisted copy:

SELECT sample_time, session_id, sql_id, event, blocking_session FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN TO_TIMESTAMP('2024-09-30 08:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2024-09-30 09:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND event = 'enq: TX - row lock contention';

Theoretical Note :

DBA_HIST_ACTIVE_SESS_HISTORY is populated when AWR flushes ASH data to disk, typically every 10 seconds during snapshot generation. The data is subject to AWR retention (default 8 days) and can be preserved longer using AWR baselines. This makes it possible to diagnose a transient problem that occurred days ago, as long as the AWR snapshots from that period are still retained .

ASH vs AWR vs ADDM : When to Use Which :

The decision rule is straightforward: if the problem lasted less than the AWR snapshot interval (default 1 hour) and ADDM did not report it, use ASH. If the problem is gradual degradation over weeks, use AWR Compare Periods. If the problem is a single slow SQL statement that is still running, use SQL Monitor.

Practical Considerations :

ASH Sampling Bias :

ASH samples once per second per active session. A session that is active for 0.4 seconds may produce zero samples. This means ASH under-reports very short-lived operations. For diagnosing latch contention or buffer busy waits on high-frequency OLTP systems, ASH can show the trend but may miss individual spikes .

In-Memory Buffer Sizing :

The _ash_size hidden parameter controls the in-memory buffer. On systems with very high concurrency (thousands of active sessions), the buffer may wrap quickly. Monitor V$ACTIVE_SESSION_HISTORY row count and time range to determine how far back the in-memory data extends.

ASH and Licensing :

ASH is part of the Diagnostics Pack. The CONTROL_MANAGEMENT_PACK_ACCESS parameter must be set to DIAGNOSTIC+TUNING for ASH data to be collected and for V$ACTIVE_SESSION_HISTORY to be populated .

Summary :

ASH is not a replacement for AWR or ADDM. It is the tool you reach for when the snapshot-based model fails — when the problem was real, the users felt it, but ADDM says nothing because the incident was statistically invisible. The sampling model makes the invisible visible, at the cost of accepting that not every second is recorded.