Oracle 'db file scattered read': multiblock reads, full scans, and plan regressions

A sudden spike in db file scattered read wait time on an OLTP database is one of the most reliable signals of an execution plan regression. The wait event itself is benign on analytics and warehouse workloads, where multiblock full scans are the expected access path. On a transactional system it usually means a query that used to do a handful of index reads is now scanning whole tables.

This guide covers the failure pattern, the path from wait event to offending SQL_ID, and the tradeoffs in the common fixes. It is scoped to single-instance and RAC databases doing buffered multiblock reads. From 11g onward, many large serial scans bypass the cache via direct path read instead; that event is covered where it affects diagnosis.

What this means

db file scattered read is the wait event Oracle posts when a server process issues a multiblock read for up to DB_FILE_MULTIBLOCK_READ_COUNT contiguous blocks and scatters them into non-contiguous slots in the buffer cache. The default for DB_FILE_MULTIBLOCK_READ_COUNT is platform-dependent and typically capped by the OS maximum I/O size. The name refers to buffer placement, not the on-disk pattern. The I/O itself is sequential.

The event fires for:

• Full table scans (FTS) on segments small enough that the optimizer chose the buffered read path.
• Index fast full scans (FFS) reading the index segment in storage order rather than walking the b-tree.
• Some index range or full index scans that use optimizer-driven index prefetch. The read can batch logically adjacent leaf blocks; the observed block count is not necessarily DB_FILE_MULTIBLOCK_READ_COUNT, and some batches may post db file parallel read instead.

From 11g onward, many large serial scans bypass the buffer cache via direct path read and post that wait event instead. As a result, db file scattered read typically undercounts total full scan activity on modern releases. A system with very low scattered read waits can still be drowning in physical reads done under direct path read. Check both events.

The operational question is not “are scattered reads happening” but “did they start happening on a workload where they do not belong”. On an OLTP system, db file scattered read becoming a top-3 wait event is a regression marker until proven otherwise. On a warehouse, the same signal is the workload.

Common causes

The first three are incident-response cases. The last two are slow-burn capacity issues that look like incidents when a threshold is crossed.

Quick checks

All queries below are read-only and safe to run on a production instance.

-- System-level wait time and average latency
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 scattered read';

-- Where scattered reads rank among non-idle waits right now
SELECT NVL(WAIT_CLASS, 'ON CPU') AS wait_class, EVENT, COUNT(*) AS sessions
FROM V$SESSION
WHERE STATUS = 'ACTIVE' AND TYPE = 'USER' AND WAIT_CLASS != 'Idle'
GROUP BY NVL(WAIT_CLASS, 'ON CPU'), EVENT
ORDER BY COUNT(*) DESC
FETCH FIRST 15 ROWS ONLY;

-- Top SQL by logical reads (the usual regression offenders)
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,
       SUBSTR(SQL_TEXT, 1, 120) AS sql_preview
FROM V$SQL
WHERE EXECUTIONS > 0
ORDER BY BUFFER_GETS DESC
FETCH FIRST 20 ROWS ONLY;

-- Segment-level physical reads (which table is being scanned)
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, VALUE
FROM V$SEGMENT_STATISTICS
WHERE STATISTIC_NAME = 'physical reads'
ORDER BY VALUE DESC
FETCH FIRST 20 ROWS ONLY;

-- SQL_IDs with multiple plan hashes (plan instability)
SELECT SQL_ID, COUNT(DISTINCT PLAN_HASH_VALUE) AS plan_count
FROM V$SQL
GROUP BY SQL_ID
HAVING COUNT(DISTINCT PLAN_HASH_VALUE) > 1
ORDER BY plan_count DESC
FETCH FIRST 20 ROWS ONLY;

-- Sessions currently waiting on scattered read, with SQL_ID and blocker
SELECT SID, SERIAL#, USERNAME, EVENT, SECONDS_IN_WAIT, SQL_ID, BLOCKING_SESSION
FROM V$SESSION
WHERE EVENT = 'db file scattered read' AND STATE = 'WAITING'
ORDER BY SECONDS_IN_WAIT DESC;

-- Compare scattered reads vs direct path reads vs single-block reads on the same instance.
-- Note: 'direct path read' also counts temp I/O, so correlate with TEMP usage before attributing.
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 IN ('db file scattered read', 'direct path read', 'db file sequential read');

How to diagnose it

1. Confirm scattered reads are the dominant signal. Run the second quick-check query. If db file scattered read is not in the top 3 non-idle waits, this is not the right incident.
2. Identify the offending segment. Use V$SEGMENT_STATISTICS with STATISTIC_NAME = 'physical reads'. A single table or index dominating is the regression signature. Many segments with similar read counts is a cache-sizing or workload-shift problem.
3. Identify the offending SQL. Cross-reference the segment from step 2 with the top-SQL-by-gets query. The same SQL_ID should appear at the top of both. If the SQL_ID has multiple PLAN_HASH_VALUEs in V$SQL, the regressed plan is the one with high gets_per_exec.
4. Verify the plan change. Compare the current plan with the previous one. On a licensed system, DBA_HIST_SQL_PLAN gives the historical plan. Without the Diagnostics Pack, the previous plan may already be aged out of V$SQL_PLAN.
5. Check for a triggering event. Look at the alert log and DBA_TAB_STATISTICS.LAST_ANALYZED around the onset time. Statistics gathering is the most common trigger. Parameter changes, index drops or invisibility, and version upgrades also cause regressions.
6. Decide on a fix path. If the previous plan is known and the SQL_ID is identified, a SQL Plan Baseline is the cleanest fix. If the plan must be forced urgently, a SQL Profile or SQL Patch is faster. If the index is genuinely missing, create it. Each path has tradeoffs covered below.

flowchart TD
    A["db file scattered read in top waits"] --> B{"Single segment dominates?"}
    B -- "Yes" --> C["Find SQL_ID via V$SQL by gets"]
    B -- "No" --> D["Check buffer cache size and working set growth"]
    C --> E{"Multiple PLAN_HASH_VALUE?"}
    E -- "Yes" --> F["Plan regression. Compare to old plan."]
    E -- "No" --> G["Missing index or stats-driven scan cost"]
    F --> H["SQL Plan Baseline or SQL Profile"]
    G --> I["Add index or fix statistics"]
    D --> J["Grow cache, isolate workload, or partition"]

Metrics and signals to monitor

Average latency is the key signal for separating storage problems from plan problems. If scattered-read average wait time is in the tens of milliseconds, the storage layer is the bottleneck. If average wait is normal but total wait time is high, the database is doing too many scans.

Fixes

The right fix depends on the cause. All of these assume the regressed SQL has already been identified via the diagnostic steps above.

Restore the known-good plan

If you have a known-good PLAN_HASH_VALUE from before the regression, capture it as a SQL Plan Baseline so the optimizer cannot deviate from it.

-- Load the known-good plan from the cursor cache.
-- This pins optimizer behavior for the SQL_ID going forward; test before applying in production.
BEGIN
  DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id          => '&good_sql_id',
    plan_hash_value => &good_plan_hash
  );
END;
/

SQL Plan Baselines are the intended stability mechanism. They persist across restarts and survive statistics gathering. The downside is adoption overhead: every critical SQL_ID needs to be captured proactively, not reactively. If you only enable baselines after a regression, you need the old plan still in the cursor cache or in AWR.

For an urgent incident where the previous plan is still in the cursor cache, a SQL Profile or SQL Patch can be applied faster than a baseline and will hold the system while a baseline is built.

Fix the missing index

If the diagnostic shows a query doing a full scan on a table where the predicate columns have no supporting index, the fix is to add the index. Before creating it:

• Verify the predicate is stable and selective. A full scan on a low-cardinality column is sometimes the correct plan.
• Check that the same SQL is not also scanning other large tables. A join order regression can manifest as a single scan but have multiple offenders.
• Use CREATE INDEX ... ONLINE to avoid blocking DML during the build. Expect brief contention on the table during the build regardless.

Watch for enq: TM - contention after creating indexes on tables with unindexed foreign keys. See the related guide on TM contention and unindexed FKs.

Address statistics

If the regression coincides with a stats-gather job, the cleanest path is to lock statistics on the affected table at the known-good state and re-gather only with a staged approach.

-- Lock statistics on the table to prevent auto-gather from changing them.
-- Locking is safe and reversible via DBMS_STATS.UNLOCK_TABLE_STATS.
EXEC DBMS_STATS.LOCK_TABLE_STATS('SCHEMA', 'TABLE_NAME');

Do not disable statistics gathering database-wide. That creates a slower, larger regression later. Lock at the table level, document why, and revisit on a planned change window.

Tuning the scan decision directly

DB_FILE_MULTIBLOCK_READ_COUNT controls how many blocks each scattered read fetches. Increasing it makes full scans cheaper per block and can make the optimizer prefer scans. Lowering it makes scans more expensive and biases the optimizer toward indexes. Treat this as a system-level nudge, not a fix for a specific regression.

OPTIMIZER_INDEX_COST_ADJ (default 100) makes index access paths appear cheaper relative to full scans when set below 100. Setting it too low causes the inverse problem: index scans where full scans would be cheaper, with db file sequential read dominating instead.

Both parameters are blunt instruments. Use them for a known workload class on a known database, not as a triage response to a single incident.

Forcing buffered reads with event 10949

On systems where direct path reads are causing problems (PGA pressure, repeated disk reads on cached data), event 10949 forces the buffered scattered-read path for serial full scans.

-- Session-level: disable serial direct path reads. Reversible by disconnecting the session.
ALTER SESSION SET EVENTS '10949 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Oracle’s 19c and 21c documentation explicitly recommends event 10949 to disable direct path read, so it is documented on those releases. Its interaction with undocumented _serial_direct_read is version-specific; validate the selected path with the session event counters or SQL trace before relying on it.

This is a workaround, not a fix. Buffered reads pollute the cache with scan data and can displace hot OLTP blocks. It is most useful as a temporary measure on a mixed-workload system where a specific scan is causing PGA pressure.

Prevention

• Capture SQL Plan Baselines proactively for top SQL. Reactive baselining only works if the old plan is still in the cursor cache.
• Monitor BUFFER_GETS divided by EXECUTIONS for the top 20 SQL_IDs. A 10x change is the earliest reliable regression signal.
• Stage statistics gathering. Use DBMS_STATS.DIFF_TABLE_STATS_IN_STALE or an equivalent staging approach to compare plans before publishing new stats.
• Do not rely on buffer cache hit ratio. It is one of the most misleading Oracle metrics. Oracle’s cache-ratio formula uses physical reads cache and cache logical-read counters, while aggregate physical-read statistics include direct and process-private-buffer I/O, so a workload shifting to direct path can evade a naive cache-ratio drop. Wait events are the correct primary signal.
• Track the direct path read threshold. The decision between buffered scattered reads and direct path reads depends on internal small-table thresholds, buffer cache size, and segment size, and the logic has changed across versions. Oracle does not document a single stable per-release cutoff; after a major upgrade, observe which scans shift paths with wait events and execution plans.

How Netdata helps

• The Oracle integration collects per-second wait-event latency including db file scattered read, direct path read, and db file sequential read, so you can see the shift between buffered and direct path reads in real time rather than reconstructing it from AWR samples.
• ML-based anomaly detection on BUFFER_GETS per execution for top SQL surfaces plan regressions within seconds of the new plan being chosen, before the wait-event totals grow enough to trigger static thresholds.
• Correlating scattered read waits with OS-level disk latency (per-device await, queue depth) separates “the storage is slow” from “the optimizer chose a bad plan” without a second tool.
• The same integration tracks logical reads per second, TPS, and active sessions, so a plan regression is visible as a correlated jump across all three signals rather than as a single wait-event spike.
• Per-PDB resource metrics in multitenant deployments isolate which tenant’s workload is driving the scans.

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 ’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
• Oracle hard parse storm: literal SQL, bind variables, and shared pool churn
• Oracle ’library cache: mutex X’ waits: parsing pressure and cursor contention