db file scattered read: Wait: multi-block physical read, typically a full table or index fast full scan
OracleWhat does it mean?
A session waits for multiple blocks (multiblock) to be read from disk. Usually occurs during a full table scan or index fast full scan.
Why does it happen?
Full table scans due to a missing suitable index, a wrong plan due to stale statistics, scans of very large tables, or the chosen access path falling back to a table scan.
How to fix it
Identify the SQL statements doing full scans and add the required indexes or ensure the existing index is used. Update statistics: DBMS_STATS.GATHER_TABLE_STATS. If a large scan is genuinely needed, consider parallel query or partitioning. Review DB_FILE_MULTIBLOCK_READ_COUNT and storage throughput.
More from this engine
- enq: TX - row lock contentionWait: session blocked on a row-level lock held by another transaction
- log file syncWait: session waiting for LGWR to flush redo to disk on commit
- db file sequential readWait: single-block physical read, typically an index or table lookup
- buffer busy waitsWait: contention for a buffer already being read or modified by another session
- gc buffer busyWait (RAC): contention for a cache-fusion block being transferred between instances
- library cache lockWait: contention on a library cache object handle (parse/DDL serialization)
- ORA-00604error occurred at recursive SQL level