Session I/O Statistics (vsessio.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 detailed I/O statistics for database sessions, showing:

• Logical reads (block gets + consistent gets)
• Physical reads from disk
• Buffer cache efficiency (logical/physical ratio)
• Block changes for DML activity
• Totals across all sessions

The Script

rem  vsessio.sql
rem
ttitle 'Session I/O'
rem
set linesize 100
rem
col sid format 9999 heading 'SID'
col username format a12 heading 'USERNAME'
col osuser format a10 heading 'OSUSER'
col block_gets format b99999999 heading 'BLOCK|GETS'
col consistent_gets format b999999999 heading 'CONSISTENT|GETS'
col physical_reads format b9999999 heading 'PHYSICAL|READS'
col block_changes format b99999999 heading 'BLOCK|CHANGES'
col consistent_changes format b999999999 heading 'CONSISTENT|CHANGES'
col hit_ratio format b9.000 heading 'HIT|RATIO'
col log_phy format 9999 heading  'LOG/|PHY'
rem
break on report
compute sum -
     of block_gets consistent_gets physical_reads block_changes consistent_changes -
     on report
rem
select i.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
  from v$session s, v$sess_io i
 where i.sid = s.sid
   and (upper(s.osuser) like nvl(upper('&osuser'),'%') or s.osuser is null)
   and (s.username like nvl(upper('&username'),'%') or s.username is null)
 order by i.sid;

Usage

-- Basic usage (all sessions)
@vsessio.sql
-- Enter: Press Enter for both prompts

-- Filter by OS user
@vsessio.sql
-- Enter: john% for osuser
-- Enter: Press Enter for username

-- Filter by database user
@vsessio.sql
-- Enter: Press Enter for osuser
-- Enter: HR for username

Parameters

The script prompts for:

• &osuser - OS username pattern (optional, % for all)
• &username - Database username pattern (optional, % for all)

Required Privileges

SELECT ON V$SESSION
SELECT ON V$SESS_IO

Sample Output

Session I/O

 SID USERNAME     OSUSER          BLOCK   CONSISTENT   PHYSICAL  LOG/       BLOCK   CONSISTENT
                                   GETS         GETS      READS  PHY      CHANGES      CHANGES
---- ------------ ---------- ---------- ------------ ---------- ----- ----------- -------------
 125 HR           oracle          12,456      234,567      1,234   199       5,678             0
 248 APP_USER     appserver      456,789    8,901,234     45,678   203     123,456        12,345
 367 BATCH_USER   batchsvr     1,234,567   12,345,678    567,890    24     890,123             0
 512              oracle           1,234       12,345          0  9999           0             0
                             ---------- ------------ ----------       ----------- -------------
sum                           1,704,046   21,493,824    614,802     1,019,257        12,345

Key Output Columns

• SID - Session identifier
• USERNAME - Database username (null for background processes)
• OSUSER - Operating system username
• BLOCK GETS - Current mode block reads (for DML)
• CONSISTENT GETS - Consistent read blocks (for queries)
• PHYSICAL READS - Blocks read from disk
• LOG/PHY - Logical to physical read ratio (cache efficiency)
• BLOCK CHANGES - Blocks changed (DML activity)
• CONSISTENT CHANGES - Consistent changes (usually 0)

Understanding the Metrics

Buffer Cache Efficiency:

• LOG/PHY > 100: Good cache hit ratio
• LOG/PHY < 20: Poor cache efficiency, too much physical I/O
• LOG/PHY = 9999: No physical reads (100% cache hits)

I/O Patterns:

• High BLOCK GETS: Session doing lots of DML
• High CONSISTENT GETS: Session doing large queries
• High PHYSICAL READS: Poor cache efficiency or large scans
• High BLOCK CHANGES: Heavy DML activity

Common Use Cases

Find I/O Intensive Sessions

@vsessio.sql
-- Look for sessions with high PHYSICAL READS
-- These are candidates for tuning

Monitor Batch Jobs

@vsessio.sql
-- Enter: batch% for osuser
-- Monitor I/O patterns during batch runs

Cache Hit Ratio Analysis

@vsessio.sql
-- Check LOG/PHY ratio
-- Low values indicate cache problems

Performance Tuning Tips

High Physical Reads:

1. Check for missing indexes
2. Review execution plans
3. Consider increasing buffer cache
4. Look for full table scans

Low Cache Hit Ratio:

1. Analyze SQL for tuning opportunities
2. Check if buffer cache is too small
3. Look for large table scans
4. Consider partitioning large tables

Related Scripts

• Active Sessions - Current session activity
• Top SQL by User - Find problematic SQL
• Buffer Pool Statistics - Buffer cache analysis