Understanding the Oracle db file sequential read Wait Event

on April 6, 2013

The db file sequential read wait event has three parameters: file#, first block#, and block count. In Oracle Database 10g, 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.

Common Causes, Diagnosis, and Actions

The db file sequential read wait event is initiated by SQL statements (both user and recursive) that perform single-block read operations against indexes, rollback (or undo) segments, and tables (when accessed via rowid), control files and data file headers. This wait event normally appears as one of the top five wait events, according to systemwide waits.

Physical I/O requests for these objects are perfectly normal, so the presence of the db file sequential read waits in the database does not necessarily mean that there is something wrong with the database or the application. It may not even be a bad thing if a session spends a lot of time on this event. In contrast, it is definitely bad if a session spends a lot of time on events like enqueue or latch free. This is where this single-block read subject becomes complicated. At what point does the db file sequential read event become an issue? How do you define excessive? Where do you draw the line? These are tough questions, and there is no industry standard guideline. You should establish a guideline for your environment. For example, you may consider it excessive when the db file sequential read wait represents a large portion of a process response time. Another way is to simply adopt the nonscientific hillbilly approach—that is, wait till the users start screaming.

You can easily discover 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 nonidle 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 nonidle events, it prevents you from being blindsided. You may find another wait event which is of a greater significance. Based on the following example, SID# 192 deserves your attention and should be investigated:

Fig 5-1

Fig 5-2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Learn more about how to minimize waits on db file sequential reads in Part 2 of this article.

Related Posts

Comments

  2. Hi Peter,

    This article is a complimentary excerpt from Oracle Wait Interface: A Pracitcal Guide to Performance Diagnostics & Tuning, published by McGraw- Hill Education. Here is a similar query you can run that will give you all the non-idle waits for a specific SID:

    select
    se.sid,
    se.event,
    sum(se.total_waits),
    sum(se.total_timeouts),
    sum(se.time_waited/100) time_waited
    from
    v$session_event se,
    v$session sess
    where
    sess.sid = se.sid
    and sess.wait_class != ‘Idle’
    group by
    se.sid, se.event
    order by 1,3 desc;

    Reply

Trackbacks

Leave a Reply