Oracle Disk I/O and Wait Events

Oracle I/O troubleshooting requires both database and operating-system evidence. A high request count can identify a busy workload, while high service time can identify latency; neither alone proves that storage is the root cause.

I/O by file type

SELECT filetype_name,
       SUM(small_read_reqs + large_read_reqs) AS read_requests,
       ROUND(SUM(small_read_megabytes + large_read_megabytes), 1) AS read_mb,
       SUM(small_write_reqs + large_write_reqs) AS write_requests,
       ROUND(SUM(small_write_megabytes + large_write_megabytes), 1) AS write_mb
FROM   v$iostat_file
GROUP  BY filetype_name
ORDER  BY read_mb + write_mb DESC;

These statistics are cumulative since instance startup. Capture snapshots at the beginning and end of a representative interval, then compare the deltas. A single cumulative total can hide when the activity occurred.

User I/O wait events

SELECT event,
       total_waits,
       ROUND(time_waited_micro / 1000, 1) AS total_wait_ms,
       ROUND((time_waited_micro / 1000) /
             NULLIF(total_waits, 0), 3) AS avg_wait_ms
FROM   v$system_event
WHERE  wait_class = 'User I/O'
ORDER  BY time_waited_micro DESC
FETCH FIRST 20 ROWS ONLY;

System-level averages mix many workloads and files. Use them to find a direction, then narrow the time window and correlate with active sessions, SQL plans, file metrics, and operating-system storage telemetry.

Interpretation checklist

• Is the problem latency, throughput, queueing, or simply a large amount of useful work?
• Did the execution plan or SQL workload change?
• Are reads physical, or could caching and memory pressure explain the change?
• Do host, hypervisor, SAN, cloud-volume, or filesystem metrics show the same time window?
• Are backups, checkpoints, log switches, maintenance, or batch jobs competing for I/O?

Wait-event names describe where a database call waited; they are not automatic proof of a failed disk. For example, application access paths can generate excessive single-block reads even when storage latency is normal.

Tooling and licensing

Dynamic performance views are available for live diagnostics with appropriate privileges. Historical analysis may use Automatic Workload Repository or Active Session History only when the organization’s Oracle licensing permits it. Otherwise, collect time-series samples with an approved monitoring system.

The original V$FILESTAT report was useful in its day but offered only cumulative file counters. This update uses current I/O views, interval-based interpretation, and cross-layer validation.