Wait event db file sequential read

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? Would a full table scan be more efficient? Regardless of the method, often the job must be rerun to properly identify the root cause of the bottleneck. Below are some examples for interactive monitoring. If it is an index, and the plan calls for table access by index rowid, then it may be worthwhile to check the clustering factor.

If it approaches the number of rows in the table, the rows are randomly ordered. In such a case, it is unlikely that index entries in the same leaf block will point to rows in the same data blocks.

Interactive monitoring has serious limitations. It is a slow manual process and many important events can potentially be missed between sampling. It is also not practical for the DBA to "baby sit" a long-running job. The session waits while 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.

Tags: Oracle Spotlight on Oracle Wiki. Written by Quest Software. 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. 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. You may find another wait event which is of a greater significance.

Based on the following example, SID deserves your attention and should be investigated:. Learn more about how to minimize waits on db file sequential reads in Part 2 of this article. Here is a similar query you can run that will give you all the non-idle waits for a specific SID:.

In this part, we explore how to tune for this wait. This may or may not be a problem depending on the […]. Save my name, email, and website in this browser for the next time I comment.