Session waiting db file sequential read

Take the preceding SID again , for example. The centiseconds wait time may be caused by one long-running or many fast SQL statements. The latter case may not be an issue. Furthermore, the SQL statement that is currently running may or may not be the one that is responsible for the waits.

This is why interactive diagnosis without historical data is often unproductive. Due to these limitations, you may have to identify and trace the session the next time around to nail down the offending SQL statement. 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.

Leave a Reply Cancel reply Enter your comment here Fill in your details below or click an icon to log in:. Email Address never made public. Follow Following. Smart way of Technology Join other followers. Sign me up. Mar 12, AM by Quest Software.

The db file sequential read and db file scattered read events can be confusing at first, as the sequential read is associated with index read and the scattered read has to do with full table scans. It seems like they ought to be the other way around. The db file sequential read is thus named because it reads blocks into contiguous memory, and the db file scattered read gets its name because it reads multiple blocks and scatters them into the Buffer Cache.

The following discusses the common causes of db file sequential read waits that result from index reads. The issue is not index reads, rather it is the waits that are caused by excessive and unwarranted index reads. The idea is to avoid hot spots. In this case, the DBA needs to determine which index is being read the most from the P1 and P2 values, identify the SQL statement, generate and examine the explain plan against the SQL predicates, and advise on tuning.

Should the query access data through an index?