My Session I/O Analysis (vmyio.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 statistics specifically for the current user’s session, providing detailed information about block gets, consistent gets, physical reads, and block changes. It’s essential for understanding the I/O characteristics of your own session’s work, analyzing query efficiency, and troubleshooting performance issues from a session-specific perspective. The script shows cache efficiency and I/O patterns for the logged-in user’s activities.

The Script

rem  vsessio.sql
rem
ttitle 'Session I/O'
rem
set linesize 132
rem
col sid format a12 heading 'SID:SERIAL'
col username format a12 heading 'USERNAME'
col osuser format a10 heading 'OSUSER'
col block_gets format b999,999,999 heading 'BLOCK|GETS'
col consistent_gets format b999,999,999,999 heading 'CONSISTENT|GETS'
col physical_reads format b999,999,999 heading 'PHYSICAL|READS'
col block_changes format b99,999,999 heading 'BLOCK|CHANGES'
col consistent_changes format b999,999,999 heading 'CONSISTENT|CHANGES'
col hit_ratio format b9.000 heading 'HIT|RATIO'
col log_phy format 999999 heading  'LOG/|PHY'
col module format a15
rem
break on report
compute sum -
     of block_gets consistent_gets physical_reads block_changes consistent_changes -
     on report
rem
select i.sid ||':'|| s.serial# sid, s.username, s.osuser,
       block_gets, consistent_gets,
       physical_reads,
       (block_gets + consistent_gets)
           / decode(physical_reads, 0, 1, physical_reads) log_phy,
       -- 1 - (physical_reads/decode(block_gets + consistent_gets, 0, 1,
            -- block_gets + consistent_gets)) hit_ratio,
       block_changes, consistent_changes
, module    -- added sep
  from v$session s,
       v$sess_io i
 where i.sid = s.sid
   and s.audsid = userenv('sessionid')
 order by consistent_gets + physical_reads desc    -- i.sid;
 --
-- col sid clear

Key Features

• Current Session Focus: Analyzes only the current user’s session I/O
• Comprehensive I/O Metrics: Shows all major I/O statistics
• Cache Efficiency Analysis: Calculates logical to physical read ratios
• Block Change Tracking: Shows data modification activity
• Module Information: Displays the application module context
• Summary Totals: Provides aggregated I/O statistics
• Personal Performance Analysis: Focus on individual session performance

Usage

@vmyio.sql

No parameters required - automatically analyzes the current session.

Required Privileges

SELECT on V$SESSION
SELECT on V$SESS_IO

Sample Output

                                              Session I/O

SID:SERIAL   USERNAME     OSUSER        BLOCK     CONSISTENT  PHYSICAL LOG/      BLOCK   CONSISTENT MODULE
                                        GETS          GETS     READS   PHY    CHANGES      CHANGES
------------ ------------ ---------- --------- ------------- --------- ------ --------- ----------- ---------------
123:45678    SCOTT        jsmith        12,345    9,876,543    45,678    218    23,456      145,789 SQL*Plus

                                      --------- ------------- --------- ------ --------- -----------
sum                                      12,345    9,876,543    45,678           23,456      145,789

Key Output Columns

• SID:SERIAL: Session ID and serial number combination
• USERNAME: Database username
• OSUSER: Operating system username
• BLOCK GETS: Number of blocks read from buffer cache in current mode
• CONSISTENT GETS: Number of blocks read for consistent read operations
• PHYSICAL READS: Number of blocks read from disk
• LOG/PHY: Ratio of logical reads (block gets + consistent gets) to physical reads
• BLOCK CHANGES: Number of blocks modified
• CONSISTENT CHANGES: Number of consistent read blocks changed
• MODULE: Application module name

Understanding I/O Metrics

Logical vs Physical Reads

Block Gets (Current Mode)

-- Block gets represent:
-- Blocks read for modification (current mode)
-- Most recent version of blocks
-- Typically for INSERT, UPDATE, DELETE operations
-- Index maintenance operations

Consistent Gets (Consistent Read)

-- Consistent gets represent:
-- Blocks read for query consistency
-- Point-in-time consistent data
-- Typically for SELECT operations
-- Undo-based reconstructed blocks

Physical Reads

-- Physical reads indicate:
-- Blocks not found in buffer cache
-- Disk I/O operations required
-- Cache misses for required data
-- Performance impact operations

Cache Efficiency Analysis

Logical to Physical Ratio (LOG/PHY)

-- Ratio interpretation:
-- High ratio (>10): Good cache utilization
-- Low ratio (<5): Poor cache efficiency
-- Ratio of 1: All reads from disk
-- Very high ratio: Excellent cache performance

Cache Hit Rate Calculation

-- Cache hit rate formula:
-- Hit Rate = 1 - (Physical Reads / (Block Gets + Consistent Gets))
-- High hit rate (>90%): Good performance
-- Low hit rate (<80%): Potential tuning needed

Common Use Cases

1. Personal Performance Analysis

   • Analyze your own query performance
   • Understand I/O patterns of current work
   • Identify inefficient operations
   • Support individual query tuning

2. Session Troubleshooting

   • Diagnose slow-running operations
   • Identify I/O-intensive activities
   • Understand cache utilization
   • Support performance optimization

3. Query Optimization

   • Measure query I/O efficiency
   • Compare before/after optimization results
   • Validate index usage effectiveness
   • Support SQL tuning efforts

4. Application Development

   • Understand application I/O characteristics
   • Validate development code efficiency
   • Support testing and debugging
   • Analyze prototype performance

Advanced Analysis

I/O Pattern Analysis

Read-Heavy vs. Write-Heavy Operations

-- Operation type indicators:
-- High consistent gets: SELECT-heavy operations
-- High block gets: DML-heavy operations
-- High block changes: UPDATE/INSERT intensive
-- Balanced pattern: Mixed workload

Data Access Efficiency

-- Efficiency indicators:
-- Low physical reads with high logical reads: Good
-- High physical reads: Poor cache utilization
-- Very high logical reads: Potential inefficiency
-- Optimization opportunities identification

Cache Performance Assessment

Buffer Cache Utilization

-- Cache effectiveness factors:
-- Data access patterns
-- Working set size vs. cache size
-- Query selectivity
-- Index utilization effectiveness

Performance Optimization Opportunities

-- Optimization areas:
-- Query rewriting for better cache utilization
-- Index optimization for reduced I/O
-- Data access pattern improvement
-- Buffer cache sizing considerations

Application Module Analysis

Module-Specific Performance

-- Module context analysis:
-- SQL*Plus: Interactive operations
-- Application modules: Programmatic access
-- Batch processes: Bulk operations
-- Tools: Utility and maintenance operations

Real-Time Monitoring Applications

Session Performance Tracking

Before/After Comparisons

-- Performance comparison workflow:
-- 1. Run script before operation
-- 2. Execute target SQL or procedure
-- 3. Run script again after operation
-- 4. Compare I/O metrics for impact assessment

Cumulative Analysis

-- Session-level cumulative metrics:
-- Total I/O since session start
-- Incremental I/O for specific operations
-- Performance trend during session
-- Resource consumption patterns

Query Tuning Support

Baseline Establishment

-- Tuning workflow support:
-- Establish baseline I/O metrics
-- Test optimization changes
-- Measure improvement or degradation
-- Validate tuning effectiveness

Index Impact Assessment

-- Index optimization validation:
-- Measure I/O before index creation
-- Execute queries with new indexes
-- Compare I/O reduction achieved
-- Validate optimization benefits

Integration with Other Analysis

Correlation with Wait Events

I/O Wait Correlation

-- Combine with wait event analysis:
-- Physical reads correlation with I/O waits
-- Buffer cache efficiency vs. wait times
-- Performance bottleneck identification
-- Root cause analysis support

Session Activity Correlation

-- Activity-based analysis:
-- SQL execution correlation
-- Time-based I/O patterns
-- Resource consumption trends
-- Performance impact assessment

SQL Statement Analysis

Statement-Level I/O

-- Correlate with specific SQL:
-- Individual query I/O consumption
-- Cumulative effect of multiple queries
-- Application operation efficiency
-- Optimization priority identification

Best Practices

Regular Monitoring

Session Health Checks

-- Regular monitoring schedule:
-- Before major operations
-- After significant changes
-- During performance troubleshooting
-- As part of tuning methodology

Performance Baselines

-- Baseline establishment:
-- Normal operation I/O patterns
-- Expected efficiency ratios
-- Typical cache utilization
-- Performance benchmarks

Optimization Methodology

Systematic Approach

-- Optimization process:
-- Measure current performance
-- Implement targeted changes
-- Validate improvement
-- Document results and lessons learned

Impact Assessment

-- Change impact evaluation:
-- I/O reduction quantification
-- Performance improvement measurement
-- Resource utilization optimization
-- User experience enhancement

Troubleshooting Applications

High I/O Issues

Excessive Physical Reads

-- High physical read investigation:
-- Query plan analysis for full table scans
-- Index usage evaluation
-- Buffer cache sizing assessment
-- Data access pattern optimization

Poor Cache Utilization

-- Cache efficiency improvement:
-- Query optimization for cache-friendly access
-- Index strategy enhancement
-- Buffer cache parameter tuning
-- Workload pattern analysis

Performance Degradation

I/O Efficiency Decline

-- Performance degradation analysis:
-- Compare with historical baselines
-- Identify efficiency ratio changes
-- Analyze access pattern modifications
-- Plan optimization interventions

Resource Contention

-- Contention impact assessment:
-- I/O subsystem pressure
-- Buffer cache competition
-- Resource allocation optimization
-- System-level tuning considerations

Related Scripts

• vsessio.sql - All session I/O analysis
• vsession.sql - Session information
• vfile.sql - File I/O statistics
• vio2.sql - System I/O analysis

Development and Testing Support

Code Development

Development Workflow Integration

-- Development process support:
-- Baseline I/O measurement
-- Code change impact assessment
-- Performance regression detection
-- Optimization validation

Testing Support

-- Testing process integration:
-- Unit test performance validation
-- Load test I/O characteristic analysis
-- Performance test baseline establishment
-- Regression test performance verification

Debugging Applications

Performance Issue Diagnosis

-- Debugging workflow:
-- Identify high I/O operations
-- Analyze inefficient access patterns
-- Support root cause identification
-- Guide optimization efforts

Optimization Validation

-- Validation process:
-- Measure optimization effectiveness
-- Quantify performance improvements
-- Validate cache utilization enhancement
-- Document optimization results

Summary

This script is essential for:

1. Personal Performance Analysis - Understanding your own session’s I/O characteristics and efficiency
2. Query Optimization - Supporting individual SQL statement tuning and optimization efforts
3. Session Troubleshooting - Diagnosing and resolving session-specific performance issues
4. Development Support - Analyzing application code performance and efficiency during development
5. Testing and Validation - Measuring and validating performance improvements and optimization effectiveness