Skip to content
Oracle DBA Scripts & Database Utilities

Session Direct Path I/O Statistics (vsessiod.sql)

What This Script Does

Section titled “What This Script Does”

This script provides comprehensive I/O statistics for database sessions, showing:

  • Logical reads (buffer cache access)
  • Physical reads (disk reads)
  • Direct path operations (bypassing buffer cache)
  • Both read and write operations

Script

Section titled “Script”
rem  vsessiod.sql
rem
ttitle 'Session Direct Path IO'
rem
col sid format 990 heading 'SID'
col db_block_gets format b999,999,999 heading 'DB|BLOCK|GETS'
col consistent_gets format b999,999,999,999 heading 'CONSISTENT|GETS'
col logical_reads format 999,999,999,999 heading 'LOGICAL|READS'
col physical_reads format 999,999,999,999 heading 'PHYSICAL|READS'
col physical_reads_direct format 999,999,999,999 heading 'PHYSICAL|READS|DIRECT'
col physical_writes format 999,999,999,999 heading 'PHYSICAL|WRITES'
col physical_writes_direct format 999,999,999,999 heading 'PHYSICAL|WRITES|DIRECT'
rem
break on sid
rem
select
       s.sid,
       sum(decode(n.name, 'consistent gets', s.value, 0)) consistent_gets,
       sum(decode(n.name, 'db block gets', s.value, 0)) db_block_gets,
       sum(decode(n.name, 'session logical reads', s.value, 0)) logical_reads,
       sum(decode(n.name, 'physical reads', s.value, 0)) physical_reads,
       sum(decode(n.name, 'physical reads direct', s.value, 0)) physical_reads_direct,
       sum(decode(n.name, 'physical writes', s.value, 0)) physical_writes,
       sum(decode(n.name, 'physical writes direct', s.value, 0)) physical_writes_direct
  from v$sesstat s,
       v$statname n
 where n.statistic# = s.statistic#
   and s.value <> 0
   and s.sid like nvl('&sid', '%')
   and (n.name like 'phy%' or n.name in ('consistent gets','db block gets','session logical reads'))
 group by sid
 order by sid;

Usage

Section titled “Usage”
SQL> @vsessiod.sql
Enter value for sid: 156

Or for all sessions:

SQL> @vsessiod.sql
Enter value for sid: %

Parameters

Section titled “Parameters”
  • sid: Session ID to analyze (use % for all sessions with activity)

Required Privileges

Section titled “Required Privileges”
  • SELECT on V$SESSTAT
  • SELECT on V$STATNAME

Sample Output

Section titled “Sample Output”
Session Direct Path IO


         CONSISTENT              LOGICAL        PHYSICAL        PHYSICAL        PHYSICAL        PHYSICAL
SID           GETS     GETS        READS           READS           READS          WRITES          WRITES
                                                                   DIRECT                          DIRECT
--- -------------- -------- -------------- -------------- -------------- -------------- --------------
156    123,456,789   12,345    123,469,134         45,678         23,456         12,345          8,765
234     45,678,901    5,678     45,684,579        234,567        234,567              0              0
512      1,234,567      234      1,234,801          1,234              0            567              0

Key Output Columns

Section titled “Key Output Columns”
  • SID: Session identifier
  • CONSISTENT GETS: Blocks read in consistent mode (queries)
  • DB BLOCK GETS: Blocks read in current mode (updates)
  • LOGICAL READS: Total buffer cache reads (consistent + db block gets)
  • PHYSICAL READS: Total blocks read from disk
  • PHYSICAL READS DIRECT: Blocks read bypassing buffer cache
  • PHYSICAL WRITES: Total blocks written to disk
  • PHYSICAL WRITES DIRECT: Blocks written bypassing buffer cache

Understanding the Metrics

Section titled “Understanding the Metrics”

Direct Path Operations

Section titled “Direct Path Operations”

Direct path operations bypass the buffer cache for:

  • Parallel query operations
  • Large table scans (beyond small table threshold)
  • Temporary segment operations
  • Direct path loads

Performance Indicators

Section titled “Performance Indicators”

  1. High Direct Path Reads

    • May indicate parallel queries
    • Could suggest large full table scans
    • Check if intentional or needs tuning

  2. Buffer Cache Efficiency

    • Compare logical reads to physical reads
    • High ratio indicates good cache usage
    • Low ratio suggests cache misses

  3. Write Patterns

    • Direct writes often from sorting
    • High writes may indicate temp usage
    • Monitor for temp tablespace issues

Common Use Cases

Section titled “Common Use Cases”

  1. Performance Troubleshooting

    • Identify sessions with high I/O
    • Find direct path operations
    • Analyze buffer cache efficiency

  2. Resource Usage Analysis

    • Track heavy I/O consumers
    • Plan for I/O capacity
    • Validate parallel execution

  3. Tuning Opportunities

    • Sessions with high physical reads
    • Excessive temp segment usage
    • Candidates for optimization

Tuning Recommendations

Section titled “Tuning Recommendations”

  1. High Direct Path Reads

    • Verify parallel degree settings
    • Check for missing indexes
    • Consider partitioning strategy

  2. High Physical Reads

    • Increase buffer cache if needed
    • Optimize SQL statements
    • Review execution plans

  3. High Direct Path Writes

    • Check temp tablespace sizing
    • Review sort operations
    • Consider PGA tuning
Section titled “Related Scripts”