File I/O Statistics with Timing (vfileb.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 I/O statistics including timing metrics for both datafiles and tempfiles. It shows physical read/write operations, block counts, and crucially, the average time per I/O operation, helping identify files with poor I/O performance that may indicate storage issues.

Script

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

Usage

-- Run the script in SQL*Plus or SQLcl
@vfileb.sql

-- No parameters required
-- Shows cumulative statistics since instance startup
-- Ordered by highest I/O activity first

Required Privileges

• SELECT on V$DBFILE, V$FILESTAT
• SELECT on V$TEMPFILE, V$TEMPSTAT
• Generally available to most database users

Sample Output

File Statistics

  ID     PHYSICAL   PHYSICAL       READ  PHYSICAL  PHYSICAL  AVG   AVG FILE NAME
           READS     BLOCKS  DIFFERENCE    WRITES    BLOCKS READ WRITE
                       READ                        WRITTEN  TIME  TIME
---- ------------ ---------- ----------- ---------- --------- ----- ----- ---------------------------------------------------
   3   12,345,678 45,678,901  33,333,223  2,345,678 3,456,789  12.5   8.3 /u01/oradata/PROD/users01.dbf
   1    8,901,234 23,456,789  14,555,555  1,234,567 2,345,678   8.2   5.1 /u01/oradata/PROD/system01.dbf
   2    5,678,901 12,345,678   6,666,777    890,123 1,234,567  15.3   9.7 /u01/oradata/PROD/sysaux01.dbf
   4    3,456,789  8,901,234   5,444,445    567,890   890,123  18.9  12.4 /u01/oradata/PROD/undotbs01.dbf
   5      234,567    567,890     333,323     45,678    67,890  22.1  15.3 /u01/oradata/PROD/tools01.dbf
 101    1,234,567  2,345,678   1,111,111    234,567   345,678  10.2   7.8 /u01/oradata/PROD/temp01.dbf
---- ------------ ---------- ----------- ---------- ---------
sum   31,852,726 93,595,970  61,743,244  5,218,503 8,339,725

Key Output Columns

• ID: File number (datafile or tempfile)
• PHYSICAL READS: Number of physical read operations
• PHYSICAL BLOCKS READ: Total blocks read (may be multiblock)
• READ DIFFERENCE: Multiblock read indicator (blocks - reads)
• PHYSICAL WRITES: Number of physical write operations
• PHYSICAL BLOCKS WRITTEN: Total blocks written
• AVG READ TIME: Average read time in centiseconds
• AVG WRITE TIME: Average write time in centiseconds
• FILE NAME: Full path to the file

Understanding I/O Metrics

Read Patterns

• READ DIFFERENCE > 0: Multiblock reads occurring
• High difference: Full table scans or index fast full scans
• Low/zero difference: Single block reads (index access)

Timing Analysis

• AVG READ TIME < 10cs: Good performance (< 100ms)
• AVG READ TIME 10-20cs: Acceptable performance
• AVG READ TIME > 20cs: Potential I/O bottleneck
• AVG WRITE TIME: Usually lower than read times

I/O Distribution

• System/Sysaux: Typically high read activity
• User tablespaces: Application-dependent patterns
• Temp files: Sort/hash join activity
• Undo: Transaction activity indicators

Performance Analysis

Identifying I/O Bottlenecks

1. High average times: Files with >20cs indicate slow storage
2. Uneven distribution: Hot files may need redistribution
3. Temp file activity: High usage indicates sort/join tuning needed
4. Read vs write patterns: Helps understand workload type

Storage Performance Indicators

• Consistent high times: Storage subsystem issues
• Specific file slowness: Possible disk problems
• Time variations: May indicate contention or caching

Common Use Cases

Storage Performance Analysis

-- Identify slow performing files
@vfileb.sql
-- Look for files with high AVG READ/WRITE TIME
-- Investigate storage for those specific files

I/O Distribution Review

-- Analyze I/O hotspots
@vfileb.sql
-- Files with highest PHYSICAL BLOCKS READ
-- Consider spreading I/O load

Capacity Planning

-- Understand I/O patterns
@vfileb.sql
-- Use READ DIFFERENCE to identify scan-heavy files
-- Plan faster storage for high-activity files

Troubleshooting I/O Issues

High Average Read/Write Times

1. Storage verification:

   -- Check OS-level I/O statistics
   -- Verify RAID configuration
   -- Check for disk errors in system logs

2. File redistribution:

   -- Move hot files to faster storage
   ALTER DATABASE MOVE DATAFILE '/slow/path/file.dbf'
   TO '/fast/path/file.dbf';

3. I/O calibration:

   -- Calibrate I/O to understand capabilities
   EXEC DBMS_RESOURCE_MANAGER.CALIBRATE_IO(
     num_physical_disks => 10,
     max_latency => 20,
     max_iops => :max_iops,
     max_mbps => :max_mbps,
     actual_latency => :actual_latency
   );

Multiblock Read Analysis

1. High READ DIFFERENCE values:

   • Indicates full scans
   • Review execution plans
   • Consider indexing strategies

2. Optimize multiblock reads:

   -- Check and adjust multiblock read count
   SHOW PARAMETER db_file_multiblock_read_count;

Tempfile I/O Issues

1. High temp I/O:

   • Sort operations spilling to disk
   • Increase PGA_AGGREGATE_TARGET
   • Tune sort-heavy queries

2. Multiple tempfiles:

   -- Add tempfiles to spread I/O
   ALTER TABLESPACE TEMP
   ADD TEMPFILE '/path/to/temp02.dbf' SIZE 10G;

Advanced Analysis

I/O Rate Calculation

-- Calculate I/O rates per second
SELECT file#, name,
       ROUND(phyrds/((SYSDATE - startup_time)*24*60*60), 2) reads_per_sec,
       ROUND(phywrts/((SYSDATE - startup_time)*24*60*60), 2) writes_per_sec
FROM v$filestat s, v$datafile f,
     (SELECT startup_time FROM v$instance)
WHERE s.file# = f.file#;

File Efficiency Metrics

-- Calculate read efficiency
SELECT file#,
       phyrds,
       phyblkrd,
       ROUND(phyblkrd/NULLIF(phyrds,0), 2) blocks_per_read,
       CASE
         WHEN phyblkrd/NULLIF(phyrds,0) > 8 THEN 'High Multiblock'
         WHEN phyblkrd/NULLIF(phyrds,0) > 1 THEN 'Some Multiblock'
         ELSE 'Single Block'
       END read_pattern
FROM v$filestat
WHERE phyrds > 1000
ORDER BY blocks_per_read DESC;

Tablespace I/O Aggregation

-- Aggregate I/O by tablespace
SELECT ts.name tablespace_name,
       SUM(fs.phyrds) total_reads,
       SUM(fs.phywrts) total_writes,
       ROUND(AVG(fs.readtim/NULLIF(fs.phyrds,0)), 2) avg_read_time,
       ROUND(AVG(fs.writetim/NULLIF(fs.phywrts,0)), 2) avg_write_time
FROM v$filestat fs, v$datafile df, v$tablespace ts
WHERE fs.file# = df.file#
AND df.ts# = ts.ts#
GROUP BY ts.name
ORDER BY total_reads DESC;

Related Scripts

• Basic File I/O Statistics (gvio.sql) - Simpler I/O view
• Database File Information (../administration/vdbfile.md) - File configuration details
• Buffer Pool by File (vbhfile.sql) - Memory distribution by file
• File Space Analysis (../administration/dfile.md) - File space usage