DB file sequential read

A db file sequential read (Oracle Wait Event - single block read into one SGA buffer) is a sequential read from the database is performed, This event is also used for rebuilding the control file, dumping datafile headers, and getting the database file headers.

use v$session_wait View to find information about Waiting:
Parameters:
P1 = file#
P2 = block#
P3 = blocks

file# This is the file# of the file that Oracle is trying to read

block# This is the starting block number in the file from where Oracle starts reading the blocks.

blocks This parameter specifies the number of blocks that Oracle is trying to read from the file# starting at block#. This is usually "1" but if P3 > 1 then this is a multiblock read. Multiblock "db file sequential read"s may be seen in earlier Oracle versions when reading from a SORT (TEMPORARY) segments.

We can see this wait event on v$session_event View:

SELECT sid, total_waits, time_waited FROM v$session_event WHERE event='db file sequential read' and total_waits> 0 ORDER BY 3,2

SID TOTAL_WAITS TIME_WAITED
---------- ----------- -----------
518 54485 43431
532 229766 77845

Anyway, We can see Wait event in the present time:

SELECT SID, EVENT, P1, P2 , P3 FROM v$session_wait where event='db file sequential read'

SID EVENT P1 P2 P3
---------- ------------------------ ---------- ---------- ----------
620 db file sequential read 46 6072 1


SELECT tablespace_name, file_name FROM dba_data_files WHERE file_id = 46

TABLESPACE_NAME FILE_NAME
------------------------------ ----------------------------------------------
XXX_PART_C +DATA/db/datafile/XXX_part_c.300.617885887


block# ->
If the file is NOT a TEMPFILE then the following query should show the name and type of the segment:

SELECT owner , segment_name , segment_type FROM dba_extents WHERE file_id = 46 AND 6072 BETWEEN block_id AND block_id + blocks -1

OWNER SEGMENT_NAME SEGMENT_TYPE
--------------------- ----------------- ------------------
USER XXX TABLE PARTITION

About Partition use:

SELECT owner , segment_name , segment_type, partition_name FROM dba_extents WHERE file_id = 46 AND 6072 BETWEEN block_id AND block_id + blocks -1

OWNER SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME
------------------------------ ---------------- ------------------ -------------------
USER XXX TABLE PARTITION SYS_P1852


When see waiting time "db file sequential read" event, we should investigate V$SQLAREA to see SQL statements performing many disk reads. Cross-check I/O system and V$FILESTAT for poor read time.

We can reduce a waiting:
- Check SQL statements [use index scans] , Tune SQL Statements.
- Check buffer cache, A larger buffer cache can help; (increase SGA)
- Partition TABLE and INDEX , that can help to reduce the amount of data you need to look at.

Enjoy!