Scops
Docs Download

Oracle problems

db file sequential read Waits: Slow Storage or Too Many Reads, and the Fix

3 November 2026

Every index lookup that misses the buffer cache is one single-block read, and db file sequential read is the wait for it. Oracle's own tuning guide says that on a healthy system physical reads are the biggest waits after the idle ones, so the event at the top of the list is not the problem by itself. The problem is when each read got slower, or when there are far more of them, and the two look the same at the top of the list. Slower reads are the storage; more reads are a plan or a cache. Three queries tell them apart, and the fix is different for each.

What the wait means

The session is reading one block from a datafile into the buffer cache and waits for the physical I/O call to return. Single-block reads come from indexes: every index branch and leaf block, and every table block fetched by ROWID after the index, is one read and one wait. The wait's parameters name the file (P1), the block (P2) and the number of blocks, which is 1 (P3).

What a read should take depends on the storage: well under a millisecond from flash, a few milliseconds from a shared array over a SAN. Oracle's guide calls disk service times above 20 to 30 ms the sign of an overloaded I/O system; Radar draws its line at 9 ms, and only when that is well above the usual for the database. The neighbours in the same list:

Slower reads or more reads

The lifetime average in V$SYSTEM_EVENT hides the last hour. The latency and the read rate of the last minute are in V$SYSMETRIC, no pack needed:

SELECT metric_name, ROUND(value, 2) AS value, metric_unit
FROM   v$sysmetric
WHERE  group_id = 2
AND    metric_name IN (
         'Average Synchronous Single-Block Read Latency',
         'Physical Read IO Requests Per Sec',
         'Physical Reads Per Sec');

Latency up while the request rate stayed where it was: the storage got slower. Latency flat while requests doubled: the database reads more, and the next two sections find the statement or the cache. Both up: the extra reads queue on the same disks, and the reads came first. The histogram says whether every read is slow or a tail of them is:

SELECT wait_time_milli, wait_count
FROM   v$event_histogram
WHERE  event = 'db file sequential read'
ORDER  BY wait_time_milli;

Each bucket holds the waits shorter than its millisecond value and longer than the bucket before. A healthy flash array has nearly everything in the 1 and 2 ms buckets. A hump at 32 and 64 ms with the small buckets still full is a path or an array that stalls now and then: a storage ticket, not a SQL one.

Which file, which object

Average single-block read time for each datafile since startup, from V$FILESTAT, whose times are in hundredths of a second:

SELECT d.file#, d.name,
       f.singleblkrds                             AS reads,
       ROUND(f.singleblkrdtim * 10
             / NULLIF(f.singleblkrds, 0), 2)      AS avg_ms
FROM   v$filestat f
JOIN   v$datafile d ON d.file# = f.file#
WHERE  f.singleblkrds > 0
ORDER  BY avg_ms DESC;

One file far slower than the rest is one LUN, one path or one disk group; every file slower at once is the array or the path to it. The sessions on the event right now, with the block they wait for:

SELECT s.sid, s.username, s.sql_id,
       s.p1 AS file#, s.p2 AS block#,
       ROUND(s.wait_time_micro / 1000)            AS wait_ms
FROM   v$session s
WHERE  s.event = 'db file sequential read'
AND    s.state = 'WAITING';

The file and block name the segment through DBA_EXTENTS, which is slow on a database with millions of extents, so pass one pair at a time:

SELECT owner, segment_name, segment_type
FROM   dba_extents
WHERE  file_id = :file#
AND    :block# BETWEEN block_id AND block_id + blocks - 1;

Over a longer window, V$SEGMENT_STATISTICS keeps physical reads by segment since startup, again without a pack. The top of that list is usually one index and the table under it:

SELECT owner, object_name, object_type,
       value                                      AS physical_reads
FROM   v$segment_statistics
WHERE  statistic_name = 'physical reads'
ORDER  BY value DESC
FETCH FIRST 10 ROWS ONLY;

Which statement does the reading

SELECT sql_id, executions, disk_reads,
       ROUND(disk_reads / NULLIF(executions, 0))  AS reads_per_exec,
       buffer_gets,
       ROUND(elapsed_time / 1e6)                  AS seconds,
       SUBSTR(sql_text, 1, 60)                    AS sql_text
FROM   v$sql
WHERE  disk_reads > 0
ORDER  BY disk_reads DESC
FETCH FIRST 10 ROWS ONLY;

Two shapes come back. A statement with few executions and millions of reads is a report or a batch step that should scan: an index range scan over a large share of the table, then one single-block read per row on a table whose rows are scattered across its blocks. A statement with modest reads per execution and a huge execution count is the OLTP workhorse whose working set no longer fits the cache. The plan tells which: TABLE ACCESS BY INDEX ROWID under an INDEX RANGE SCAN with a high row count in DBMS_XPLAN.DISPLAY_CURSOR is the first shape. When the statement ran fast last week with another plan, SQL plan changed has the query that shows the old plan without the Diagnostics Pack.

Is the cache too small

Oracle simulates the cache at other sizes in V$DB_CACHE_ADVICE, on while the DB_CACHE_ADVICE parameter is ON, which is dynamic. The read factor is the ratio of the physical reads at that size to the reads at the current size:

SELECT size_for_estimate                           AS cache_mb,
       size_factor,
       estd_physical_read_factor                   AS read_factor
FROM   v$db_cache_advice
WHERE  name = 'DEFAULT'
AND    advice_status = 'ON'
AND    block_size = (SELECT value FROM v$parameter
                     WHERE name = 'db_block_size')
ORDER  BY size_for_estimate;

A factor of 0.6 at twice the size means doubling the cache removes 40% of the reads: the cache is short. A curve that is flat above the current size means the reads are once-only, a scan or a batch, and more memory changes nothing; the fix is in the statement.

The fix, in order

  1. Fix the statement that reads the most. This is the largest win on most databases and costs no hardware. An index range scan that visits a large share of the table, followed by a table access by ROWID, does one single-block read per row; a hash join with a full scan reads the same rows in multiblock reads, in a fraction of the time. A composite index that covers the filter and the columns selected removes the table reads entirely. A plan that flipped to a worse index goes back to the old plan with a baseline while the statistics are fixed.
  2. Keep the hot data in memory. When the advisor shows the reads falling with size, raise DB_CACHE_SIZE, or SGA_TARGET when the SGA is automatic, by the step the advisor priced and not beyond: Oracle's guide says not to keep increasing it. Small, hot tables and indexes that a scan keeps pushing out go into the KEEP pool: DB_KEEP_CACHE_SIZE for the pool, STORAGE (BUFFER_POOL KEEP) on the object.
  3. Treat it as a storage problem only when the reads got slower without getting more numerous. Every file slower at once, the request rate flat, a new tail in the histogram: that is the array, the path or the host, and no parameter fixes it. The usual causes are a failed path in the multipath set that halves the bandwidth, a rebuild or a snapshot on the array, a backup or a clone running on the same disks, an ASM rebalance, and a neighbour on the same storage that got busy. The host's own view, iostat -x on Linux with its await and queue columns, says whether the latency is below Oracle or in it.
  4. Move the hottest files to the fastest storage. One file far slower than the rest is a slow device; one file far busier than the rest is a hot segment whose tablespace deserves flash. The per-file query above gives both.
  5. Read fewer blocks. Partition pruning turns an index probe over years into one over a month; compression puts more rows in each block, so the cache holds more of the table; an index whose leaf blocks are mostly empty after mass deletes reads several blocks for what should be one, and a rebuild fixes that one case.
  6. Do not chase the hit ratio, and do not add indexes blindly. The buffer cache hit ratio moves with the workload, not with its health, and a new index is one more set of single-block reads for every statement that chooses it. DB_FILE_MULTIBLOCK_READ_COUNT changes scattered reads, not this wait.

See it coming

Radar Engine, the detection layer of Scops, watches single-block read latency live against the usual level of that hour, with the read rate beside it, so it tells slower reads from more reads the way the first query does. No agent, no AWR: Scops samples the sessions every 5 seconds, and the User I/O class by hour is in the Activity chart on any edition:

DetectorClockWhen it fires
Slow single-block readsLive. 9 ms or more and well above the usual, in 3 of 10 minutesWatch. With User I/O waits: Warning. Five times the usual without twice the reads: Critical.
Unusual single-block read latencyHourly, read latency together with User I/O waitsBy size and duration.
Unusual User I/O waitsHourly, against the same hour on other daysBy size, duration and cause: the usual load, many statements, a backup or a database link. Critical only with a named cause, high for 3 hours, while slow reads are open.
Sudden wait eventLiveA sudden large share of the waits: Watch. The largest one, if it continues: Warning.

The Critical rule is the storage rule: five times the usual latency without twice the reads means the disks got slower, not the database busier. The finding names the database, the latency against its usual and the hour it began, which is usually the hour a path failed or a batch was deployed.

Questions

Is db file sequential read a storage problem?
Only when the reads got slower without getting more numerous. Latency up with a flat request rate is the storage; latency flat with the rate up is a plan or a cache. V$SYSMETRIC gives both numbers in one query.

What is a normal db file sequential read time?
Well under a millisecond on flash, a few milliseconds on a shared array. Oracle's tuning guide calls service times above 20 to 30 ms an overloaded I/O system; Radar warns at 9 ms when that is well above the database's usual level.

Why does an index cause db file sequential read waits?
Because every index block and every table block fetched by ROWID is one single-block read. A range scan over a large share of a table whose rows are scattered across its blocks does one read per row, where a full scan reads the same rows in multiblock reads.

Will a bigger buffer cache fix it?
V$DB_CACHE_ADVICE answers before the memory is bought. If the estimated reads fall steeply with size, yes. If the curve is flat above the current size, the reads are once-only and the fix is in the statement.

Related problems

Sources