RAC I/O Wait Time Analysis (gvio.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 I/O performance across RAC instances by:

• Monitoring single block reads (db file sequential read) and multi-block reads (db file scattered read)
• Calculating average wait times in milliseconds for accurate performance measurement
• Providing both instance-level and session-level I/O analysis
• Filtering out system and background sessions to focus on user activity
• Identifying I/O performance bottlenecks across the RAC cluster

Script

rem  gvio.sql
rem
ttitle 'I/O Times'
rem
set linesize 500
rem
col s_sid format 999999 heading 'SESS|SID'
col sid format 999999 heading 'SESS|SID'
col e_sid format 9999 heading 'EVNT|SID'
col elapsed_secs format 9999999 heading 'ELAPSED|SECS'
col event format a28 heading 'EVENT'
col total_waits format 9,999,999,999 heading 'TOTAL|WAITS'
col total_timeouts format b999999 heading 'TOTAL|TIMEOUTS'
col timeout_pct format b999.9 heading 'TIME|OUT|PCT'
col time_waited format b9,999,999,999,999 heading 'TIME|WAITED'
col time_waited_secs format b9999999.00 heading 'TIME|WAITED|(secs)'
col average_wait format b999999.9 heading 'AVERAGE|WAIT'
col avg_wait_ms format b9999.0 heading 'AVERAGE|WAIT|(ms)'
col MAX_WAIT_MS format b999999 heading 'MAX|WAIT|(ms)'
COLUMN   module HEADING  'MODULE' FORMAT   a34 trunc
rem

rem

set pause off;

select e.inst_id, e.event,
       sum(e.total_waits) total_waits,
  sum(e.time_waited_micro) time_waited,
       sum(e.time_waited_micro) / sum(e.total_waits) / 1000 avg_wait_ms
  from gv$session_event e, gv$session s
where s.sid -0 = e.sid
 and e.event in ('db file sequential read','db file scattered read')
  and s.username <> 'VBL'
  and s.username is not null
  and s.username not like 'TMAN%'
--  AND s.USERNAME <> 'ORADBA'
 and e.total_waits > 1000
group by e.inst_id, e.event
;



select e.inst_id, s.sid, e.event, s.module,
       sum(e.total_waits) total_waits,
  sum(e.time_waited_micro) time_waited,
       sum(e.time_waited_micro) / sum(e.total_waits) / 1000 avg_wait_ms
  from gv$session_event e, gv$session s
where s.sid -0 = e.sid
 and e.event in ('db file sequential read','db file scattered read')
  and s.username <> 'VBL'
  and s.username is not null
  and s.username not like 'TMAN%'
--  AND s.USERNAME <> 'ORADBA'
 and e.total_waits > 1000
group by e.inst_id, s.sid, e.event, s.module
order by e.inst_id, e.event, s.module
;

set pause on;

rem

Usage

SQL> @gvio.sql

Required Privileges

• SELECT on GV$SESSION_EVENT
• SELECT on GV$SESSION

Sample Output

Instance-Level I/O Summary

I/O Times

INST_ID EVENT                        TOTAL WAITS     TIME WAITED  AVERAGE WAIT (ms)
------- ---------------------------- ----------- --------------- -----------------
      1 db file sequential read        5,234,567      45,678,901              8.7
      1 db file scattered read         1,456,789      23,456,789             16.1
      2 db file sequential read        4,987,432      52,345,678             10.5
      2 db file scattered read         1,234,567      19,876,543             16.1
      3 db file sequential read        5,123,456      41,234,567              8.0
      3 db file scattered read         1,345,678      21,345,678             15.9

Session-Level I/O Detail

INST_ID   SESS SID EVENT                        MODULE                             TOTAL WAITS TIME WAITED AVG WAIT (ms)
------- --------- ---------------------------- ---------------------------------- ----------- ----------- -------------
      1       123 db file sequential read      MYAPP.EXE                               45,678   1,234,567          27.0
      1       124 db file sequential read      BATCH_JOB.EXE                          123,456   2,345,678          19.0
      1       125 db file scattered read       REPORTS.EXE                             67,890   1,456,789          21.5
      2       256 db file sequential read      MYAPP.EXE                               78,901   1,578,902          20.0
      2       257 db file scattered read       ETL_PROCESS.EXE                        234,567   4,567,890          19.5

Key Output Columns

Instance Summary

• INST_ID: RAC instance number
• EVENT: Type of I/O wait event
• TOTAL WAITS: Total number of I/O operations
• TIME WAITED: Total wait time in microseconds
• AVERAGE WAIT (ms): Average wait time per I/O operation in milliseconds

Session Detail

• SESS SID: Session identifier
• MODULE: Application module name
• Additional columns: Same metrics as instance summary but per session

Understanding I/O Events

db file sequential read

• Purpose: Single block reads (index lookups, ROWID access)
• Typical Operations: Index range scans, primary key lookups
• Expected Performance: 5-15ms for disk storage, 1-5ms for SSD

db file scattered read

• Purpose: Multi-block reads (full table scans, fast full index scans)
• Typical Operations: Table scans, bulk operations
• Expected Performance: 10-30ms for disk storage, 5-15ms for SSD

Performance Analysis

Baseline Performance Targets

• Excellent: < 5ms average for sequential reads
• Good: 5-10ms average for sequential reads
• Acceptable: 10-20ms average for sequential reads
• Poor: > 20ms average for sequential reads

Cross-Instance Comparison

• Balanced Performance: Similar wait times across instances
• Instance Imbalance: Significant variation may indicate storage issues
• Workload Distribution: Compare total waits across instances

Common Performance Issues

High Sequential Read Times

• Cause: Storage performance bottleneck
• Investigation: Check storage I/O metrics, disk queue lengths
• Solution: Upgrade storage, optimize I/O subsystem

High Scattered Read Times

• Cause: Poor multi-block read performance
• Investigation: Check DB_FILE_MULTIBLOCK_READ_COUNT parameter
• Solution: Tune MBRC, consider SSD storage

Instance Imbalance

• Cause: Uneven storage configuration or workload
• Investigation: Check storage layout, connection affinity
• Solution: Balance storage, review application connection logic

Advanced Analysis

Compare with System-Level Events

-- System-level I/O events for comparison
SELECT inst_id, event, total_waits, time_waited_micro,
       time_waited_micro/total_waits/1000 avg_wait_ms
FROM gv$system_event
WHERE event IN ('db file sequential read', 'db file scattered read')
ORDER BY inst_id, event;

Identify Heavy I/O Sessions

-- Find sessions with highest I/O wait times
SELECT inst_id, sid, username, module,
       SUM(total_waits) total_waits,
       SUM(time_waited_micro)/1000000 total_wait_secs
FROM gv$session s, gv$session_event e
WHERE s.sid = e.sid
AND s.inst_id = e.inst_id
AND e.event IN ('db file sequential read', 'db file scattered read')
AND s.username IS NOT NULL
GROUP BY inst_id, sid, username, module
HAVING SUM(time_waited_micro) > 60000000  -- More than 60 seconds
ORDER BY total_wait_secs DESC;

Storage Hotspot Analysis

-- Identify files with highest I/O activity
SELECT f.inst_id, f.tablespace_name, f.file_name,
       SUM(f.phyrds) physical_reads,
       SUM(f.phywrts) physical_writes,
       SUM(f.readtim) read_time_cs,
       ROUND(SUM(f.readtim)/SUM(f.phyrds)*10, 2) avg_read_time_ms
FROM gv$filestat f, gv$datafile d
WHERE f.file# = d.file#
AND f.inst_id = d.inst_id
GROUP BY f.inst_id, f.tablespace_name, f.file_name
HAVING SUM(f.phyrds) > 1000
ORDER BY avg_read_time_ms DESC;

RAC-Specific Considerations

Cross-Instance Performance

• Load Balancing: Check if I/O load is evenly distributed
• Storage Affinity: Verify similar storage access patterns
• Network Impact: Consider interconnect effects on storage

Cluster-Wide Optimization

• Shared Storage: Ensure consistent performance across nodes
• Cache Fusion: Monitor for excessive block transfers
• Instance Affinity: Consider storage-aware connection routing

Troubleshooting High I/O Waits

Immediate Actions

1. Check Storage Metrics: Disk utilization, queue depth, response time
2. Review Recent Changes: Application deployments, storage changes
3. Identify Hot Files: Use V$FILESTAT to find busy files

Long-term Solutions

1. Storage Upgrade: Consider SSD or faster storage tiers
2. I/O Optimization: Tune multiblock read count, implement compression
3. Application Tuning: Optimize SQL, improve indexing strategies

Integration with Other Metrics

Combine with Wait Event Analysis

-- Compare I/O waits with other top wait events
SELECT inst_id, event, total_waits,
       time_waited_micro/1000000 time_waited_secs,
       ROUND(time_waited_micro/total_waits/1000, 2) avg_wait_ms
FROM gv$system_event
WHERE time_waited_micro > 60000000  -- More than 60 seconds total
ORDER BY time_waited_secs DESC;

Monitor Trends Over Time

-- Create trending table for I/O performance
CREATE TABLE io_performance_history (
    check_time DATE,
    inst_id NUMBER,
    event VARCHAR2(64),
    total_waits NUMBER,
    avg_wait_ms NUMBER
);

-- Insert current values
INSERT INTO io_performance_history
SELECT SYSDATE, inst_id, event,
       SUM(total_waits),
       SUM(time_waited_micro)/SUM(total_waits)/1000
FROM gv$session_event
WHERE event IN ('db file sequential read', 'db file scattered read')
GROUP BY inst_id, event;

Best Practices

1. Regular Monitoring

   • Run during peak business hours
   • Compare performance across time periods
   • Track trends for capacity planning

2. Threshold Management

   • Establish baseline performance metrics
   • Set alerts for degraded performance
   • Document expected performance ranges

3. Correlation Analysis

   • Compare with storage metrics
   • Correlate with application activity
   • Review alongside SQL performance

Related Scripts

• System I/O Analysis
• File I/O Statistics
• Session Wait Analysis