File I/O Statistics with Timing (vfile.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 file-level I/O analysis by:

• Combining data file and temp file statistics
• Calculating average read and write response times
• Showing block-to-operation ratios
• Identifying the most active files
• Including both current data files and temporary files

Script

rem  vfile.sql
rem
set linesize 150
rem
ttitle 'File Statistics'
rem
col file# format 9999 heading 'ID'
col fname format a35 heading 'FILE NAME'
col phyrds format b999,999,999 heading 'PHYSICAL|READS'
col phyblkrd like phyrds heading 'PHYSICAL|BLOCKS|READ'
col blockreadratio format 990.0 heading 'BLOCK|READ|RATIO'
col phywrts format b9,999,999 heading 'PHYSICAL|WRITES'
col phyblkwrt like phywrts heading 'PHYSICAL|BLOCKS|WRITTEN'
col readrt format b9990.9 heading 'AVG|READ|TIME|(ms)'
col writert format b990.9 heading 'AVG|WRITE|TIME|(ms)'
col totio noprint
rem
break on report
compute sum of phyrds phyblkrd readdiff phywrts phyblkwrt on report
rem
select s.file#,
       phyrds,
       phyblkrd,
       phyblkrd / decode(phyrds,0,1,phyrds) blockreadratio,
       phywrts,
       phyblkwrt,
       trunc((readtim*10)/decode(phyrds,0,null,phyrds),1) readrt,
       readtim,
       -- SINGLEBLKRDTIM,
       trunc((writetim*10)/decode(phywrts,0,null,phywrts),1) writert,
       writetim,
       f.name fname,
       (phyblkrd + phywrts) totio
  from v$dbfile f, v$filestat s
 where s.file# = f.file#
union all
select s.file#,
       phyrds,
       phyblkrd,
       phyblkrd / decode(phyrds,0,1,phyrds) blockreadratio,
       phywrts,
       phyblkwrt,
       trunc((readtim*10)/decode(phyrds,0,null,phyrds),1) readrt,
       readtim,
       -- SINGLEBLKRDTIM,
       trunc((writetim*10)/decode(phywrts,0,null,phywrts),1) writert,
       writetim,
       f.name fname,
       (phyblkrd + phywrts) totio
  from v$tempfile f, v$tempstat s
 where s.file# = f.file#
order by totio desc;
rem
set linesize 80

Usage

SQL> @vfile.sql

Required Privileges

• SELECT on V$DBFILE
• SELECT on V$FILESTAT
• SELECT on V$TEMPFILE
• SELECT on V$TEMPSTAT

Sample Output

File Statistics

                              PHYSICAL PHYSICAL     BLOCK PHYSICAL PHYSICAL      AVG      AVG
   ID                            READS   BLOCKS      READ    WRITES   BLOCKS     READ    WRITE
                                           READ     RATIO            WRITTEN   TIME(ms) TIME(ms) FILE NAME
----- ----------------------------- -------- --------- ------- -------- --------- -------- ---------
    4                    12,345,678 12,890,234     1.0  2,345,678  2,456,789     15.2      8.5 /u01/oradata/prod/users01.dbf
    1                     8,765,432  9,123,456     1.0  1,234,567  1,345,678     12.8      6.2 /u01/oradata/prod/system01.dbf
    3                     5,432,109  5,987,654     1.1    876,543    987,654     18.7      9.1 /u01/oradata/prod/sysaux01.dbf
    2                     3,210,987  3,456,789     1.1    543,210    654,321     14.3      7.8 /u01/oradata/prod/undotbs01.dbf
  201                     1,987,654  2,234,567     1.1    123,456    234,567     25.4     12.3 /u01/oradata/prod/temp01.dbf
                       ----------- ----------          -------- ---------
sum                      31,741,860 33,692,700           5,123,454  5,678,009

Key Output Columns

• ID: File number identifier
• PHYSICAL READS: Number of read operations from file
• PHYSICAL BLOCKS READ: Number of blocks read (may exceed reads for multi-block reads)
• BLOCK READ RATIO: Blocks read per read operation
• PHYSICAL WRITES: Number of write operations to file
• PHYSICAL BLOCKS WRITTEN: Number of blocks written
• AVG READ TIME (ms): Average read response time in milliseconds
• AVG WRITE TIME (ms): Average write response time in milliseconds
• FILE NAME: Full path to the database file

Understanding the Metrics

I/O Performance Thresholds

• Good: Read/Write time < 10ms
• Acceptable: 10-20ms
• Warning: 20-50ms
• Critical: > 50ms

Block Read Ratio Analysis

• 1.0: Single-block reads (index access)
• > 1.0: Multi-block reads (full scans)
• Very high ratios: Large full table scans

File Activity Patterns

• High reads, low writes: Read-heavy files (tables, indexes)
• High writes: Undo segments, temporary space
• Balanced: Mixed workload files

Common Use Cases

1. I/O Performance Analysis

   • Identify slow storage subsystems
   • Find I/O bottlenecks
   • Validate storage performance

2. Capacity Planning

   • Track I/O growth patterns
   • Plan storage expansion
   • Balance I/O across devices

3. Database Tuning

   • Identify hot files for redistribution
   • Find candidates for different storage tiers
   • Optimize file placement

Troubleshooting High I/O Times

Storage Issues

• Check underlying storage performance
• Verify RAID configuration
• Monitor storage queue depths

Database Issues

• Large full table scans
• Missing indexes
• Poor SQL performance

System Issues

• High CPU utilization
• Memory pressure
• Network latency (for NFS/SAN)

Advanced Analysis

Top files by read time:

SELECT file#, name,
       trunc((readtim*10)/decode(phyrds,0,null,phyrds),1) avg_read_ms
FROM v$filestat fs, v$dbfile df
WHERE fs.file# = df.file#
AND phyrds > 0
ORDER BY avg_read_ms DESC;

Files with high multi-block reads:

SELECT file#, name,
       phyblkrd/decode(phyrds,0,1,phyrds) block_ratio
FROM v$filestat fs, v$dbfile df
WHERE fs.file# = df.file#
AND phyrds > 0
AND phyblkrd/decode(phyrds,0,1,phyrds) > 2
ORDER BY block_ratio DESC;

Related Scripts

• I/O Statistics by Day
• File I/O Analysis
• System I/O Statistics