I/O Wait Analysis for Current Sessions (VIO.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 provides focused I/O performance analysis by:

• Monitoring the two most critical I/O wait events across all RAC instances
• Calculating average wait times in milliseconds for accurate performance assessment
• Aggregating I/O statistics from all current sessions
• Comparing performance between instances in RAC environments
• Focusing specifically on database file read operations

Script

COLUMN "total_waits" FORMAT 999,999,999
column "avg_wait_ms" format 9,999.9
ttitle "IO Wait current sessions"
select
e.inst_id,
e.event event,
--e.total_waits twaits,
sum(e.total_waits) total_waits ,
sum(e.time_waited_micro) / sum(e.total_waits) / 1000 avg_wait_ms
from       Gv$session_event e
where e.event like '%db file sequential%'
or e.event like '%db file scat%'
group by inst_id, event
order by inst_id,event
;

Usage

SQL> @VIO.SQL

Required Privileges

• SELECT on GV$SESSION_EVENT

Sample Output

IO Wait current sessions

INST_ID EVENT                             TOTAL_WAITS  AVG_WAIT_MS
------- --------------------------------- ----------- -----------
      1 db file sequential read             5,234,567        8.7
      1 db file scattered read              1,456,789       16.1
      2 db file sequential read             4,987,432       10.5
      2 db file scattered read              1,234,567       16.8
      3 db file sequential read             5,123,456        8.0
      3 db file scattered read              1,345,678       15.9

Key Output Columns

• INST_ID: RAC instance number
• EVENT: I/O wait event name
• TOTAL_WAITS: Total number of I/O wait occurrences across all sessions
• AVG_WAIT_MS: Average wait time per I/O operation in milliseconds

Understanding I/O Events

db file sequential read

• Purpose: Single block reads (index range scans, ROWID access)
• Common Operations: Index lookups, primary key access, nested loop joins
• Performance Target: < 10ms for traditional disk, < 5ms for SSD

db file scattered read

• Purpose: Multi-block reads (full table scans, fast full index scans)
• Common Operations: Table scans, hash joins, bulk operations
• Performance Target: < 20ms for traditional disk, < 10ms for SSD

Performance Analysis

Baseline Performance Expectations

• Excellent: Sequential reads < 5ms, Scattered reads < 10ms
• Good: Sequential reads 5-10ms, Scattered reads 10-20ms
• Acceptable: Sequential reads 10-15ms, Scattered reads 20-30ms
• Poor: Sequential reads > 15ms, Scattered reads > 30ms

Cross-Instance Comparison

• Balanced Performance: Similar wait times across instances
• Instance Imbalance: Investigate storage configuration differences
• Consistent Ratios: Scattered reads should be higher than sequential reads

Common Performance Issues

High Sequential Read Times

• Storage Performance: Slow disk subsystem
• Hot Blocks: Contention for popular data blocks
• Buffer Cache: Insufficient memory causing cache misses

High Scattered Read Times

• Storage Configuration: Poor multi-block read performance
• Parameter Tuning: DB_FILE_MULTIBLOCK_READ_COUNT optimization needed
• Application Design: Excessive full table scans

Instance Performance Variance

• Storage Differences: Uneven storage allocation across nodes
• Network Issues: Cluster interconnect problems affecting storage access
• Workload Distribution: Unbalanced application connections

Advanced Analysis

Detailed I/O Breakdown

-- Get more detailed I/O analysis
SELECT inst_id, event,
       total_waits,
       ROUND(time_waited_micro/1000000, 2) total_wait_secs,
       ROUND(time_waited_micro/total_waits/1000, 2) avg_wait_ms,
       ROUND(total_waits * 100 / SUM(total_waits) OVER (), 2) pct_of_total_waits
FROM (
    SELECT inst_id, event,
           SUM(total_waits) total_waits,
           SUM(time_waited_micro) time_waited_micro
    FROM gv$session_event
    WHERE event LIKE '%db file sequential%'
    OR event LIKE '%db file scat%'
    GROUP BY inst_id, event
)
ORDER BY inst_id, total_wait_secs DESC;

Compare with System Events

-- Compare session events with system-wide statistics
SELECT se.inst_id, se.event,
       se.session_total_waits,
       se.session_avg_ms,
       sye.system_total_waits,
       sye.system_avg_ms
FROM (
    SELECT inst_id, event,
           SUM(total_waits) session_total_waits,
           ROUND(SUM(time_waited_micro)/SUM(total_waits)/1000, 2) session_avg_ms
    FROM gv$session_event
    WHERE event LIKE '%db file sequential%' OR event LIKE '%db file scat%'
    GROUP BY inst_id, event
) se,
(
    SELECT inst_id, event,
           total_waits system_total_waits,
           ROUND(time_waited_micro/total_waits/1000, 2) system_avg_ms
    FROM gv$system_event
    WHERE event LIKE '%db file sequential%' OR event LIKE '%db file scat%'
) sye
WHERE se.inst_id = sye.inst_id
AND se.event = sye.event
ORDER BY se.inst_id, se.event;

Historical Trend Analysis

-- Create table for trending (one-time setup)
CREATE TABLE io_wait_history (
    check_time DATE,
    inst_id NUMBER,
    event VARCHAR2(64),
    total_waits NUMBER,
    avg_wait_ms NUMBER
);

-- Insert current snapshot
INSERT INTO io_wait_history
SELECT SYSDATE, inst_id, event,
       SUM(total_waits),
       SUM(time_waited_micro)/SUM(total_waits)/1000
FROM gv$session_event
WHERE event LIKE '%db file sequential%' OR event LIKE '%db file scat%'
GROUP BY inst_id, event;

Troubleshooting High I/O Waits

Immediate Investigation Steps

1. Check Storage Metrics: Disk utilization, queue depth, response times
2. Review Current SQL: Identify statements causing high I/O
3. Analyze Object Access: Find hot tables and indexes

Storage Analysis

-- Check file-level I/O statistics
SELECT f.inst_id, ts.name tablespace_name, f.name file_name,
       f.phyrds, f.phywrts,
       ROUND(f.readtim/GREATEST(f.phyrds, 1)*10, 2) avg_read_ms,
       ROUND(f.writetim/GREATEST(f.phywrts, 1)*10, 2) avg_write_ms
FROM gv$filestat f, gv$tablespace ts
WHERE f.ts# = ts.ts#
AND f.inst_id = ts.inst_id
AND f.phyrds > 0
ORDER BY f.inst_id, avg_read_ms DESC;

SQL Analysis

-- Find SQL statements with high I/O
SELECT sql_id, child_number,
       executions,
       disk_reads,
       buffer_gets,
       ROUND(disk_reads/GREATEST(executions, 1), 2) disk_reads_per_exec,
       ROUND(buffer_gets/GREATEST(executions, 1), 2) buffer_gets_per_exec
FROM v$sql
WHERE disk_reads > 10000
ORDER BY disk_reads DESC;

RAC-Specific Considerations

Instance Load Balancing

• Even Distribution: I/O should be balanced across instances
• Connection Affinity: Check application connection patterns
• Storage Affinity: Consider storage-aware connection routing

Cluster Performance

• Interconnect Impact: Monitor cluster wait events
• Cache Fusion: Check for excessive block transfers
• Shared Storage: Ensure consistent performance across nodes

Performance Optimization

Storage Level Optimizations

1. Upgrade Storage: Consider SSD or faster storage tiers
2. I/O Configuration: Optimize stripe size and RAID configuration
3. ASM Tuning: Balance disk groups and allocation units

Database Level Optimizations

1. Buffer Cache: Increase size if hit ratios are low
2. Multiblock Read Count: Tune DB_FILE_MULTIBLOCK_READ_COUNT
3. Asynchronous I/O: Ensure DISK_ASYNCH_IO is enabled

Application Level Optimizations

1. SQL Tuning: Optimize queries to reduce I/O
2. Indexing: Add appropriate indexes to reduce full scans
3. Connection Pooling: Balance load across instances

Monitoring and Alerting

Performance Thresholds

-- Set up alerting for poor I/O performance
SELECT inst_id, event, avg_wait_ms,
       CASE
         WHEN avg_wait_ms > 30 THEN 'CRITICAL'
         WHEN avg_wait_ms > 20 THEN 'WARNING'
         WHEN avg_wait_ms > 10 THEN 'CAUTION'
         ELSE 'OK'
       END alert_level
FROM (
    SELECT inst_id, event,
           SUM(time_waited_micro)/SUM(total_waits)/1000 avg_wait_ms
    FROM gv$session_event
    WHERE event LIKE '%db file sequential%' OR event LIKE '%db file scat%'
    GROUP BY inst_id, event
)
WHERE avg_wait_ms > 10;

Automated Monitoring

-- Script for automated monitoring
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') check_time,
       inst_id,
       event,
       total_waits,
       avg_wait_ms
FROM (
    SELECT inst_id, event,
           SUM(total_waits) total_waits,
           ROUND(SUM(time_waited_micro)/SUM(total_waits)/1000, 2) avg_wait_ms
    FROM gv$session_event
    WHERE event LIKE '%db file sequential%' OR event LIKE '%db file scat%'
    GROUP BY inst_id, event
);

Best Practices

1. Regular Monitoring

   • Check I/O performance during peak hours
   • Compare patterns across different time periods
   • Track trends for capacity planning

2. Baseline Establishment

   • Document normal I/O wait times
   • Establish performance expectations by workload
   • Monitor for deviations from baseline

3. Proactive Management

   • Set up automated alerts for performance degradation
   • Regular storage health checks
   • Plan capacity upgrades before issues occur

Related Scripts

• Session I/O Statistics
• RAC I/O Analysis
• File I/O Statistics