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 session-level I/O performance analysis by:

• Displaying I/O statistics for each active session
• Calculating logical-to-physical read ratios for efficiency assessment
• Showing block modification activity per session
• Supporting filtering by OS user and database username
• Computing totals across all sessions for aggregate analysis

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

SQL> @vsessio.sql
Enter value for osuser: %
Enter value for username: SCOTT

Parameters

• osuser: Operating system username pattern (% for all users)
• username: Database username pattern (% for all users, specific name for single user)

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
----- ------------ ---------- ------- ---------- -------- ---- --------- ----------
  123 SCOTT        jsmith      45,678    234,567    2,345   119    12,345      5,678
  124 HR_USER      mjohnson    23,456    156,789    1,234   146     8,901      3,456
  125 SALES_APP    appuser     78,901    345,678    4,567   93    23,456     12,345
  126 BATCH_USER   batchjob   234,567  1,234,567   12,345  119    45,678     23,456
  127 REPORTS      rptuser     12,345     89,012      891   114     2,345      1,234

                              ------- ---------- -------- ---- --------- ----------
sum                           394,947  2,060,613   21,382  116    92,725     46,169

Key Output Columns

Session Identification

• SID: Session identifier
• USERNAME: Database username
• OSUSER: Operating system username

I/O Metrics

• BLOCK GETS: Current mode block reads (for modifications)
• CONSISTENT GETS: Consistent mode block reads (for queries)
• PHYSICAL READS: Actual disk reads performed
• LOG/PHY: Logical to physical read ratio
• BLOCK CHANGES: Number of blocks modified
• CONSISTENT CHANGES: Consistent read changes (usually 0)

Understanding I/O Metrics

Logical vs Physical I/O

• Logical I/O: Memory buffer reads (block gets + consistent gets)
• Physical I/O: Actual disk reads when data not in buffer cache
• Higher LOG/PHY ratio: Better buffer cache efficiency

Block Access Types

• Block Gets: Reads for modification (current mode)
• Consistent Gets: Reads for queries (consistent mode)
• Block Changes: Actual modifications made to blocks

Performance Indicators

• High Physical Reads: Potential I/O bottleneck or cache misses
• Low LOG/PHY Ratio: Poor buffer cache hit ratio
• High Block Changes: Heavy DML activity

Performance Analysis

I/O Efficiency Assessment

-- Calculate buffer hit ratios by session
SELECT sid, username,
       block_gets + consistent_gets logical_reads,
       physical_reads,
       ROUND((1 - physical_reads/GREATEST(block_gets + consistent_gets, 1)) * 100, 2) hit_ratio_pct,
       ROUND((block_gets + consistent_gets)/GREATEST(physical_reads, 1), 2) log_phy_ratio
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
AND s.username IS NOT NULL
AND (block_gets + consistent_gets) > 0
ORDER BY logical_reads DESC;

High I/O Sessions

-- Identify sessions with highest I/O activity
SELECT sid, username, osuser,
       (block_gets + consistent_gets) total_logical_io,
       physical_reads,
       block_changes,
       ROUND(physical_reads * 100 / GREATEST(block_gets + consistent_gets, 1), 2) miss_ratio_pct
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
AND s.username IS NOT NULL
ORDER BY total_logical_io DESC;

Common Use Cases

1. Session Performance Monitoring

   • Identify high I/O consuming sessions
   • Monitor buffer cache efficiency by user
   • Track DML activity levels

2. Application Analysis

   • Compare I/O patterns between applications
   • Identify inefficient data access patterns
   • Monitor batch job performance

3. Capacity Planning

   • Understand I/O workload distribution
   • Plan buffer cache sizing
   • Identify peak I/O periods

Performance Tuning Insights

High Physical I/O Sessions

For sessions with high physical reads:

1. Review SQL Execution Plans: Check for full table scans
2. Analyze Indexing: Ensure proper indexes exist
3. Check Buffer Cache: May need larger buffer pool

Low Hit Ratios

For sessions with poor buffer hit ratios:

1. SQL Optimization: Review query efficiency
2. Data Access Patterns: Check for sequential vs random access
3. Application Design: Consider connection pooling or caching

High Block Changes

For sessions with extensive modifications:

1. Batch Optimization: Consider bulk operations
2. Transaction Sizing: Review commit frequency
3. Indexing Impact: Monitor index maintenance overhead

Session I/O Monitoring

Real-time I/O Tracking

-- Monitor I/O changes over time
CREATE TABLE session_io_history (
    check_time DATE,
    sid NUMBER,
    username VARCHAR2(128),
    logical_reads NUMBER,
    physical_reads NUMBER,
    block_changes NUMBER
);

-- Capture current snapshot
INSERT INTO session_io_history
SELECT SYSDATE, s.sid, s.username,
       i.block_gets + i.consistent_gets,
       i.physical_reads,
       i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
AND s.username IS NOT NULL;

I/O Rate Analysis

-- Calculate I/O rates (requires historical data)
SELECT sid, username,
       (current_logical - prev_logical) /
       ((current_time - prev_time) * 86400) logical_io_per_sec,
       (current_physical - prev_physical) /
       ((current_time - prev_time) * 86400) physical_io_per_sec
FROM session_io_trending_view
WHERE time_interval_minutes > 0;

Advanced Analysis

Application I/O Patterns

-- Analyze I/O by application module
SELECT s.module, COUNT(*) session_count,
       SUM(i.block_gets + i.consistent_gets) total_logical_io,
       SUM(i.physical_reads) total_physical_io,
       AVG(i.block_gets + i.consistent_gets) avg_logical_per_session
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
AND s.username IS NOT NULL
AND s.module IS NOT NULL
GROUP BY s.module
ORDER BY total_logical_io DESC;

User I/O Summary

-- Summarize I/O by database user
SELECT username,
       COUNT(*) active_sessions,
       SUM(block_gets + consistent_gets) total_logical_reads,
       SUM(physical_reads) total_physical_reads,
       ROUND(AVG(block_gets + consistent_gets), 0) avg_logical_per_session,
       ROUND((1 - SUM(physical_reads)/GREATEST(SUM(block_gets + consistent_gets), 1)) * 100, 2) overall_hit_ratio
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
AND s.username IS NOT NULL
GROUP BY username
ORDER BY total_logical_reads DESC;

Buffer Cache Analysis

Session Impact on Buffer Cache

-- Estimate session impact on buffer cache
SELECT sid, username,
       ROUND((block_gets + consistent_gets) * 8192 / 1024 / 1024, 2) data_touched_mb,
       ROUND(physical_reads * 8192 / 1024 / 1024, 2) data_read_from_disk_mb,
       ROUND((1 - physical_reads/GREATEST(block_gets + consistent_gets, 1)) * 100, 2) personal_hit_ratio
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
AND s.username IS NOT NULL
AND (block_gets + consistent_gets) > 0
ORDER BY data_touched_mb DESC;

Troubleshooting Performance Issues

Identify Problematic Sessions

• High Physical I/O: Sessions causing cache misses
• Low Hit Ratios: Inefficient data access patterns
• High Block Changes: Heavy DML impacting performance

Investigation Steps

1. Review Current SQL: Check V$SQL for active statements
2. Analyze Execution Plans: Look for inefficient access paths
3. Check Object Statistics: Verify optimizer statistics currency

Best Practices

1. Regular Monitoring

   • Check during peak business hours
   • Compare patterns across different time periods
   • Track trends for capacity planning

2. Performance Baselines

   • Establish normal I/O patterns by application
   • Document expected hit ratios for different workloads
   • Monitor for deviations from baseline

3. Optimization Priorities

   • Focus on highest I/O consuming sessions first
   • Address low hit ratios through SQL tuning
   • Consider infrastructure improvements for high I/O loads

Related Scripts

• Global Session Analysis
• I/O Wait Analysis
• Session Wait Events