If an AWR report is a summary of a patient's health over a whole week, an ASH (Active Session History) report is the high-def security footage of exactly what was happening in the emergency room at 2:05:15 PM.

While AWR is great for seeing trends over hours or days, ASH is designed for "surgical" troubleshooting—finding out what is happening right now or during a very short spike of slowness.

1. What is ASH?

The database samples all active sessions every single second. It records:

• Who is the user?

• What SQL are they running?

• Where are they stuck? (Which wait event?)

• Which specific row or block are they waiting for?

This data lives in a circular buffer in the memory (SGA). If you don't catch it quickly, it gets flushed, but the most important bits eventually move into the AWR repository.

2. When to use ASH instead of AWR

You reach for an ASH report when:

1. The problem is transient: A query hung for 30 seconds and then finished. An hourly AWR report will "smooth out" that spike, making it invisible. ASH will show it clearly.

2. The problem is happening NOW: You need to see which session is currently blocking everyone else.

3. You need granular detail: You want to know which specific Object ID or File ID is causing a bottleneck.

3. Key Sections of an ASH Report

A. Top User Events

This tells you the "Why." If 90% of your active sessions are waiting on enq: TX - row lock contention, you don't have a slow disk problem; you have a code/locking problem.

B. Top SQL

This identifies the "What." It lists the exact SQL IDs that were active during the sampled seconds. It even breaks down if the SQL was spending time Using CPU vs. Waiting for I/O.

C. Top Blocking Sessions

This is the "Who." ASH is excellent at showing the "lead blocker"—the session at the top of a chain that is causing 50 other people to wait.

4. How to Generate an ASH Report

You can generate the report via SQL*Plus (requires the Diagnostic Pack license):

@$ORACLE_HOME/rdbms/admin/ashrpt.sql

You will be asked for:

• Start Time: (e.g., 14:00:00 for 2:00 PM).

• Duration: (e.g., 5 for five minutes).

5. Comparison: AWR vs. ASH

6. The "Emergency" Query

If you can't run a full report, you can query the ASH view directly to see what's happening this second:

SELECT session_id, sql_id, event, wait_class
FROM v$active_session_history
WHERE session_state = 'WAITING'
AND sample_time > sysdate - (5/1440); -- Last 5 minutes

Pro-Tip: ASH and Top Objects

One of the most powerful parts of an ASH report is the Top Objects section. If you see a specific table or index appearing at the top of the ASH report for db file sequential read, you know exactly where to apply your tuning (like partitioning that table or rebuilding that index) without guessing.