Scattered I/O Wait Analysis

Caution

This script queries AWR tables which require Diagnostic Pack licensing. Ensure proper licensing before use.

Script: Scattered_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
< 15 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
< 22 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

Purpose

This script monitors ‘db file scattered read’ wait events from AWR history, providing insight into multi-block I/O operations typically associated with full table scans and index fast full scans.

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 < 15ms (good performance)
• YELLOW: Average wait time 15-22ms (marginal performance)
• RED: Average wait time > 22ms (poor performance)

Usage

SQL> @Scattered_io.sql

The script automatically analyzes the current day’s data (from midnight to current time).

Understanding Scattered Reads

Scattered reads occur when Oracle reads multiple blocks that are scattered throughout the buffer cache, typically during:

• Full table scans
• Index fast full scans
• Direct path reads

Performance Implications

High scattered read waits may indicate:

• Missing or inefficient indexes
• Large table scans
• Insufficient buffer cache
• Slow I/O subsystem

Optimization Strategies

1. Add Appropriate Indexes: Reduce full table scans
2. Tune SQL Statements: Use selective predicates
3. Increase Buffer Cache: If memory permits
4. Improve I/O Performance: Faster storage, better distribution
5. Consider Partitioning: For large tables

Related Scripts

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