DB File Sequential Read Wait Event

The db file sequential read wait event has three parameters: file#, first block#, and block count. In Oracle Database 10 g , this wait event falls under the User I/O wait class. Keep the following key thoughts in mind when dealing with the db file sequential read wait event.

The Oracle process wants a block that is currently not in the SGA, and it is waiting for the database block to be read into the SGA from disk.
The two important numbers to look for are the TIME_WAITED and AVERAGE_WAIT by individual sessions.
Significant db file sequential read wait time is most likely an application issue.

You can determine which session has high TIME_WAITED on the DB file sequential read wait event from the V$SESSION_EVENT view. The TIME_WAITED must be evaluated with the LOGON_TIME and compared with other non-idle events that belong to the session for a more accurate analysis. Sessions that have logged on for some time (days or weeks) may accumulate a good amount of time on the DB file sequential read event. In this case, a high TIME_WAITED may not be an issue. Also, when the TIME_WAITED is put in perspective with other non-idle events, it prevents you from being blindsided . You may find another wait event which is of a greater significance.

SELECT  a.sid, a.event, a.time_waited,
        a.time_waited / c.sum_time_waited * 100 pct_wait_time,
        ROUND((sysdate - b.logon_time) * 24) hours_connected
FROM    v$session_event a, v$session b,
        (SELECT sid, SUM(time_waited) sum_time_waited
        FROM   v$session_event
        WHERE  event NOT IN (
                   'Null event',
                   'client message',
                   'KXFX: Execution Message Dequeue - Slave',
                   'PX Deq: Execution Msg',
                   'KXFQ: kxfqdeq - normal deqeue',
                   'PX Deq: Table Q Normal',
                   'Wait for credit - send blocked',
                   'PX Deq Credit: send blkd',
                   'Wait for credit - need buffer to send',
                   'PX Deq Credit: need buffer',
                   'Wait for credit - free buffer',
                   'PX Deq Credit: free buffer',
                   'parallel query dequeue wait',
                   'PX Deque wait',
                   'Parallel Query Idle Wait - Slaves',
                   'PX Idle Wait',
                   'slave wait',
                   'dispatcher timer',
                   'virtual circuit status',
                   'pipe get',
                   'rdbms ipc message',
                   'rdbms ipc reply',
                   'pmon timer',
                   'smon timer',
                   'PL/SQL lock timer',
                   'SQL*Net message from client',
                   'WMON goes to sleep')
       HAVING SUM(time_waited) > 0 GROUP BY sid) c
WHERE    a.sid = b.sid
AND      a.sid = c.sid
AND      a.time_waited > 0
AND      a.event = 'db file sequential read'
ORDER BY hours_connected desc, pct_wait_time;