AWR & ASH Deep Dive: Reading the Top 5 Wait Events & Building a Bottleneck Baseline

1. Overview & Executive Summary

Oracle’s Automatic Workload Repository (AWR) and Active Session History (ASH) are the premier diagnostic frameworks for performance tuning in enterprise Oracle Database 19c and 23ai environments. However, DBAs frequently misinterpret AWR reports by focusing strictly on single wait event names without evaluating DB Time, CPU utilization, or ASH session timelines.

A wait event showing high total wait time (such as log file sync or db file sequential read) is not necessarily a bottleneck if it accounts for a minor fraction of total DB Time. Conversely, high CPU consumption (DB CPU) can mask latch contention or unindexed join operations.

This guide provides a practical, step-by-step diagnostic runbook for reading AWR Top 5 Timed Foreground Events, executing custom ASH SQL queries, calculating DB Time ratios, and building a 30-day baseline to detect performance drift.

Process Flow

---

2. Diagnostic Checklist

Run these SQL scripts to extract AWR snapshots, query ASH in real time, and inspect Top Wait Events.

2.1 Generate AWR and ASH Reports via SQL*Plus

-- Connect as SYSDBA
sqlplus / as sysdba

-- 1. Generate standard AWR HTML report for snapshot range
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

-- 2. Generate ASH HTML report for a specific 15-minute peak window
@$ORACLE_HOME/rdbms/admin/ashrpt.sql

2.2 Query Real-Time ASH Top Wait Events (Last 15 Minutes)

-- Query v$active_session_history for top wait events by active session count
SELECT event, 
       session_state,
       COUNT(*) as sample_count,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) as pct_db_time
FROM v$active_session_history
WHERE sample_time > SYSDATE - (15 / 1440)
  AND wait_class != 'Idle'
GROUP BY event, session_state
ORDER BY sample_count DESC
FETCH FIRST 10 ROWS ONLY;

Expected output: Highlights whether active sessions are consuming CPU (session_state = 'ON CPU') or waiting on specific I/O, concurrency, or lock wait events.

2.3 Calculate DB Time vs. Elapsed Time Ratio

-- Calculate DB Time to Elapsed Time ratio from AWR snapshots
SELECT snap_id,
       to_char(end_interval_time, 'YYYY-MM-DD HH24:MI') as snap_time,
       round(db_time / 60 / 1000000, 2) as db_time_mins,
       round(elapsed_time / 60 / 1000000, 2) as elapsed_mins,
       round((db_time / elapsed_time), 2) as avg_active_sessions
FROM (
  SELECT s.snap_id, s.end_interval_time,
         e.value - b.value as db_time,
         (extract(day from (s.end_interval_time - s.begin_interval_time))*86400 +
          extract(hour from (s.end_interval_time - s.begin_interval_time))*3600 +
          extract(minute from (s.end_interval_time - s.begin_interval_time))*60 +
          extract(second from (s.end_interval_time - s.begin_interval_time))) * 1000000 as elapsed_time
  FROM dba_hist_snapshot s
  JOIN dba_hist_sys_time_model b ON b.snap_id = s.snap_id - 1 AND b.stat_name = 'DB time'
  JOIN dba_hist_sys_time_model e ON e.snap_id = s.snap_id AND e.stat_name = 'DB time'
  WHERE s.end_interval_time > SYSDATE - 7
)
ORDER BY snap_id DESC;

---

3. Step-by-Step Resolution Runbook

Step 0: Safety Checks

⚠️ PREREQUISITE: Ensure the Oracle Diagnostic Pack is licensed (CONTROL_MANAGEMENT_PACK_ACCESS = 'DIAGNOSTIC+TUNING').

SHOW PARAMETER control_management_pack_access;

---

Step 1: Analyze AWR Top 5 Timed Foreground Events

When reviewing an AWR report, locate the Top 5 Timed Foreground Events table:

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Event                        Waits    Time(s)  Avg wait(ms)  % DB time  Wait Class
--------------------------- ------- ---------- ------------- ---------- ----------
DB CPU                        -         4,210       -           58.4     
db file sequential read     450,210     1,820       4.04        25.2     User I/O
log file sync               120,400       680       5.64         9.4     Commit
enq: TX - row lock contention  1,420       310     218.31        4.3     Application

Diagnostic Rules:

1. DB CPU > 50% DB Time: The workload is CPU-bound. Focus on SQL statements with high buffer gets (SQL ordered by Buffer Gets) and missing indexes rather than I/O hardware.
2. db file sequential read High: Single-block index reads dominate. Verify index clustering factors and check for unindexed foreign keys.
3. log file sync High: Commit frequency is excessive or redo log disk latency is high. Check redo log space requests and log file write times.

---

Step 2: Correlate Wait Events with SQL Statements via ASH

Query dba_hist_active_sess_history to identify the exact SQL_IDs responsible for a specific wait event during a performance spike.

-- Find Top 5 SQL_IDs for a specific wait event (e.g. 'db file sequential read')
SELECT sql_id, 
       COUNT(*) as wait_samples,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) as pct_of_event
FROM dba_hist_active_sess_history
WHERE event = 'db file sequential read'
  AND sample_time BETWEEN TO_TIMESTAMP('2026-08-12 08:00:00', 'YYYY-MM-DD HH24:MI:SS')
                      AND TO_TIMESTAMP('2026-08-12 09:00:00', 'YYYY-MM-DD HH24:MI:SS')
GROUP BY sql_id
ORDER BY wait_samples DESC
FETCH FIRST 5 ROWS ONLY;

Once top SQL_IDs are identified, extract their execution plan and runtime metrics:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('sql_id_here'));

---

Step 3: Establish a 30-Day Performance Baseline

Create an AWR baseline to capture normal database behavior, enabling automated alert thresholds for unexpected spikes.

-- Create named AWR baseline for a healthy 7-day period
BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
    start_snap_id => 1420,
    end_snap_id   => 1588,
    baseline_name => 'Prod_Normal_Baseline_Aug2026'
  );
END;
/

Verify baseline creation:

SELECT baseline_name, start_snap_id, end_snap_id, creation_date
FROM dba_hist_baseline;

---

Step 4: Verification & Trend Analysis

Run a trend comparison between current AWR metrics and your established baseline:

-- Compare current snapshot metrics against baseline averages
SELECT b.metric_name, 
       b.average as baseline_avg,
       c.average as current_avg,
       round(((c.average - b.average) / b.average) * 100, 2) as pct_change
FROM dba_hist_sysmetric_summary b
JOIN dba_hist_sysmetric_summary c ON c.metric_name = b.metric_name
WHERE b.baseline_name = 'Prod_Normal_Baseline_Aug2026'
  AND c.snap_id = (SELECT max(snap_id) FROM dba_hist_snapshot)
  AND b.metric_name IN ('Host CPU Utilization (%)', 'Average Active Sessions', 'Database Wait Time Ratio');

---

📚 Official Documentation & Technical References

Oracle Documentation

• Oracle Database 23ai SQL Tuning Guide
• Oracle Database 23ai 2-Day + Performance Tuning Guide

My Oracle Support

For official My Oracle Support AWR and ASH diagnostic guidelines, search the MOS Knowledge Base directly:

• Interpreting AWR Reports: Top 5 Timed Events Analysis Runbook
• Active Session History (ASH) Querying Best Practices & Diagnostic Scripts

Related Resources

• Oracle Database 19c SQL Tuning Guide

---

Need Expert Performance Tuning Assistance?

Diagnosing complex AWR/ASH wait events, resolving latch contention, and tuning high-volume ERP database workloads require specialized DBA expertise. DBPros.Net’s certified Oracle Masters can help you:

• Analyze & Audit AWR/ASH reports to identify root cause bottlenecks
• Establish automated 30-day performance baselines and anomaly detection
• Tune top resource-consuming SQL queries, execution plans, and index structures
• Eliminate log file sync, buffer busy waits, and enqueue contention 24/7/365

Contact DBPros.Net Tuning Experts | Explore Tuning Services