DB file sequential read - wait event

When performance problems happen it's important to find out where Oracle/Application is spending most of the time. One of the tools that you can use for diagnosing the problem is script that is querying V$SESSION_WAIT view. V$SESSION_WAIT displays the resources or events for which active sessions are waiting.

I've made script that you can use for displaying wait events for which active sessions are currently waiting

col SID for 99999
col STATE for a9
col EVENT for a48
col P1_TXT for a20
col P2_TXT for a20
col p3_TXT for a25
col SECONDS_IN_WAIT for 999999999
col WAIT_CLASS for a15

SELECT  sw.sid sid,
CASE
WHEN sw.state != 'WAITING'
THEN 'WORKING'
ELSE 'WAITING'
END AS state,
CASE
WHEN sw.state != 'WAITING'
THEN 'On CPU / runqueue'
ELSE event
END AS event                                            ,
sw.seconds_in_wait                                         ,
DECODE(sw.p1text,NULL,'n/a',sw.p1text ||'=' ||sw.p1) p1_txt,
DECODE(sw.p2text,NULL,'n/a',sw.p2text ||'=' ||sw.p2) p2_txt,
DECODE(sw.p3text,NULL,'n/a',sw.p3text ||'=' ||sw.p3) p3_txt,
en.wait_class
FROM    v$session_wait sw,
v$event_name en
WHERE   sw.event = en.name
AND sid     IN
(SELECT sid
FROM    v$session
)
ORDER BY seconds_in_wait DESC;

RESULT:

SID STATE EVENT SECONDS_IN_WAIT P1_TXT P2_TXT P3_TXT WAIT_CLASS
------ --------- ------------------------------------------------ --------------- -------------------- -------------------- ------------------------- -----------
...
373 WAITING db file sequential read 4 file#=4 block#=14943 blocks=1 User I/O
375 WAITING db file sequential read 3 file#=4 block#=827973 blocks=1 User I/O
...

The most significant wait event in this specified situation is db file sequential read so I removed other events from the result.

Db file sequential read wait event happens when process has issued an I/O request to read one block from a data file into the buffer cache, and is waiting for the operation to complete. This typically happens during an index lookup or a fetch from a table by ROWID when the required data block is not already in memory. Do not be misled by the confusing name of this wait event!

A sequential read is usually single block read, and you can easily find blocks being waited on using information from script above - querying V$SESSION_WAITS.

You can use this script below for searching segments by file# and block#.

column owner format a15
column tablespace_name format a35
column file_id format 999999999
column segment_name format a35
column block_id format 9999999999

ACCEPT FILEID PROMPT "[Enter File#]: "
ACCEPT BLCKNO PROMPT "[Enter Block#]: "

select owner,
tablespace_name,
file_id,
segment_name,
block_id
from dba_extents
where file_id=&&FILEID
and block_id between &&BLCKNO and &&BLCKNO+blocks;

RESULT:

OWNER TABLESPACE_NAME FILE_ID SEGMENT_NAME BLOCK_ID
--------------- ----------------------------------- ---------- ----------------------------------- -----------
SRVCEA USERS 4 DEVICES 14945
SRVCEA USERS 4 LOGS 14985

Here you can see what were the objects being waited for.

There are various operations that you can do to reduce "db file sequential read" wait events. You cannot avoid block reads, but you can minimize un-necessary IO. Usually the problem is application design and if that is the case you should concentrate on tweaking application if possible. Scripts above can help you in finding better solutions.
Besides that larger buffer cache might help or reorganising data.




RELATED DOCUMENTS:
Interpreting Wait Events to Boost System Performance
Ask Tom - Wait Event: DB sequential read