Oracle 'db file sequential read': single-block index reads and buffer cache misses

db file sequential read is the dominant single-block I/O wait event in Oracle OLTP. Each time a foreground process needs one block, finds it missing from the buffer cache, and waits for the read from a datafile into the SGA, the session accrues time on this event. The name is historical: “sequential” refers to the single-block read into a specific buffer cache slot, not to a sequential scan pattern. Multiblock scans are tracked separately as db file scattered read.

The event sits in the User I/O wait class. It is normal and unavoidable for index-driven OLTP. What matters is the average latency per read and the share of total DB time the event consumes. A healthy NVMe-backed system typically sustains averages under 1ms per read; a SAN-backed system under 10ms. When the average climbs or the event dominates the wait profile, the bottleneck is either storage latency, a buffer cache that no longer fits the working set, or execution plans doing far more single-block lookups than the workload should require.

What the event represents

A db file sequential read wait begins when a foreground server process issues a single-block physical read and blocks until that read completes. On Linux, these reads typically use a synchronous pread() system call; the wait time is the elapsed time of that one read, from syscall issue to completion.

The three wait parameters identify exactly which block was read:

• P1 = file# (absolute file number, joinable to V$DATAFILE)
• P2 = block# (block number within that file)
• P3 = blocks (almost always 1 for this event; that is the defining trait)

When P2 is 1, the read targets a datafile header. Header reads happen during file opens, checkpoints, and backups and are normal, but a flood of them on slow storage can become a metadata bottleneck.

The classic driver is an index-driven lookup that follows TABLE ACCESS BY INDEX ROWID. The index walk yields rowids; each rowid points at a table block; if that block is not in the buffer cache, the session pays one db file sequential read. Undo block reads during read-consistent queries are a second, easily missed source: a long-running query reconstructing older block versions issues single-block reads against undo tablespace datafiles, and those wait on the same event. Reads of control file blocks and other metadata also surface here.

A related event, read by other session, covers the case where a second session wants a block that a first session is currently reading from disk. Before Oracle 10.1, that scenario was folded into buffer busy waits. After the split, the first session to miss the cache accrues db file sequential read, and every subsequent session waiting for the in-flight read accrues read by other session. Seeing both climb together is a strong signal of many sessions hitting the same hot blocks.

flowchart TD
    A[Session needs one block] --> B{In buffer cache?}
    B -- hit --> C[Logical read, no wait]
    B -- miss --> D[Issue pread syscall]
    D --> E[db file sequential read wait starts]
    E --> F[Block returned into cache]
    F --> G[Wait ends, session resumes]
    H[Other session wants same block] --> I[read by other session wait]
    I --> F

Where it shows up in production

Normal and expected:

• OLTP index lookups on a working set that mostly fits the buffer cache. Most reads hit cache; the misses show up here at low average latency.
• Undo reads during read-consistent queries, especially long-running reports against a busy OLTP instance.
• Datafile header reads during routine operations.

Worth investigating:

• The event’s total time crosses roughly 30% of DB time and is trending up. That points at either a shrinking effective cache or a workload doing more index-driven block fetches than before.
• Average latency exceeds storage class expectations (see thresholds below). Storage latency is the usual culprit, but I/O scheduler or filesystem behavior can also be the cause.
• The event appears during what should be a full table scan. Oracle can fall back to single-block reads during a cache-read tablescan or index fast full scan when the blocks still needed are scattered and not contiguous, so db file sequential read can dominate a scan you expected to see as db file scattered read.

A common silent driver is a high clustering factor on a frequently used index. When DBA_INDEXES.CLUSTERING_FACTOR approaches NUM_ROWS, adjacent index keys point at table blocks spread across the segment, so a small number of index entries forces a large number of TABLE ACCESS BY INDEX ROWID single-block reads. The index looks fine; the access pattern is the problem.

Starting in Oracle Database 11g, Oracle can choose direct path read for serial full scans of large segments, bypassing the buffer cache entirely. When that happens, scan I/O moves off db file scattered read and db file sequential read and onto direct path read. Do not assume a drop in db file sequential read means less I/O; it may mean the I/O moved events.

On Exadata, the storage cells service single-block reads, and the equivalent event is cell single block physical read. Smart scan offloading means many reads never surface as either event. If you are interpreting wait profiles on Exadata, look for the cell-prefixed event, not the db-file-prefixed one.

Interpreting latency and DB time share

Average latency per read is the first number to read. Typical operator thresholds:

These are averages. The distribution matters more. A storage subsystem with a healthy average but occasional 50ms+ outliers is usually queueing under burst load, and per-second latency visibility catches what a 5-minute average hides.

The second number is the event’s share of DB time. A practical escalation threshold is average latency above 10ms on SSD or above 20ms on SAN, sustained. For proactive tracking, the trigger is total db file sequential read time exceeding roughly 30% of DB time and trending up. If the event is the number-one non-idle wait on an OLTP system and growing, the system is becoming I/O-bound on single-block reads.

Two failure modes look similar but resolve differently:

• Latency rising at roughly constant wait count: storage degradation. Compare with V$FILESTAT.AVGIOTIM per datafile and OS-level iostat await on the underlying devices.
• Wait count rising at roughly constant latency: more cache misses or more index-driven access. Check buffer cache hit ratio trend, working set growth, and whether a high-frequency SQL changed plans.

Querying the event directly

These are safe to run on a production instance.

-- Average latency and total time for the event (system level)
SELECT EVENT, TOTAL_WAITS, TIME_WAITED_MICRO,
       ROUND(TIME_WAITED_MICRO/NULLIF(TOTAL_WAITS,0)/1000, 2) AS avg_ms
FROM V$SYSTEM_EVENT
WHERE EVENT = 'db file sequential read';

-- Current sessions waiting on the event right now
SELECT SID, SERIAL#, SQL_ID, P1 AS file_id, P2 AS block_id, SECONDS_IN_WAIT
FROM V$SESSION
WHERE EVENT = 'db file sequential read' AND STATE = 'WAITING';

-- Per-file physical read latency (AVGIOTIM is in milliseconds)
SELECT f.FILE#, d.NAME, f.PHYRDS, f.AVGIOTIM
FROM V$FILESTAT f JOIN V$DATAFILE d ON f.FILE# = d.FILE#
ORDER BY f.AVGIOTIM DESC;

-- Top SQL by buffer gets: high gets/exec with index access often drives this event
SELECT SQL_ID, PLAN_HASH_VALUE, EXECUTIONS,
       BUFFER_GETS / NULLIF(EXECUTIONS, 0) AS gets_per_exec,
       ELAPSED_TIME / NULLIF(EXECUTIONS, 0) / 1000 AS ms_per_exec
FROM V$SQL
WHERE EXECUTIONS > 0
ORDER BY BUFFER_GETS DESC
FETCH FIRST 20 ROWS ONLY;  -- 12c+; use ROWNUM <= 20 on 11g

-- Buffer cache hit ratio (context signal, not primary diagnostic)
SELECT
  ROUND(
    (1 - (SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'physical reads')
       / NULLIF((SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'db block gets')
              + (SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'consistent gets'), 0)
    ) * 100, 2
  ) AS buffer_cache_hit_ratio
FROM DUAL;

For session-level and historical drill-down, V$ACTIVE_SESSION_HISTORY and the AWR repository are the right tools, but both require the Diagnostics Pack license on Enterprise Edition. Without that license, rely on V$SYSTEM_EVENT, V$SESSION, V$SQL, and V$FILESTAT.

Signals to watch in production

How Netdata helps

• Per-second wait-event latency reveals single-block read average latency at the granularity where storage queueing actually shows up, rather than smoothed away in a 5-minute average.
• Correlating db file sequential read average latency with OS-level disk await and IOPS on the same dashboard separates a storage problem (latency rising everywhere) from a database problem (latency stable, wait count rising).
• Trending the event’s share of DB time alongside buffer cache hit ratio shows whether a growing cache miss rate is the driver, which points at working-set growth rather than storage degradation.
• Pairing this event with read by other session on one view surfaces hot-block contention: both climbing together means many sessions converging on the same blocks.
• SQL-level buffer-gets-per-execution trends catch the plan regressions that quietly multiply single-block reads before they become a page.

Netdata’s Oracle Database monitoring brings these signals together with per-second metrics and ML anomaly detection.

Related guides

• How Oracle Database actually works in production: a mental model for operators
• Oracle ’enq: TX - row lock contention’: blocking sessions and uncommitted DML
• Oracle ’enq: TM - contention’: unindexed foreign keys and table-level locks
• Oracle hard parse storm: literal SQL, bind variables, and shared pool churn
• Oracle ’library cache: mutex X’ waits: parsing pressure and cursor contention
• Oracle ‘cursor: pin S wait on X’: mutex contention on hot cursors
• Oracle ‘Checkpoint not complete’: redo log sizing, DBWn, and log-switch stalls
• Oracle ‘Thread N cannot allocate new log’: the archive hang that masquerades as up
• Oracle blocking sessions: finding the blocker at the head of the chain