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 .
No comments:
Post a Comment
Thank you for your Suggestions...