What is the difference between DBFile Sequential and Scattered Reads?
Both "db file sequential read" and "db file scattered read" events
signify time waited for I/O read requests to complete. Time is
reported in 100's of a second for Oracle 8i releases and below, and
1000's of a second for Oracle 9i and above. Most people confuse these
events with each other as they think of how data is read from disk.
Instead they should think of how data is read into the SGA buffer
cache.
db file sequential read:
A sequential read operation reads data into contiguous memory (usually
a single-block read with p3=1, but can be multiple blocks). Single
block I/Os are usually the result of using indexes. This event is also
used for rebuilding the controlfile and reading datafile headers
(P2=1). In general, this event is indicative of disk contention on
index reads.
db file scattered read:
Similar to db file sequential reads, except that the session is
reading multiple data blocks and scatters them into different
discontinuous buffers in the SGA. This statistic is NORMALLY
indicating disk contention on full table scans. Rarely, data from full
table scans could be fitted into a contiguous buffer area, these waits
would then show up as sequential reads instead of scattered reads.
The following query shows average wait time for sequential versus
scattered reads:
prompt "AVERAGE WAIT TIME FOR READ REQUESTS" select a.average_wait "SEQ READ", b.average_wait "SCAT READ" from sys.v_$system_event a, sys.v_$system_event b where a.event = 'db file sequential read' and b.event = 'db file scattered read';
- Login to post comments

