db file scattered read - Diagnose Oracle Full Table Scans

db file scattered read

Overview

Wait Event Class: User I/O

Parameters: file#, block#, blocks (number of blocks in the multi-block read, up to DB_FILE_MULTIBLOCK_READ_COUNT)

db file scattered read fires whenever Oracle reads multiple consecutive database blocks from disk in a single I/O operation. The name “scattered” describes where the blocks land in memory: Oracle scatters them into non-contiguous buffers in the buffer cache (unlike direct path reads which write sequentially to PGA memory).

This wait event is associated with:

• Full table scans (FTS) — Oracle reads the entire segment in large chunks
• Fast full index scans (FFIS) — reading all index blocks without following the B-tree structure
• Index fast full scans — used when an index covers all columns needed by the query

The number of blocks read per I/O is controlled by the DB_FILE_MULTIBLOCK_READ_COUNT parameter (default is typically 128 on modern systems, meaning up to 1 MB per read on an 8 KB block size database).

Why It Matters

Unlike db file sequential read (which is expected for OLTP index lookups), heavy db file scattered read waits often signal:

• Missing indexes causing avoidable full table scans
• Queries accessing far more data than necessary
• Analytics workloads running on OLTP systems without proper partitioning or parallel query configuration
• Reports or batch jobs competing with transactional workloads

---

When This Wait Is a Problem

Normal vs. Concerning Scenarios

Scenario	Assessment
Small tables (< a few hundred blocks)	Full scan is expected and optimal — no action needed
Analytics / reporting on large tables with parallel query	Normal — ensure I/O subsystem can sustain the throughput
OLTP queries scanning large tables repeatedly	Problem — likely missing index
Total time in this event > 20% of DB time on OLTP system	Investigate — full scans may be avoidable
Average wait > 10 ms	Storage bottleneck — even multi-block reads should be fast

The Optimizer’s Decision

Oracle performs a full table scan when:

1. No suitable index exists
2. The query’s selectivity is too low (optimizer estimates full scan is cheaper than index + ROWID)
3. The table is too small to warrant an index lookup
4. FULL hint is used explicitly
5. Statistics are stale and the optimizer miscalculates cost

Understanding why a full scan was chosen is essential before deciding whether to add an index.

---

Diagnostic Queries

1. System-Level Statistics for Multi-Block Reads

-- Instance-level scattered read statistics
SELECT
    event,
    total_waits,
    total_timeouts,
    ROUND(time_waited / 100, 2)           AS total_secs_waited,
    ROUND(average_wait * 10, 2)           AS avg_wait_ms,
    ROUND(time_waited * 100.0 /
          NULLIF((SELECT SUM(time_waited)
                  FROM v$system_event
                  WHERE wait_class NOT IN ('Idle')), 0), 2) AS pct_db_time
FROM v$system_event
WHERE event = 'db file scattered read';

-- Compare all User I/O waits
SELECT
    event,
    total_waits,
    ROUND(average_wait * 10, 2) AS avg_ms,
    ROUND(time_waited / 100, 2) AS total_secs
FROM v$system_event
WHERE wait_class = 'User I/O'
ORDER BY time_waited DESC;

2. Currently Active Sessions Doing Multi-Block Reads

-- Sessions currently waiting on scattered reads
SELECT
    sw.sid,
    sw.serial#,
    s.username,
    s.sql_id,
    s.program,
    sw.p1                          AS file_number,
    sw.p2                          AS starting_block,
    sw.p3                          AS blocks_in_read,
    sw.seconds_in_wait,
    f.name                         AS data_file
FROM v$session_wait  sw
JOIN v$session       s  ON sw.sid  = s.sid
JOIN v$datafile      f  ON sw.p1   = f.file#
WHERE sw.event = 'db file scattered read'
ORDER BY sw.seconds_in_wait DESC;

3. ASH Analysis — Identify Objects Driving Full Scans

-- Top objects and SQL causing scattered reads (last hour)
-- Requires Diagnostics Pack license
SELECT
    ash.sql_id,
    ash.current_obj#,
    o.object_name,
    o.object_type,
    o.owner,
    COUNT(*)                              AS ash_samples,
    COUNT(*) * 10                         AS estimated_wait_secs,
    ROUND(COUNT(*) * 100.0 /
          SUM(COUNT(*)) OVER (), 2)       AS pct_of_scattered_waits
FROM v$active_session_history ash
LEFT JOIN dba_objects         o ON ash.current_obj# = o.object_id
WHERE ash.event        = 'db file scattered read'
  AND ash.sample_time  > SYSDATE - 1/24
GROUP BY ash.sql_id, ash.current_obj#, o.object_name, o.object_type, o.owner
ORDER BY ash_samples DESC
FETCH FIRST 20 ROWS ONLY;

4. File-Level Multi-Block Read Statistics

-- Which datafiles are serving the most multi-block reads?
SELECT
    f.name                                          AS file_name,
    ts.name                                         AS tablespace,
    fs.phyrds                                       AS total_phys_reads,
    fs.phyblkrd                                     AS total_blocks_read,
    fs.phyrds - fs.singleblkrds                    AS multiblock_reads,
    fs.phyblkrd - fs.singleblkrds                  AS multiblock_blocks_read,
    ROUND(fs.readtim / NULLIF(fs.phyrds, 0) / 10, 4) AS avg_read_ms,
    ROUND((fs.phyrds - fs.singleblkrds) * 100.0 /
          NULLIF(fs.phyrds, 0), 2)                  AS pct_multiblock
FROM v$filestat   fs
JOIN v$datafile   f  ON fs.file# = f.file#
JOIN v$tablespace ts ON f.ts#    = ts.ts#
WHERE fs.phyrds > 0
ORDER BY (fs.phyrds - fs.singleblkrds) DESC
FETCH FIRST 15 ROWS ONLY;

5. Segment-Level Statistics — Confirm Full Scan Offenders

-- Segment-level physical reads from the shared pool
SELECT
    owner,
    object_name,
    object_type,
    statistic_name,
    value
FROM v$segment_statistics
WHERE statistic_name IN ('physical reads', 'physical reads direct')
  AND value > 0
ORDER BY value DESC
FETCH FIRST 20 ROWS ONLY;

---

Root Causes

1. Missing Index on a High-Cardinality Column

The most common cause of unwanted full table scans is a missing index on a column used in a WHERE clause. When Oracle cannot use an index, it must scan every block in the segment.

Example: A TRANSACTIONS table with 50 million rows has no index on ACCOUNT_ID. Any query filtering by account must read the entire table.

Detection: Pull the SQL_ID from ASH, examine its execution plan with DBMS_XPLAN, look for TABLE ACCESS FULL on large tables without a PARTITION RANGE above it.

2. Low-Selectivity Predicate Making Index Uneconomical

The optimizer correctly chooses a full scan when a query’s WHERE clause is not selective enough. For example, querying STATUS = 'ACTIVE' where 80% of the table is active — an index would require reading most of the table anyway, so a full scan with multi-block reads is cheaper.

In these cases, the full scan is correct behavior. Tuning options are partitioning (partition pruning eliminates most blocks) or a bitmap index (for very low-cardinality columns in data warehouse environments).

3. Stale or Missing Statistics

Without current statistics, the optimizer cannot accurately estimate selectivity or table size. It may choose a full scan when an index would be faster, or vice versa. Particularly problematic after bulk loads that dramatically change table size.

-- Check last statistics collection for a table
SELECT
    owner,
    table_name,
    num_rows,
    last_analyzed,
    SYSDATE - last_analyzed AS days_since_analyzed
FROM dba_tables
WHERE owner = 'YOUR_SCHEMA'
  AND table_name = 'YOUR_TABLE';

4. Fast Full Index Scans (Intended Behavior)

When a query needs all rows and the required columns are all in an index, Oracle may perform a fast full index scan — reading all index blocks with multi-block I/O. This generates db file scattered read on the index segment, not the table. This is often optimal and may not need tuning.

5. DB_FILE_MULTIBLOCK_READ_COUNT Too Low

If DB_FILE_MULTIBLOCK_READ_COUNT is set below the optimal value for the storage system, Oracle issues more I/O operations than necessary per full scan. On modern storage, 128 (1 MB with 8 KB blocks) is a reasonable default. Some systems benefit from higher values.

6. Parallel Query Generating Many Concurrent Multi-Block Reads

Parallel query slaves each generate their own db file scattered read waits as they process table partitions concurrently. High total wait time in this event during parallel operations is normal — evaluate it by looking at elapsed time rather than cumulative wait time.

---

Resolution Steps

Resolution for Missing Indexes

-- Step 1: Get the problem SQL from ASH
SELECT sql_id, COUNT(*) AS samples
FROM v$active_session_history
WHERE event = 'db file scattered read'
  AND sample_time > SYSDATE - 1
GROUP BY sql_id
ORDER BY samples DESC;

-- Step 2: Review the execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));

-- Step 3: Check existing indexes on the problem table
SELECT
    index_name,
    column_name,
    column_position,
    status,
    visibility
FROM dba_ind_columns
WHERE table_owner = 'SCHEMA'
  AND table_name  = 'TABLE_NAME'
ORDER BY index_name, column_position;

-- Step 4: Create an appropriate index
-- For selective single-column predicate:
CREATE INDEX idx_transactions_acct_id
    ON transactions(account_id)
    TABLESPACE indexes
    ONLINE;

-- For covering index (eliminates table access entirely):
CREATE INDEX idx_trans_acct_date_amt
    ON transactions(account_id, txn_date, amount)
    TABLESPACE indexes
    ONLINE;

Resolution for Stale Statistics

-- Re-gather stats on a specific table with full scan estimation
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => 'YOUR_SCHEMA',
    tabname    => 'YOUR_TABLE',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade    => TRUE,
    degree     => 4,
    no_invalidate => FALSE
);

-- Verify stats were collected
SELECT table_name, num_rows, blocks, last_analyzed
FROM dba_tables
WHERE owner = 'YOUR_SCHEMA' AND table_name = 'YOUR_TABLE';

Tuning DB_FILE_MULTIBLOCK_READ_COUNT

-- Check current setting
SHOW PARAMETER db_file_multiblock_read_count;

-- Calculate maximum effective value (OS limit / db_block_size)
-- Most systems support at least 128 for 8KB blocks (1MB reads)
-- Increase if I/O subsystem supports larger sequential reads
ALTER SYSTEM SET db_file_multiblock_read_count = 128 SCOPE=BOTH;

-- Verify the effective maximum I/O size
SELECT
    name,
    value,
    description
FROM v$parameter
WHERE name IN ('db_file_multiblock_read_count', 'db_block_size');

Using Partitioning to Reduce Full Scan Scope

-- If full scans are unavoidable, partition the table so only relevant partitions are scanned
-- Range partition by date (most common)
CREATE TABLE transactions_partitioned
    PARTITION BY RANGE (txn_date) (
        PARTITION p2024_q1 VALUES LESS THAN (DATE '2024-04-01'),
        PARTITION p2024_q2 VALUES LESS THAN (DATE '2024-07-01'),
        PARTITION p2024_q3 VALUES LESS THAN (DATE '2024-10-01'),
        PARTITION p2024_q4 VALUES LESS THAN (DATE '2025-01-01'),
        PARTITION p_future  VALUES LESS THAN (MAXVALUE)
    )
AS SELECT * FROM transactions;
-- Queries with date range predicates will only scan relevant partitions

---

Prevention & Tuning

1. Proactive Index Review

Regularly review AWR Top SQL for TABLE ACCESS FULL on large tables. Use SQL Access Advisor for recommendations:

-- Use SQL Tuning Advisor for a problem SQL
DECLARE
    l_task_name VARCHAR2(30) := 'TUNE_FULL_SCAN_SQL';
    l_sql_tune_task_id VARCHAR2(100);
BEGIN
    l_sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(
        sql_id      => '&problem_sql_id',
        scope       => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
        time_limit  => 60,
        task_name   => l_task_name
    );
    DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => l_task_name);
    DBMS_OUTPUT.PUT_LINE(DBMS_SQLTUNE.REPORT_TUNING_TASK(l_task_name));
END;
/

2. Automatic Statistics Collection

Ensure the GATHER_STATS_JOB (or Automatic Statistics Collection in 12c+) is enabled and running. Monitor tables that receive bulk loads and gather stats immediately after large inserts.

3. Track Full Scan Rate Over Time

-- Monitor full scan rates from AWR
SELECT
    s.snap_id,
    TO_CHAR(s.begin_interval_time, 'YYYY-MM-DD HH24:MI') AS snap_time,
    st.value - LAG(st.value) OVER (ORDER BY s.snap_id)   AS table_scans_in_interval
FROM dba_hist_sysstat   st
JOIN dba_hist_snapshot  s  ON st.snap_id    = s.snap_id
                           AND st.dbid      = s.dbid
                           AND st.instance_number = s.instance_number
WHERE st.stat_name = 'table scans (long tables)'
  AND s.begin_interval_time > SYSDATE - 7
ORDER BY s.snap_id;

4. Evaluate Parallel Query for Legitimate Large Scans

For analytics workloads where large scans are expected and acceptable, configure parallel query to maximize throughput rather than trying to eliminate the scans:

-- Enable parallel query for a specific table (analytics tier)
ALTER TABLE large_fact_table PARALLEL 8;

-- Or use query-level hint
SELECT /*+ PARALLEL(t, 8) */ * FROM large_fact_table t WHERE ...;

---

Related Wait Events

• db file sequential read — Single-block I/O for index lookups and ROWID access
• direct path read — Direct I/O bypassing the buffer cache (parallel scans, temp reads)
• buffer busy waits — Multiple sessions contending for the same buffer
• latch: cache buffers chains — Hot buffer chain latches, often related to hot blocks from full scans