Oracle problems

db file scattered read: The Scan Behind It, and the Fix

3 November 2026

db file scattered read is the wait for a multiblock read. Oracle's reference defines it as similar to db file sequential read, "except that the session is reading multiple data blocks", and the blocks land scattered through the buffer cache, which is where the name comes from. In practice it is a full table scan or an index fast full scan reading from disk. One such scan at night is a healthy database; the same event at the top of the list at ten in the morning, from a statement that ran on an index last week, is a plan that changed or an index that is gone. The queries below name the statement and the segment, and the fix depends on whether the scan should exist at all.

What the wait means

The reference gives the wait time as "the actual time it takes to do all of the I/Os" and three parameters: file#, block# and blocks, "the number of blocks that the session is trying to read from the file# starting at block#". The blocks of one wait are at most DB_FILE_MULTIBLOCK_READ_COUNT, which Oracle sets by default from the largest I/O the platform does efficiently, so a wait that reads 8 blocks on a system that could read 128 is a scan hitting the ends of extents, or a cache that already holds some of the blocks. Two other things are not this event. A single-block read for an index lookup is db file sequential read. And since 11g a full scan of a table Oracle judges large against the buffer cache usually bypasses the cache altogether and waits on direct path read, so what remains under db file scattered read is the scan of a small or medium table, an index fast full scan, or a scan Oracle decided to cache.

Which statement, which object

The sessions waiting on it right now, with the file and block of the current read:

SELECT s.sql_id, COUNT(*) AS sessions,
       MAX(s.p1) AS file#, MAX(s.p2) AS block#, MAX(s.p3) AS blocks
FROM   v$session s
WHERE  s.event = 'db file scattered read'
AND    s.status = 'ACTIVE'
GROUP  BY s.sql_id
ORDER  BY sessions DESC;

The segment that owns the block being read:

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

And the scan in the statement's plan, which is the line to judge:

SELECT id, operation, options, object_name, cardinality, cost
FROM   v$sql_plan
WHERE  sql_id = '&sql_id' AND child_number = 0
AND    options LIKE '%FULL%'
ORDER  BY id;

TABLE ACCESS FULL and INDEX FAST FULL SCAN are the two operations that produce this wait. The cardinality next to them is the optimizer's estimate of the rows the scan returns; when it is a few rows out of millions, the scan is there because no index fits the predicate, or because the optimizer thinks so.

How much scanning there is, and who does it

Oracle counts scans since startup in V$SYSSTAT; two readings an hour apart give the rate:

SELECT name, value
FROM   v$sysstat
WHERE  name IN ('table scans (short tables)',
                'table scans (long tables)',
                'table scan blocks gotten', 'table scan rows gotten',
                'index fast full scans (full)',
                'physical reads', 'physical reads direct');

Thousands of long-table scans an hour on an OLTP system is the number to chase. The statements that read the most since they were loaded, which is the list the scan is usually on:

SELECT sql_id, plan_hash_value, executions,
       ROUND(disk_reads / NULLIF(executions, 0)) AS reads_per_exec,
       disk_reads, buffer_gets,
       ROUND(elapsed_time / 1e6)                 AS elapsed_s
FROM   v$sql
WHERE  disk_reads > 100000
ORDER  BY disk_reads DESC FETCH FIRST 20 ROWS ONLY;

And the segments read most, from V$SEGMENT_STATISTICS, where a table that is small and read a million times a day is the second kind of problem:

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 15 ROWS ONLY;

Should the scan exist

A scan is right when the statement needs a large share of the rows: a report, a nightly batch, an aggregate over a year. It is wrong when the statement filters to a handful of rows and reads the whole table to find them. The usual reasons for the wrong kind are an index that is missing, an index that has become unusable after a partition operation or a move, a predicate that defeats the index (a function on the column, an implicit conversion from a bind of the wrong type, a leading wildcard), and new statistics that made the optimizer change its mind. plan_hash_value in the query above is the quick test: the same sql_id with a new hash and far more reads per execution is a plan flip, not a storage problem.

The fix, in order

A scan that should be a seek: fix the access, not the disk. Create the index the predicate needs, rebuild the unusable one, or rewrite the predicate so the existing index applies. When the plan flipped, bring the old plan back with a SQL plan baseline, which the Oracle licensing manual says "does not require a license for Oracle Diagnostics Pack or Oracle Tuning Pack".
A scan that is right: make it read less. Partition the table so the scan prunes to the partitions the predicate names; archive the rows a report never asks for; compress the table so the same rows are fewer blocks. Each of these cuts the blocks the scan reads, which is the only number this wait responds to.
A small table scanned all day: keep it in memory. A reference table of a few thousand blocks that is read from disk a thousand times an hour belongs in the KEEP pool or on an index, and the V$SEGMENT_STATISTICS list names it.
Reads that are slow, not many. Divide time_waited_micro by total_waits for the event in V$SYSTEM_EVENT: a multiblock read that averages far above the single-block latency of the same storage, or that got slower this week while the block count did not change, is the storage, and the latency page has the checks.
Leave DB_FILE_MULTIBLOCK_READ_COUNT alone. Setting it high makes each read bigger and also makes the optimizer cost every scan cheaper, so you get more scans, not faster ones. The default already matches the platform's I/O size.
Move the scan off the busy hour. A report that must scan runs at six in the morning, with a degree of parallelism the storage can serve, and nobody waits behind it.

See it coming

Radar Engine, the detection layer of Scops, samples every session every 5 seconds and keeps the per-statement figures every 10 minutes, so it sees the scan arrive as a statement that now reads a hundred times more per execution, or as a new plan, hours before the User I/O class is the whole chart:

Detector	Clock	When it fires
SQL on a new plan	Live, every 5 minutes	A new plan: Warning. Three times slower, and it adds a full active session: Critical.
SQL running slower	Live	Three times slower per run: Warning. Five times, and it adds a full active session or holds two: Critical.
SQL taking most of the load	Live. Half or more of the active sessions.	Above the usual of this hour: Warning. More sessions than CPUs: Critical. Always so at this hour: Watch.
Unusual User I/O waits	Hourly, against the same hour on other days	By 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 event	Live	A sudden large share of the waits: Watch. The largest one, if it continues: Warning.

The finding names the statement, its old and new plan, the reads per execution before and after, and the segment it scans. The Activity chart draws the User I/O class by statement, and the SQL page shows the plan with the full scan on it, on any edition and without the Diagnostics Pack.

Questions

What is db file scattered read?
The wait for a multiblock read into the buffer cache: a full table scan or an index fast full scan reading from disk. Oracle's reference defines it as the sequential read's sibling where the session reads multiple data blocks; the blocks are scattered through the cache, not on the disk.

Is db file scattered read bad?
Not by itself. A report that must read a whole table waits on it and should. It is a problem when the statement behind it needs a few rows and scans millions to find them, which is a missing or unusable index or a plan that changed, and when the same small table is scanned from disk all day.

What is the difference between db file sequential read and db file scattered read?
Sequential read is one block at a time, usually an index lookup or a row fetch by rowid; scattered read is many blocks in one call, a scan. Both go into the buffer cache. A large scan that bypasses the cache is a third event, direct path read.

Why does my full table scan show direct path read instead?
Since 11g Oracle reads a table it judges large against the buffer cache straight into the session's memory, so the scan waits on direct path read and leaves nothing in the cache for the next session. Smaller tables and index fast full scans still go through the cache and show db file scattered read.

Sources

Oracle: Descriptions of wait events, DB_FILE_MULTIBLOCK_READ_COUNT, V$SEGMENT_STATISTICS, V$SQL_PLAN
Oracle: Instance tuning using performance views, Database Licensing Information User Manual
Scops docs: Detector reference