High IO wait / disk latency

The system is waiting on disk IO. First we separate whether the load is read- or write-heavy, then drill down to the causes: insufficient buffer cache, full table scans, checkpoint storms, IOPS limits or spills to temporary space.

Is the IO read-heavy or write-heavy?

Use file IO statistics to determine whether the wait is on the read side (data being read from disk) or on the write side (redo/checkpoint/data writes). This splits the tree in two.

SELECT function_name, small_read_reqs+large_read_reqs AS reads, small_write_reqs+large_write_reqs AS writes,
       small_read_megabytes+large_read_megabytes+small_write_megabytes+large_write_megabytes AS mb
FROM v$iostat_function ORDER BY mb DESC;

• Mostly read IO
• Mostly write IO

Why is read IO high?

Excessive physical reads come from three main causes: the buffer cache is too small (the working set does not fit in RAM), queries perform full table scans, or large sort/hash operations spill to temporary space.

SELECT ROUND((1-(pr.value/(dbg.value+cg.value)))*100,2) AS buffer_hit_pct
FROM v$sysstat pr, v$sysstat dbg, v$sysstat cg
WHERE pr.name='physical reads' AND dbg.name='db block gets' AND cg.name='consistent gets';
SELECT sql_id, disk_reads, executions FROM v$sql ORDER BY disk_reads DESC FETCH FIRST 10 ROWS ONLY;

• Buffer cache hit is low, the working set does not fit
• Queries perform full table scans
• Large sorts/hashes spill to temporary space

Why is write IO high?

A write-side bottleneck comes either from periodic checkpoints/flushes briefly saturating the disk (checkpoint storm) or from the storage lacking the IOPS/throughput to sustain the load.

SELECT event, total_waits, ROUND(time_waited/100,1) AS sec
FROM v$system_event
WHERE event IN ('log file parallel write','db file parallel write','checkpoint completed')
ORDER BY time_waited DESC;

• Sudden checkpoint/flush waves (checkpoint storm)
• Storage cannot keep up with sustained load (IOPS limit)

Root cause: insufficient buffer cache

The frequently accessed data set (working set) does not fit in the buffer cache, so blocks are constantly re-read from disk. The hit ratio is low and physical reads are high.

Root cause: full table scans

Queries scan large tables end to end because there is no suitable index; this both causes many physical reads and pollutes the cache with useless blocks.

Root cause: sorts/hashes spilling to temporary space

Large sort, hash join or group by operations do not fit in working memory and write to temporary space (temp/tempdb), generating heavy read/write IO.

Root cause: checkpoint storm

Dirty pages accumulate for a long time and are written to disk in bulk at short intervals; these sudden write waves periodically saturate IO.

Root cause: insufficient IOPS / throughput

The storage lacks the IOPS or bandwidth to serve the read+write requests the workload requires; queue depth and latency are persistently high.