Sequential I/O Wait Analysis

Caution

This script queries AWR tables which require Diagnostic Pack licensing. Note: The provided script contains an error - it monitors scattered reads instead of sequential reads.

Script: Sequential_io.sql

rem psysev.sql
rem
set lines 132
rem
set pagesize 200;
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
rem
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,
  (case
when
(se.time_waited_micro - sb.time_waited_micro)
          / decode( (se.total_waits - sb.total_waits), 0, 1, (se.total_waits - sb.total_waits) )/1000
< 11 then 'GREEN'
when
(se.time_waited_micro - sb.time_waited_micro)
          / decode( (se.total_waits - sb.total_waits), 0, 1, (se.total_waits - sb.total_waits) )/1000
< 16 then 'YELLOW'
else 'RED' END) STATUS
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.end_interval_time >= trunc(sysdate)
  and e.end_interval_time <= sysdate + 1
  and sb.event_name = se.event_name
  and upper(se.event_name) like upper('db file scattered read')
order by
      e.end_interval_time
      , e.instance_number
      , se.event_name
/
rem
undefine start_dt
undefine end_dt
undefine nam
undefine instno

Important Note

The provided script has an error: It monitors ‘db file scattered read’ instead of ‘db file sequential read’. To monitor sequential reads, change line 68 to:

and upper(se.event_name) like upper('db file sequential read')

Purpose

This script should monitor ‘db file sequential read’ wait events from AWR history, providing insight into single-block I/O operations typically associated with index lookups and table access by ROWID.

Key Features

• AWR-Based Analysis: Uses historical snapshots for trend analysis
• Performance Metrics: Calculates waits per second and average wait times
• Status Indicators: Color-coded status based on wait time thresholds
• Delta Calculations: Shows changes between consecutive snapshots

Metrics Displayed

1. SNAP ID: AWR snapshot identifier
2. SNAP TIME: Snapshot timestamp
3. INST: Instance number (for RAC)
4. TOTAL WAITS: Number of waits in the interval
5. WAITS PER SEC: Wait frequency
6. TIMEOUT PCT: Percentage of timed-out waits
7. TIME WAITED(ms): Total wait time in milliseconds
8. AVG WAIT TIME(ms): Average wait time per event
9. STATUS: Performance indicator (GREEN/YELLOW/RED)

Status Thresholds

• GREEN: Average wait time < 11ms (good performance)
• YELLOW: Average wait time 11-16ms (marginal performance)
• RED: Average wait time > 16ms (poor performance)

Note: Sequential read thresholds are lower than scattered reads because single-block reads should be faster.

Understanding Sequential Reads

Sequential reads occur when Oracle reads a single block, typically during:

• Index unique scans
• Index range scans
• Table access by ROWID
• Undo segment reads

Performance Implications

High sequential read waits may indicate:

• Slow I/O subsystem
• Hot blocks causing contention
• Inefficient index access patterns
• Insufficient buffer cache

Optimization Strategies

1. Improve I/O Performance: Faster storage, better RAID configuration
2. Increase Buffer Cache: Keep frequently accessed blocks in memory
3. Optimize Indexes: Ensure efficient index design
4. Reduce Logical I/O: Tune SQL to minimize block accesses
5. Consider SSD Storage: For frequently accessed data

Corrected Usage

To properly monitor sequential reads:

-- Fix the event name in the script
SQL> @Sequential_io.sql

Related Scripts

• Scattered_io.sql: Monitor multi-block reads
• random_io.sql: Analyze random I/O patterns
• gvio.sql: Real-time I/O statistics