Sunday, March 25, 2007

Db sequential file read

This is one of the most common wait events you would see in your top 5 wait event list in your statspack report, and it is also irritating to see such a thing. i started my blog with this wait event because this has also troubled me a lot in my DB Tuning projects.
Its important to know why a process is waiting on this event before you apply any solution. The "db sequential file read" wait event an out come of a process trying to read an index or a file header which is on the disk and has not been cached into the buffer, hence there is a lot of I/O happening to read these entries and hence the process has to wait till the entries have been put into the buffer.
Here i use the word's like index / file headers as you would notice that the reads that happen are normally a 1 block operation (This can be verified from V$session_wait (P3).

Hope i was clear in explaining the wait event if not do leave in your query in the comments box and i will get back on it.

Solution to reduce this wait event:
  • Check the explain plan of the query and see if there are unnecessary scans on any indexes. by unnecessary i mean if there are additional indexes than needed, if so you may go ahead and drop them. But this requires some experience so consult with some one and then proceed with this step.
  • If possible create a keep pool in your SGA and store those indexes in it.
  • Increasing the Buffer cache may help (Not necessary in some cases).

Do let me know if this article was helpful or not your feedback will help be to write better stuff.

Cheers!!!

No comments: