DB File Sequential Read

An Oracle session logs the db file sequential read wait event when it has to wait for a single-block I/O read request to complete. Oracle issues single-block I/O read requests when reading from indexes, rollback segments, sort segments, control files, datafile headers and tables (when tables are accessed via rowids). A sequential read is usually a single-block read, although it is possible to see sequential reads for more than one block (See P3).  To determine the actual object being waited can be checked by the p1, p2, p3 info in v$session_wait.

SELECT p1 "file#", p2 "block#", p3 "class#",event

FROM v$session_wait

WHERE event IN ('db file sequential read');

Select * from   v$session_event

where  event = 'db file sequential read'

order by time_waited;

Select segment_name, partition_name, segment_type, tablespace_name

from   dba_extents a, v$session_wait b

where  b.p2 between a.block_id and (a.block_id + a.blocks - 1)

and    a.file_id  = b.p1

and    b.event    = 'db file sequential read';

Select a.sid, a.serial#, a.username, a.osuser, b.sql_text

from   v$session a, v$sqltext b

where  a.sql_hash_value = b.hash_value

and    a.sql_address    = b.address and    a.sid in (select sid from   v$session_wait

where  event = 'db file sequential read')

order by a.sid, b.hash_value, b.piece;

Note: Where P1 = file#, P2 = block#, P3 = blocks 9 (should be 1)

Generally the entire database having some wait event doing IO for index scan usually. But if you see seconds in waiting greater then 0, you must tune index I/O.

To reduce this wait event follow the below points:

1.      Tuning SQL statements to reduce unnecessary I/O request is the only guaranteed way to reduce "db file sequential read" wait time.

2.      Distribute the index in different file system to reduce the contention for I/O
Tuning Physical devices, the data on different disk to reduce the I/O.

3.      Use of Faster disk reduces the unnecessary I/O request.
Increase db_block_buffers or larger buffer cache sometimes can help.