Oracle physical I/O latency: per-datafile hotspots and AVGIOTIM

When a datafile’s read or write latency rises, every session touching that file slows. Wait events like db file sequential read and db file scattered read dominate the top timed events, and throughput drops. Oracle reports I/O statistics across several views with different units, coverage, and granularity. Querying V$FILESTAT without understanding that AVGIOTIM is in centiseconds, not milliseconds, leads to misdiagnosis by 10x.

The views that matter

Three views cover physical I/O statistics, each with different scope and granularity.

V$FILESTAT covers tablespace datafiles only. It reports cumulative physical reads (PHYRDS), physical writes (PHYWRTS), and timing columns including AVGIOTIM, READTIM, WRITETIM, and SINGLEBLKRDTIM. All counters are cumulative since instance startup. Redo logs, archive logs, control files, and temp files are invisible here.

V$TEMPSTAT covers temp files with the same column structure as V$FILESTAT. Temp file I/O comes from sort and hash operations that spill from PGA, plus global temporary tables.

V$IOSTAT_FILE covers all file types: data files, temp files, log files, archive logs, control files, and flashback logs. Its service time columns (SMALL_READ_SERVICETIME, SMALL_WRITE_SERVICETIME, LARGE_READ_SERVICETIME, LARGE_WRITE_SERVICETIME) are reported in milliseconds. It also has an ASYNCH_IO column per file. Unlike V$FILESTAT, it has no pre-computed average column; you divide service time by operation count.

The AVGIOTIM unit trap

AVGIOTIM is reported in centiseconds (hundredths of a second), not milliseconds. A value of 10 means 100 milliseconds, not 10 milliseconds. The same unit applies to READTIM, WRITETIM, SINGLEBLKRDTIM, MINIOTIM, MAXIORTM, and MAXIOWTM in this view.

An operator reading AVGIOTIM = 8 and concluding “8ms, that’s fine for SSD” is actually looking at 80ms latency. That is degraded on any storage class.

V$IOSTAT_FILE does not have this problem. Its service time columns are in milliseconds, matching what most storage engineers expect.

TIMED_STATISTICS must be TRUE

All timing columns in V$FILESTAT and V$TEMPSTAT return zero if TIMED_STATISTICS is FALSE. This is the default when STATISTICS_LEVEL is BASIC.

Verify before relying on timing data:

SELECT name, value FROM v$parameter
WHERE name IN ('timed_statistics', 'statistics_level');

If TIMED_STATISTICS is FALSE or STATISTICS_LEVEL is BASIC, all timing columns return zero regardless of actual I/O performance. The count columns (PHYRDS, PHYWRTS) are still populated, but latency analysis is impossible without timing enabled.

Per-datafile hotspot queries

Per-datafile physical I/O from V$FILESTAT:

SELECT f.FILE#, d.NAME, f.PHYRDS, f.PHYWRTS, f.AVGIOTIM
FROM V$FILESTAT f JOIN V$DATAFILE d ON f.FILE# = d.FILE#
ORDER BY f.PHYRDS + f.PHYWRTS DESC;

AVGIOTIM here is in centiseconds. Multiply by 10 for milliseconds before comparing to storage thresholds.

For millisecond-native latency without unit conversion, use V$IOSTAT_FILE:

SELECT d.NAME,
       f.SMALL_READS,
       f.SMALL_READ_SERVICETIME,
       ROUND(f.SMALL_READ_SERVICETIME / NULLIF(f.SMALL_READS, 0), 2) AS avg_small_read_ms
FROM V$DATAFILE d JOIN V$IOSTAT_FILE f ON d.FILE# = f.FILE_NO
ORDER BY avg_small_read_ms DESC;

SMALL_READS are single-block reads (index access). LARGE_READS are multi-block reads (full scans). Check both columns when diagnosing whether latency is index-driven or scan-driven.

For temp files:

SELECT f.FILE#, d.NAME, f.PHYRDS, f.PHYWRTS, f.AVGIOTIM
FROM V$TEMPSTAT f JOIN V$TEMPFILE d ON f.FILE# = d.FILE#
ORDER BY f.PHYRDS + f.PHYWRTS DESC;

For system-level physical I/O volume:

SELECT NAME, VALUE FROM V$SYSSTAT
WHERE NAME IN ('physical reads', 'physical writes',
               'physical reads direct', 'physical writes direct',
               'redo writes');

physical reads direct and physical writes direct bypass the buffer cache entirely. These come from parallel queries, serial direct-path reads of large segments (11g+), LOB reads, and temp file operations. They do not benefit from buffer cache sizing changes.

Delta sampling for real latency

AVGIOTIM is a cumulative average since instance startup (or since the counter was last reset). It smooths over brief latency spikes. A datafile that ran at 5ms for a week and then spiked to 200ms for 10 minutes will still show a low cumulative average.

To get current latency, sample deltas over a short interval:

-- Sample 1: capture baseline
SELECT f.FILE#, f.PHYRDS, f.READTIM
FROM V$FILESTAT f;

-- Wait 60 seconds, then capture sample 2 and compute:
-- Delta read latency (centiseconds):
--   (READTIM_t2 - READTIM_t1) / (PHYRDS_t2 - PHYRDS_t1)
-- Convert to ms: multiply by 10

Guard against division by zero: if PHYRDS did not change during the interval, the file had no read activity and the latency is undefined.

V$IOSTAT_FILE data covers a shorter rolling window than V$FILESTAT’s instance-lifetime counters, making it a better starting point for recent analysis without manual delta scripting.

Thresholds and what they mean

Storage-dependent latency targets:

Remember the unit conversion: AVGIOTIM is in centiseconds. A 10ms threshold means AVGIOTIM should be below 1. A raw AVGIOTIM of 10 is 100ms, which is degraded on any storage class.

Compare per-file latency against peers, not just absolute thresholds. One file at 15ms while others sit at 2ms is a hotspot, even if 15ms is technically within the normal range for SAN. The relative difference matters as much as the absolute value.

Exadata: where physical reads mislead

On Exadata, smart scan offloads predicate filtering and column projection to storage cells. This changes what database-level physical read statistics represent.

The physical reads direct statistic may undercount actual cell-level I/O because the cell filters data before returning results to the database. For cell-level I/O volume, check V$IOSTAT_FUNCTION (which has a Smart Scan function row separating offloaded I/O from other types) and V$IOFUNCMETRIC (per-function IOPS, MBPS, and average wait time for the most recent interval).

The wait event for single-block reads on Exadata is cell single block physical read, not db file sequential read. Smart scans produce cell smart table scan or cell smart index scan events, not the single-block event.

Flash cache hits are typically sub-millisecond. Flash cache misses can be significantly higher. If you are correlating wait events with V$FILESTAT latency on Exadata, look for the Exadata-specific event names.

flowchart LR
  A[Session requests block] --> B{In buffer cache?}
  B -->|Yes| C[Logical read, no physical I/O]
  B -->|No| D{Direct path?}
  D -->|No| E[Buffer cache read]
  D -->|Yes| F[Direct path read]
  F --> G{Exadata smart scan?}
  G -->|Yes| H[cell smart table scan
cell filters and projects]
  G -->|No| I[physical reads direct]
  E --> J[V$FILESTAT
PHYRDS + AVGIOTIM]
  H --> J
  I --> J

Beyond averages: latency distribution

AVGIOTIM is an average. Averages hide tail latency. A file with an average of 5ms might have a p99 of 80ms if most reads complete in 1ms but a fraction stall.

V$FILE_HISTOGRAM displays a histogram of single-block read times per file, showing how many reads fell into each latency bucket (1ms, 2ms, 4ms, 8ms, etc.). This reveals whether the average is representative or distorted by a tail.

V$EVENT_HISTOGRAM provides the same distribution for wait events, including db file sequential read and db file scattered read. Check these when the average looks acceptable but users report intermittent slowness.

Async I/O verification

V$IOSTAT_FILE has an ASYNCH_IO column per file. If it shows ASYNC_OFF, the database may not report accurate service times.

SELECT d.NAME, f.ASYNCH_IO
FROM V$DATAFILE d JOIN V$IOSTAT_FILE f ON d.FILE# = f.FILE_NO;

If async I/O is off, investigate the FILESYSTEMIO_OPTIONS parameter and disk async I/O settings at the OS level.

Signals to watch

How Netdata helps

• Per-second collection of physical reads, physical writes, and physical reads direct from V$SYSSTAT shows I/O volume shifts within seconds, not after AWR snapshot intervals.
• Per-datafile AVGIOTIM trends correlated with wait event profiles (db file sequential read, db file scattered read) pinpoint which file drives system-wide latency without manual delta scripting.
• OS-level disk metrics (await, %util, queue depth per device) overlaid against Oracle per-file latency distinguish database-side contention from storage-side degradation.
• On Exadata, cell single block physical read wait time tracks flash cache hit/miss patterns alongside standard I/O metrics.

Netdata’s Oracle Database monitoring with Netdata brings these signals together with per-second metrics and anomaly detection on per-file latency baselines.

Related guides

• How Oracle Database actually works in production: a mental model for operators
• Oracle archive log destination full: V$ARCHIVE_DEST_STATUS, the ERROR state, and space
• Oracle autoextend hit MAXSIZE: the space gotcha with a half-empty filesystem
• Oracle blocking sessions: finding the blocker at the head of the chain
• Oracle ‘Thread N cannot allocate new log’: the archive hang that masquerades as up
• Oracle ‘Checkpoint not complete’: redo log sizing, DBWn, and log-switch stalls
• Oracle ‘cursor: pin S wait on X’: mutex contention on hot cursors
• Oracle ‘db file scattered read’: multiblock reads, full scans, and plan regressions
• Oracle ‘db file sequential read’: single-block index reads and buffer cache misses
• Oracle ’enq: TM - contention’: unindexed foreign keys and table-level locks
• Oracle ’enq: TX - row lock contention’: blocking sessions and uncommitted DML
• Oracle Fast Recovery Area full: db_recovery_file_dest_size, reclaimable space, and DELETE OBSOLETE