Tuesday, December 13, 2011

How to Reduce DB File Sequential Read Wait


DB File Sequential Read wait event occurs when we are trying to access data using index and oracle is waiting for the read of index block from disk to buffer cache to complete.  A sequential read is a single-block read.Single block I/Os are usually the result of using indexes. Rarely, full table scan calls could get truncated to a single block call due to extent boundaries, or buffers already present in the buffer cache.Db file sequential read wait events may also appear when undo blocks are read from disk in order to provide a consistent get(rarely).

To determine the actual object being waited can be checked by the p1, p2, p3 info in v$session_wait .  A sequential read is usually a single-block read, although it is possible to see sequential reads for more than one block (See P3). This wait may also be seen for reads from datafile headers (P2 indicates a file header read) ,where p1,p2 and p3 gives the the absolute file number ,the block being read ,and  the number of blocks (i.e, P3 should be 1) respectively. 

Block reads are fairly inevitable so the aim should be to minimise un-necessary IO. This is best achieved by good application design and efficient execution plans. Changes to execution plans can yield orders of magnitude changes in performance.Hence to reduce this wait event follow the below points .

1.) Tune Oracle - tuning SQL statements to reduce unnecessary I/O request is the only guaranteed way to reduce "db file sequential read" wait time.
2.) Tune Physical Devices - Distribute(stripe) the data on diferent disk to reduce the i/o . Logical distribution is useless. "Physical" I/O performance is only governed by "independency of devices".
3.) Faster Disk - Buy the faster disk to reduce the unnecessary I/O request .
4.) Increase db_block_buffers - A larger buffer cache can (not will, "might") help .


Reference :: From  Asktom site


Enjoy     :-) 


6 comments:

Unknown said...

One of my setup screen in my Oracle Financials Application was running very slow while querying, updatin ,& saving.
When i checked the trace file it showing me msg

Wait# 45 nam='db file scattered read'

how to solve this , can you answer me in detail, your help would be appreciated.

thanks

Dirkox said...

Ok. Thanks.

How can I Know which SQL is making to grow my "db file seq read" wait event?

Dirkox said...

Thanks.

How can I know the SQL responsible for the "db seq file read" wait events to tune it?

Anonymous said...

this appears to be a post of Ask Tom

NEERAJ VISHEN said...

Yes ...

This is one of the very useful comments by Tom on his site and i have decided to share with others too. :) :)


Anonymous said...

The SQL statement associated with this event can be obtained using this query:

select a.sid, a.serial#, a.username, a.osuser, b.sql_text
from v$session a, v$sqltext b
where a.sql_hash_value = b.hash_value
and a.sql_address = b.address
and a.sid in (select sid
from v$session_wait
where event = ‘db file sequential read’)
order by a.sid, b.hash_value, b.piece;