Enhanced File I/O Statistics (vfiler.sql)

Enhanced File I/O Statistics Script (vfiler.sql)

Overview

The vfiler.sql script provides detailed file I/O statistics including physical reads, writes, block operations, and response times. It analyzes both data files and temporary files, helping identify I/O bottlenecks and hot files in your Oracle database.

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.

Script

REM vfiler.sql
REM v$filestat with added columns for phys reads/writes per block and average read/write times
REM

Column file#           Format 999     Heading "File|Id"
Column tablespace_name Format a12     Heading "Tablespace|Name"
Column file_name       Format a35     Heading "File Name"
Column size_mb         Format 999,999 Heading "File|Size|MB"
Column phyrds          Format 999,999,999 Heading "Physical|Reads"
Column phyblkrd        Format 999,999,999 Heading "Physical|Blocks|Read"
Column phywrts         Format 999,999,999 Heading "Physical|Writes"
Column phyblkwrt       Format 999,999,999 Heading "Physical|Blocks|Written"
Column rb_per_rd       Format 999,999 Heading "Blocks|Per|Read"
Column rb_per_wr       Format 999,999 Heading "Blocks|Per|Write"
Column avg_rd          Format 999,999 Heading "Avg|Read|Time"
Column avg_wr          Format 999,999 Heading "Avg|Write|Time"

TTITLE "FILE I/O STATISTICS"

SELECT
        df.file_id             file#,
        df.tablespace_name     tablespace_name,
        df.file_name           file_name,
        df.bytes/(1024*1024)   size_mb,
        fs.phyrds              phyrds,
        fs.phyblkrd            phyblkrd,
        fs.phywrts             phywrts,
        fs.phyblkwrt           phyblkwrt,
        decode(fs.phyrds,0,0,fs.phyblkrd/fs.phyrds)   rb_per_rd,
        decode(fs.phywrts,0,0,fs.phyblkwrt/fs.phywrts) rb_per_wr,
        fs.avgiotim            avg_rd,
        fs.avgiotim            avg_wr
FROM  v$filestat fs, dba_data_files df
WHERE df.file_id = fs.file#
UNION
SELECT
        tf.file_id+10000      file#,
        tf.tablespace_name     tablespace_name,
        tf.file_name           file_name,
        tf.bytes/(1024*1024)   size_mb,
        ts.phyrds              phyrds,
        ts.phyblkrd            phyblkrd,
        ts.phywrts             phywrts,
        ts.phyblkwrt           phyblkwrt,
        decode(ts.phyrds,0,0,ts.phyblkrd/ts.phyrds)   rb_per_rd,
        decode(ts.phywrts,0,0,ts.phyblkwrt/ts.phywrts) rb_per_wr,
        ts.avgiotim            avg_rd,
        ts.avgiotim            avg_wr
FROM  v$tempstat ts, dba_temp_files tf
WHERE tf.file_id = ts.file#
ORDER BY 5 desc, 7 desc
/

What This Script Does

• Displays comprehensive I/O statistics for all database files
• Includes both permanent data files and temporary files
• Calculates blocks per read/write operations
• Shows average I/O response times
• Orders results by highest I/O activity (reads then writes)
• Helps identify I/O hotspots and performance bottlenecks

Usage

SQL> @vfiler.sql

Parameters

This script requires no input parameters.

Required Privileges

• SELECT privilege on V$FILESTAT, V$TEMPSTAT
• SELECT privilege on DBA_DATA_FILES, DBA_TEMP_FILES
• SELECT_CATALOG_ROLE or DBA role recommended

Sample Output

                                    FILE I/O STATISTICS

File Tablespace    File Name                           File  Physical    Physical    Physical    Physical  Blocks Blocks    Avg    Avg
  Id Name                                              Size   Reads      Blocks      Writes     Blocks      Per    Per    Read  Write
                                                        MB               Read                   Written     Read  Write   Time   Time
---- ------------ ----------------------------------- ------ ----------- ----------- ----------- ----------- ------ ------ ------ ------
   1 SYSTEM       /u01/app/oracle/system01.dbf        1,024  12,456,789  89,234,567    234,567   1,234,567      7      5     12     15
   3 SYSAUX       /u01/app/oracle/sysaux01.dbf        2,048   8,123,456  45,678,901    123,456     987,654      6      8     10     12
   5 USERS        /u01/app/oracle/users01.dbf         5,120   5,234,567  23,456,789     89,012     567,890      4      6      8     10
   2 UNDOTBS1     /u01/app/oracle/undotbs01.dbf       1,024   2,345,678  12,345,678    456,789   3,456,789      5      8     15     18
10001 TEMP         /u01/app/oracle/temp01.dbf          2,048   1,234,567   8,901,234     67,890     456,789      7      7     20     25

Key Output Columns

File Id

• Regular data files: Normal file ID
• Temp files: File ID + 10000 (for unique identification)

Physical Reads/Blocks Read

• Physical Reads: Number of read operations
• Physical Blocks Read: Total blocks read
• Shows read activity intensity

Physical Writes/Blocks Written

• Physical Writes: Number of write operations
• Physical Blocks Written: Total blocks written
• Indicates write activity level

Blocks Per Read/Write

• Average blocks per I/O operation
• Higher values indicate multi-block operations
• Helps identify sequential vs random I/O patterns

Avg Read/Write Time

• Average time for I/O operations (centiseconds)
• Key performance indicator
• Higher values indicate I/O subsystem stress

Understanding the Metrics

I/O Patterns

• High Blocks Per Read: Indicates full table scans
• Low Blocks Per Read (1-2): Indicates index access patterns
• Consistent High I/O: May indicate hot files needing redistribution

Response Times

• < 10ms: Good performance
• 10-20ms: Acceptable for most workloads
• > 20ms: May indicate I/O bottlenecks
• > 50ms: Serious I/O performance issues

File Types

• SYSTEM/SYSAUX: High reads normal for data dictionary access
• UNDO: High writes normal for transaction management
• TEMP: High I/O during sorts and hash joins
• User tablespaces: Varies by application workload

Common Use Cases

Identify Hot Files

-- Files with most physical reads
SELECT * FROM (
    SELECT file_name, phyrds
    FROM v$filestat fs, dba_data_files df
    WHERE fs.file# = df.file_id
    ORDER BY phyrds DESC
) WHERE ROWNUM <= 10;

Monitor I/O Response Times

-- Files with slow response times
SELECT file_name, avgiotim
FROM v$filestat fs, dba_data_files df
WHERE fs.file# = df.file_id
AND avgiotim > 20
ORDER BY avgiotim DESC;

Analyze I/O Distribution

• Review if I/O is evenly distributed across files
• Identify files that may need to be moved to faster storage
• Consider striping hot files across multiple disks

Performance Analysis

Hot File Remediation

1. Move to faster storage: SSD for high I/O files
2. Stripe across disks: Distribute I/O load
3. Add data files: Spread objects across multiple files
4. Partition tables: Distribute large tables

I/O Pattern Optimization

• Sequential reads: Consider larger DB_FILE_MULTIBLOCK_READ_COUNT
• Random reads: Focus on reducing logical I/O through indexing
• High write activity: Review redo and undo configuration

Troubleshooting

High I/O Wait Times

-- Check for I/O-related wait events
SELECT event, total_waits, time_waited/100 seconds_waited
FROM v$system_event
WHERE event LIKE 'db file%'
ORDER BY time_waited DESC;

Unbalanced I/O

• Review tablespace file distribution
• Consider using bigfile tablespaces for large objects
• Implement ASM for automatic I/O balancing

Temp File I/O Issues

• Monitor for excessive sorting
• Increase PGA_AGGREGATE_TARGET
• Consider using memory for sorts

Best Practices

1. Regular Monitoring: Track I/O patterns over time
2. Baseline Metrics: Establish normal I/O patterns
3. Proactive Tuning: Address issues before they impact users
4. Storage Planning: Use metrics for capacity planning
5. Performance Testing: Measure I/O impact of changes

Integration with Storage Management

ASM Integration

-- For ASM-managed files
SELECT dg.name diskgroup, af.bytes/1024/1024 size_mb,
       af.phyrds, af.phywrts
FROM v$asm_file af, v$asm_diskgroup dg
WHERE af.group_number = dg.group_number;

Storage Tier Optimization

• Place high I/O files on fastest storage
• Use storage tiering based on access patterns
• Consider hybrid storage solutions

Related Scripts

• File I/O Statistics with Timing (vfile.sql) - Basic file I/O stats
• Random I/O Analysis (random_io.sql) - Random I/O patterns
• Sequential I/O Wait Analysis (sequential_io.sql) - Sequential I/O analysis