Oracle AWR vs ASH: When to Use Each

The Core Difference

AWR (Automatic Workload Repository) captures aggregated statistics at snapshot intervals. By default, Oracle takes a snapshot every 60 minutes, computing the difference between cumulative counters. An AWR report covers the delta between two snapshots — typically 60 minutes of activity, averaged.

ASH (Active Session History) samples individual active sessions every second and stores those samples in memory (v$active_session_history) and on disk (dba_hist_active_sess_history). Each sample records what a session was doing at that exact moment: which SQL, which wait event, which object.

The analogy: AWR is a weather report (average temperature over a month). ASH is a weather camera (a photo every second). Both are useful — but for different questions.

AWR at a Glance

How it works:

Every 60 minutes (default):
  MMON process wakes up
  → reads cumulative counters from v$* views
  → stores snapshot in dba_hist_* tables
  → old snapshots beyond retention (8 days) are purged

What it captures: - Top SQL by elapsed time, CPU, I/O, memory, executions - Top wait events (aggregated over the interval) - Load profile: logical/physical reads, commits, parses per second - System statistics: redo size, buffer cache efficiency - Segment statistics: which tables and indexes generated the most I/O - Instance activity: detailed counters for every database metric

Report generation:

sqlplus / as sysdba
@?/rdbms/admin/awrrpt.sql

Retention: 8 days by default, configurable.

Granularity: 60-minute intervals by default (configurable down to 10 minutes).

ASH at a Glance

How it works:

Every 1 second:
  MMNL process samples all ACTIVE sessions
  → stores sample in v$active_session_history (circular buffer, ~1 hour in memory)
  → every AWR snapshot, recent ASH data is flushed to dba_hist_active_sess_history (on disk)

What it captures (per sample): - Session identifier and username - SQL ID being executed - Wait event (what the session was waiting for) - Object being accessed (table, index) - P1/P2/P3 parameters of the wait event (specific block, file number, etc.) - Program, module, action (from dbms_application_info) - Service name

Report generation:

sqlplus / as sysdba
@?/rdbms/admin/ashrpt.sql

Retention: ~1 hour in v$active_session_history (memory), then flushed to disk via AWR (8 days by default). Historical ASH on disk is sampled at 1-in-10 (only 10% of samples are kept on disk to save space).

Granularity: 1-second samples.

When to Use AWR

Use AWR when:

1. The problem lasted more than 30 minutes

AWR's 60-minute intervals make it ideal for sustained performance issues. If the database was slow all morning, an AWR report for that window gives you a complete picture of where the time went.

2. You want to compare periods

AWR's awrddrpt.sql generates a comparison between two time windows — perfect for before/after analysis: - Before and after an index change - This week vs last week - Peak hour vs off-peak hour

@?/rdbms/admin/awrddrpt.sql
-- Prompts for two snapshot ranges to compare

3. You need segment-level statistics

AWR's "Segments by Physical Reads" and "Segments by Buffer Busy Waits" sections identify which specific tables and indexes are causing I/O or contention. ASH doesn't have this.

4. You're doing periodic performance review

AWR is your tool for routine performance monitoring — weekly or monthly review of load trends, parse ratios, buffer cache efficiency, and top SQL evolution.

5. The incident happened yesterday or last week

ASH in memory only covers the last ~1 hour. For historical analysis (up to 8 days), AWR is your only option. Note that historical ASH on disk (dba_hist_active_sess_history) is sampled at 1-in-10 — less precise than the in-memory version.

When to Use ASH

Use ASH when:

1. The problem lasted less than 30 minutes

A 15-minute spike in a 60-minute AWR window appears diluted — the spike's contribution is averaged into the broader interval. ASH captures every second of that spike with full detail.

-- AWR: shows average over 60 minutes — spike is hidden
-- ASH: shows exactly what happened during the 15-minute window
SELECT event, count(*) samples
FROM v$active_session_history
WHERE sample_time BETWEEN TO_TIMESTAMP('2026-06-20 14:00', 'YYYY-MM-DD HH24:MI')
                      AND TO_TIMESTAMP('2026-06-20 14:15', 'YYYY-MM-DD HH24:MI')
GROUP BY event
ORDER BY samples DESC;

2. You need to identify a specific moment

"The system froze at exactly 14:23" — AWR can't help here. ASH can show you what every session was doing at 14:23:00.

-- What was happening at a specific second?
SELECT sql_id, event, session_id, count(*) sessions
FROM v$active_session_history
WHERE sample_time BETWEEN TO_TIMESTAMP('2026-06-20 14:23:00', 'YYYY-MM-DD HH24:MI:SS')
                      AND TO_TIMESTAMP('2026-06-20 14:23:10', 'YYYY-MM-DD HH24:MI:SS')
GROUP BY sql_id, event, session_id
ORDER BY sessions DESC;

3. You need to identify a specific session or user

ASH records session IDs and usernames per sample. If the business says "user JSMITH reported slowness at 2pm", you can query ASH for exactly what JSMITH's sessions were doing.

SELECT sql_id, event, count(*) samples
FROM v$active_session_history
WHERE session_id IN (
    SELECT sid FROM v$session WHERE username = 'JSMITH'
)
AND sample_time > SYSDATE - 1/24
GROUP BY sql_id, event
ORDER BY samples DESC;

4. You're investigating blocking and locking

ASH records blocking_session — the session holding the lock that a waiting session is blocked on. This creates a lock chain you can trace.

SELECT
    ash.session_id AS blocked_session,
    ash.blocking_session AS blocker_session,
    ash.event,
    ash.sql_id,
    count(*) AS samples
FROM v$active_session_history ash
WHERE ash.blocking_session IS NOT NULL
AND ash.sample_time > SYSDATE - 1/24
GROUP BY ash.session_id, ash.blocking_session, ash.event, ash.sql_id
ORDER BY samples DESC;

5. You need to understand a specific SQL statement's history

AWR shows aggregate SQL statistics. ASH shows the execution history of a specific SQL ID — when it ran, how long each execution took, what it was waiting for.

SELECT
    TO_CHAR(sample_time, 'HH24:MI:SS') AS time,
    event,
    session_id
FROM v$active_session_history
WHERE sql_id = 'abc123def456'
AND sample_time > SYSDATE - 2/24
ORDER BY sample_time;

Decision Guide

Situation	Use
System was slow for 2 hours	AWR
System froze for 10 minutes	ASH
Want to compare this week vs last week	AWR
"It was slow at exactly 14:23"	ASH
Need top SQL for the day	AWR
Need to trace a specific SQL's executions	ASH
Segment-level I/O analysis	AWR
Lock contention — who blocked whom	ASH
Weekly performance review	AWR
User-specific slowness investigation	ASH
Incident happened 5 days ago (> 1 hour)	AWR
Incident happened 30 minutes ago	ASH (in-memory)
Parse ratio analysis	AWR
Active session count over time	ASH

Using AWR and ASH Together

The most effective approach combines both:

Step 1: Start with AWR to understand the overall workload and identify the dominant wait events and top SQL for the period.

Step 2: Use ASH to drill into specifics — if AWR shows db file sequential read is the top wait event, use ASH to find exactly which sessions, SQL statements, and objects were involved, and when the contention peaked.

-- AWR told you: db file sequential read dominated 14:00-15:00
-- ASH tells you: it peaked at 14:23 on this specific SQL and object

SELECT
    sql_id,
    current_obj#,
    count(*) samples,
    count(*) * 100 / SUM(count(*)) OVER() AS pct
FROM v$active_session_history
WHERE event = 'db file sequential read'
AND sample_time BETWEEN TO_TIMESTAMP('2026-06-20 14:00', 'YYYY-MM-DD HH24:MI')
                    AND TO_TIMESTAMP('2026-06-20 15:00', 'YYYY-MM-DD HH24:MI')
GROUP BY sql_id, current_obj#
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;

Step 3: Cross-reference the SQL ID from ASH with the execution plan in AWR's dba_hist_sql_plan.

Generating the Reports

AWR Report

-- Interactive
sqlplus / as sysdba
@?/rdbms/admin/awrrpt.sql

-- Programmatic (spool to file)
SELECT output FROM TABLE(
  DBMS_WORKLOAD_REPOSITORY.awr_report_html(
    l_dbid    => (SELECT dbid FROM v$database),
    l_inst_num => 1,
    l_bid     => 4818,
    l_eid     => 4819));

ASH Report

-- Interactive
sqlplus / as sysdba
@?/rdbms/admin/ashrpt.sql

-- ASH report for a specific time window
SELECT output FROM TABLE(
  DBMS_WORKLOAD_REPOSITORY.ash_report_html(
    l_dbid      => (SELECT dbid FROM v$database),
    l_inst_num  => 1,
    l_btime     => TO_DATE('2026-06-20 14:00', 'YYYY-MM-DD HH24:MI'),
    l_etime     => TO_DATE('2026-06-20 14:30', 'YYYY-MM-DD HH24:MI')));

For a detailed guide on generating AWR reports, see How to Generate an Oracle AWR Report.

Frequently Asked Questions

Can I use ASH without the Diagnostics Pack license? No. Both AWR and ASH require the Oracle Diagnostics Pack. Without it, you cannot legally query v$active_session_history or dba_hist_active_sess_history. Use Statspack for the AWR equivalent without a license (there is no free ASH equivalent).

How long does ASH keep data in memory? The v$active_session_history circular buffer holds approximately 1 million rows. At 1 sample per second with an average of 10 active sessions, that's about 27 hours. On a very busy system with 100+ active sessions, it may only hold a few hours.

Why does my ASH report show fewer samples than expected? ASH only samples active sessions — sessions with status = 'ACTIVE' and not idle. If your database has low concurrency (few simultaneous active sessions), ASH data is sparse but still accurate.

What is dba_hist_active_sess_history vs v$active_session_history? v$active_session_history is the in-memory buffer — every sample for the last ~1 hour with 1-second granularity. dba_hist_active_sess_history is persisted to disk via AWR snapshots, but only 1 in 10 samples is kept (to save space). For recent incidents, use v$; for older incidents, use dba_hist_.

Can I extend ASH retention beyond 8 days? Yes, by extending AWR retention: DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(retention => 30 * 24 * 60). This increases retention to 30 days for both AWR and historical ASH. More disk space in SYSAUX is required.

Analyse AWR and ASH Automatically

Switching between AWR and ASH reports, correlating wait events with specific SQL, and knowing which tool to use for which symptom requires experience that takes years to build.

DBA Copilot handles this automatically. Upload your AWR or ASH report and the AI identifies the dominant bottlenecks, correlates wait events with responsible SQL statements, and generates a plain-language diagnosis — in seconds.

Try DBA Copilot free — no credit card required