AWR System Wait Event Analysis (dhsysev.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script analyzes historical wait event data from AWR (Automatic Workload Repository) to show wait event trends over time. It calculates delta values between snapshots to display waits per second, timeout percentages, and average wait times, enabling performance trend analysis and bottleneck identification across time periods.

Script

rem psysev.sql
rem
set lines 132
rem
col SNAP_ID format 999999 heading 'SNAP ID'
col instance_number format 99 head 'INST'
col end_interval_time format a15 heading 'SNAP TIME'
col EVENT_NAME format a35 heading 'EVENT'
col total_waits format b999,999,990 heading 'TOTAL WAITS'
col waits_per_sec format b99,990 heading 'WAITS|PER|SEC'
col timeout_pct format b990.0 heading 'TIMEOUT|PCT'
col time_waited_micro format b999,999,990 heading 'TIME WAITED(ms)'
col avg_wait format 990.0 heading 'AVG|WAIT|TIME(ms)'
rem
define start_dt='&start_date'
define end_dt='&end_date'
define nam='&name'
define instno='&inst_no'
rem
ttitle 'System Wait Events (&nam)'
rem
clear break
clear compute
rem
select /*+ ordered use_nl(e x b se sb) */
       e.snap_id,
       to_char(e.end_interval_time, 'MM/DD HH24:MI') end_interval_time,
       e.instance_number,
       se.event_name,
       se.total_waits - sb.total_waits total_waits,
       ((se.total_waits - sb.total_waits) /
        ((to_date(to_char(e.end_interval_time,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS')
       -  to_date(to_char(b.end_interval_time,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS')) *60*60*24)) waits_per_sec,
       -- (se.total_waits - sb.total_waits)
      -- / ((e.end_interval_time - b.end_interval_time) ) waits_per_sec,
       (se.total_timeouts - sb.total_timeouts)
          / decode( (se.total_waits - sb.total_waits), 0, 1, (se.total_waits - sb.total_waits) )*100 timeout_pct,
       (se.time_waited_micro - sb.time_waited_micro)/1000 time_waited_micro,
       (se.time_waited_micro - sb.time_waited_micro)
          / decode( (se.total_waits - sb.total_waits), 0, 1, (se.total_waits - sb.total_waits) )/1000 avg_wait
from dba_hist_snapshot e,
     dba_hist_snapshot b,
     dba_hist_system_event se,
     dba_hist_system_event sb
where b.snap_id =
         (select max(x.snap_id)
      from dba_hist_snapshot x
     where x.snap_id < e.snap_id
       and x.dbid = e.dbid
       and x.instance_number = e.instance_number)
  and b.dbid = e.dbid
  and b.instance_number = e.instance_number
  and se.snap_id = e.snap_id
  and se.dbid = e.dbid
  and se.instance_number = e.instance_number
  and sb.snap_id = b.snap_id
  and sb.dbid = b.dbid
  and sb.instance_number = b.instance_number
  and e.instance_number like nvl('&instno','%')
  and e.end_interval_time >= nvl(to_date('&start_dt'), trunc(sysdate))
  and e.end_interval_time <= nvl(to_date('&end_dt'), sysdate) + 1
  and sb.event_name = se.event_name
  and upper(se.event_name) like upper('&nam')
order by
  b.snap_id,e.instance_Number,
      e.end_interval_time
      , e.instance_number
      , se.event_name
/
rem
undefine start_dt
undefine end_dt
undefine nam
undefine instno

Usage

-- Run the script in SQL*Plus or SQLcl
@dhsysev.sql

-- When prompted, enter parameters:
-- start_date: Starting date (MM/DD/YYYY format)
-- end_date: Ending date (MM/DD/YYYY format)
-- name: Wait event name pattern (% for all)
-- inst_no: Instance number (% for all instances)

-- Examples
Enter value for start_date: 01/01/2024
Enter value for end_date: 01/07/2024
Enter value for name: %
Enter value for inst_no: %

-- Specific wait event
Enter value for name: db file sequential read

Parameters

• &start_date: Starting date for analysis (defaults to today)
• &end_date: Ending date for analysis (defaults to today + 1)
• &name: Wait event name pattern (use % for all events)
• &inst_no: Instance number (use % for all instances in RAC)

Required Privileges

• SELECT on DBA_HIST_SNAPSHOT
• SELECT on DBA_HIST_SYSTEM_EVENT
• Requires Diagnostic Pack license
• Typically requires DBA role

Sample Output

System Wait Events (db file%)

SNAP ID SNAP TIME       INST EVENT                              TOTAL WAITS WAITS TIMEOUT TIME WAITED(ms)   AVG
                                                                            PER     PCT                   WAIT
                                                                            SEC                          TIME(ms)
------- --------------- ---- --------------------------------- ----------- ------ ------- -------------- -------
  45678 01/06 10:00        1 db file sequential read             1,234,567    343     0.0      567,890     0.5
  45678 01/06 10:00        1 db file scattered read                456,789    127     0.0      234,567     0.5
  45678 01/06 10:00        2 db file sequential read             1,345,678    374     0.0      678,901     0.5
  45678 01/06 10:00        2 db file scattered read                567,890    158     0.0      345,678     0.6

  45679 01/06 11:00        1 db file sequential read             2,345,678    652     0.0    1,234,567     0.5
  45679 01/06 11:00        1 db file scattered read                789,012    219     0.0      456,789     0.6
  45679 01/06 11:00        2 db file sequential read             2,456,789    683     0.0    1,345,678     0.5
  45679 01/06 11:00        2 db file scattered read                890,123    247     0.0      567,890     0.6

Key Output Columns

• SNAP ID: AWR snapshot identifier
• SNAP TIME: Snapshot end time
• INST: Instance number (for RAC)
• EVENT: Wait event name
• TOTAL WAITS: Number of waits during snapshot interval
• WAITS PER SEC: Rate of waits per second
• TIMEOUT PCT: Percentage of waits that timed out
• TIME WAITED(ms): Total wait time in milliseconds
• AVG WAIT TIME(ms): Average wait time per event

Understanding Wait Events

Common Wait Event Categories

• User I/O: db file sequential/scattered read
• System I/O: log file sync, log file parallel write
• Concurrency: buffer busy waits, library cache lock
• Configuration: free buffer waits, undo segment extension
• Application: enqueue waits, SQL*Net events

Performance Indicators

• High WAITS PER SEC: Indicates frequent occurrence
• High AVG WAIT TIME: Shows individual wait duration issues
• TIMEOUT PCT > 0: May indicate severe contention
• Trend Analysis: Compare values across snapshots

Performance Analysis

Identifying Bottlenecks

1. I/O Issues: High db file read waits
2. Memory Pressure: Free buffer waits
3. Contention: Latch and lock waits
4. Configuration: Log file sync waits

Wait Event Priorities

-- Find top wait events by time
SELECT event_name,
       SUM(time_waited_micro)/1000000 total_wait_seconds,
       AVG(average_wait_micro)/1000 avg_wait_ms,
       SUM(total_waits) total_waits
FROM dba_hist_system_event
WHERE snap_id BETWEEN &start_snap AND &end_snap
GROUP BY event_name
ORDER BY total_wait_seconds DESC;

Common Use Cases

Performance Trending

-- Monitor wait events over time
@dhsysev.sql
-- Enter date range and % for all events
-- Identify patterns and anomalies

Specific Event Analysis

-- Deep dive into problematic wait
@dhsysev.sql
-- Enter specific event name
-- Track changes across time

RAC Performance

-- Compare instances
@dhsysev.sql
-- Enter % for events and instances
-- Look for instance imbalances

Troubleshooting Wait Events

I/O Wait Events

-- High db file sequential read
-- Check for missing indexes
-- Review execution plans
-- Monitor I/O subsystem performance

-- High db file scattered read
-- Check for full table scans
-- Review multiblock read settings
-- Consider index creation

Concurrency Waits

-- Buffer busy waits
-- Identify hot blocks
-- Consider partitioning
-- Review application design

-- Library cache lock/pin
-- Check for DDL during peak hours
-- Review parse rates
-- Consider cursor sharing

Configuration Waits

-- Log file sync
-- Review commit frequency
-- Check redo log I/O performance
-- Consider log buffer size

-- Free buffer waits
-- Increase buffer cache
-- Improve DBWR performance
-- Check checkpoint frequency

Advanced Analysis

Wait Event Correlation

-- Correlate with system metrics
SELECT h.snap_id, h.end_interval_time,
       e.event_name,
       e.time_waited_micro/1000 wait_time_ms,
       m.average cpu_usage,
       io.small_read_reqs + io.large_read_reqs total_reads
FROM dba_hist_system_event e,
     dba_hist_snapshot h,
     dba_hist_sysmetric_summary m,
     dba_hist_iostat_function io
WHERE e.snap_id = h.snap_id
AND e.snap_id = m.snap_id
AND e.snap_id = io.snap_id
AND e.instance_number = h.instance_number
AND e.instance_number = m.instance_number
AND m.metric_name = 'Host CPU Utilization (%)'
AND io.function_name = 'DBWR'
AND h.end_interval_time >= SYSDATE - 1;

Wait Class Analysis

-- Aggregate by wait class
SELECT wait_class,
       COUNT(DISTINCT event_name) event_count,
       SUM(total_waits) total_waits,
       SUM(time_waited_micro)/1000000 total_seconds
FROM dba_hist_system_event e,
     dba_hist_snapshot s,
     v$event_name n
WHERE e.snap_id = s.snap_id
AND e.event_name = n.name
AND s.end_interval_time >= SYSDATE - 1
GROUP BY wait_class
ORDER BY total_seconds DESC;

Baseline Comparison

-- Compare to baseline period
WITH baseline AS (
  SELECT event_name,
         AVG(time_waited_micro/total_waits) baseline_avg_wait
  FROM dba_hist_system_event
  WHERE snap_id BETWEEN &baseline_start AND &baseline_end
  GROUP BY event_name
),
current AS (
  SELECT event_name,
         AVG(time_waited_micro/total_waits) current_avg_wait
  FROM dba_hist_system_event
  WHERE snap_id BETWEEN &current_start AND &current_end
  GROUP BY event_name
)
SELECT c.event_name,
       c.current_avg_wait,
       b.baseline_avg_wait,
       ROUND((c.current_avg_wait - b.baseline_avg_wait) /
             b.baseline_avg_wait * 100, 2) pct_change
FROM current c, baseline b
WHERE c.event_name = b.event_name
ORDER BY ABS(pct_change) DESC;

Related Scripts

• Real-time Wait Events (../performance-analysis/vwait.md) - Current wait analysis
• Session Wait Events (../performance-analysis/vsessio.md) - Session-level waits
• System Statistics (../performance-analysis/system_stats.md) - Overall system metrics
• AWR Report Generation (../performance-analysis/awrrpt.md) - Complete AWR reports

Best Practices

Regular Monitoring

1. Daily reviews: Check for anomalies
2. Trend analysis: Weekly/monthly patterns
3. Baseline establishment: Normal vs abnormal
4. Alert configuration: Proactive monitoring

Data Retention

• AWR retention affects historical analysis
• Default is 8 days
• Increase for longer trend analysis
• Balance with space requirements