Global I/O Analysis for Sequential and Scattered Reads (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 provides comprehensive analysis of I/O wait events across all RAC instances, specifically focusing on db file sequential read and db file scattered read events. It’s essential for identifying I/O performance bottlenecks, analyzing storage subsystem performance, and optimizing database I/O patterns. The script provides both instance-level summaries and session-level details.

The 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

Key Features

• RAC-Aware Analysis: Monitors I/O across all instances in RAC environment
• Dual I/O Event Focus: Analyzes both sequential and scattered read patterns
• Two-Level Reporting: Instance summary and detailed session breakdown
• Performance Filtering: Shows only sessions with significant I/O activity (>1000 waits)
• Precision Timing: Uses microsecond timing data for accurate analysis
• Application Context: Includes module information for session identification

Usage

@gvio.sql

No parameters required - analyzes all instances and active user sessions.

Required Privileges

SELECT on GV$SESSION_EVENT
SELECT on GV$SESSION

Sample Output

Instance-Level Summary

                                               I/O Times

INST_ID EVENT                         TOTAL      TIME              AVERAGE
                                      WAITS     WAITED             WAIT
                                                                   (ms)
------- ---------------------------- ---------- ------------------ -------
      1 db file scattered read        1,234,567  45,678,901,234      37.0
      1 db file sequential read       4,567,890  12,345,678,901       2.7
      2 db file scattered read          987,654  23,456,789,012      23.7
      2 db file sequential read       3,456,789   8,901,234,567       2.6

Session-Level Detail

INST_ID   SESS EVENT                    MODULE                             TOTAL      TIME              AVERAGE
          SID                                                             WAITS     WAITED             WAIT
                                                                                                       (ms)
------- ------ ------------------------ ---------------------------------- ---------- ------------------ -------
      1    145 db file scattered read   MyApp.exe                          567,890  12,345,678,901      21.7
      1    145 db file sequential read  MyApp.exe                          234,567   1,234,567,890       5.3
      1    289 db file scattered read   JDBC Thin Client                   345,678   8,901,234,567      25.8
      1    289 db file sequential read  JDBC Thin Client                   456,789   2,345,678,901       5.1
      2    156 db file scattered read   Reports.exe                        234,567   5,678,901,234      24.2
      2    156 db file sequential read  Reports.exe                        123,456     567,890,123       4.6

Key Output Columns

Instance Summary

• INST_ID: RAC instance number
• EVENT: I/O wait event type
• TOTAL WAITS: Total number of waits across all sessions
• TIME WAITED: Total time waited in microseconds
• AVERAGE WAIT (ms): Average wait time per I/O operation in milliseconds

Session Detail

• SESS SID: Session ID experiencing I/O waits
• MODULE: Application module generating the I/O
• All other columns: Same as instance summary but per session

Understanding I/O Wait Events

db file sequential read

Characteristics

-- Single block reads:
-- Index lookups and navigation
-- Table access by ROWID
-- Primary key access
-- Individual row retrieval

Performance Expectations

-- Good performance: < 5ms average
-- Acceptable: 5-10ms average
-- Poor performance: > 10ms average
-- Critical issues: > 20ms average

db file scattered read

Characteristics

-- Multi-block reads:
-- Full table scans
-- Index fast full scans
-- Large range scans
-- Bulk data operations

Performance Expectations

-- Good performance: < 30ms average
-- Acceptable: 30-50ms average
-- Poor performance: > 50ms average
-- Critical issues: > 100ms average

Common Use Cases

1. I/O Performance Analysis

   • Identify storage subsystem bottlenecks
   • Compare performance across RAC instances
   • Analyze I/O patterns by application
   • Monitor I/O trends over time

2. Storage Optimization

   • Assess storage configuration impact
   • Plan storage upgrades or changes
   • Optimize file placement strategies
   • Evaluate storage tier performance

3. Application Performance Tuning

   • Identify applications with poor I/O patterns
   • Analyze SQL efficiency by application module
   • Plan application optimization efforts
   • Correlate I/O with application performance

4. RAC Performance Monitoring

   • Compare I/O performance across instances
   • Identify instance-specific issues
   • Balance workload distribution
   • Monitor cluster I/O efficiency

Related Scripts

• gvio2.sql - Enhanced I/O analysis with additional metrics
• vfileb.sql - File-level I/O statistics
• random_io.sql - Random I/O analysis
• sequential-io.sql - Sequential I/O analysis

Summary

This script is essential for:

1. I/O Performance Analysis - Understanding database I/O patterns and bottlenecks
2. Storage Optimization - Evaluating and optimizing storage subsystem performance
3. RAC Monitoring - Comparing I/O performance across cluster instances
4. Application Tuning - Identifying applications with poor I/O patterns
5. Capacity Planning - Planning for I/O capacity and performance requirements