Db file sequential read
The db file sequential read wait event means that Oracle is waiting while doing a single-block I/O read. This is the case when reading an index.
Like all wait events the columns P1, P2, P3 give us the information needed to diagnose the waiting.
Tip: A db sequential read is usually a single-block read, although it is possible to see sequential reads for more than one block (See P3 which is the number of blocks read).
This wait may also be seen for reads from datafile headers (P2=1 indicates a file header read) .
Parameters:Some advise
P1 = file#
P2 = block#
P3 = blocks
file# This is the file# of the file that Oracle is trying to read
from. In Oracle8/9 it is the ABSOLUTE file number.
block# This is the starting block number in the file from where
Oracle starts reading the blocks. Typically only one block is
being read.
To find the object that Oracle doing the I/O use one of
the two following ways
SELECT owner, segment_type, segment_name, partition_name,
tablespace_name
FROM dba_extents
WHERE :P2 BETWEEN block_id AND (block_id + blocks - 1)
AND file_id = :P1;
Or even better
SELECT a.SID, c.obj, c.FILE#, c.dbablk
FROM v$session_wait a, x$bh c
WHERE a.p1 = c.FILE#(+)
AND a.p2 = c.dbablk(+)
AND a.event = 'db file sequential read'
AND a.SID = :sid_waiting;
blocks This parameter specifies the number of blocks that Oracle is
trying to read from the file# starting at block#. This is
usually "1" but if P3 > 1 then this is a multiblock read.
Multiblock "db file sequential read"s may be seen in
earlier Oracle versions when reading from a SORT (TEMPORARY)
segments.
Wait Time:
The IO is generally issued as a single IO request to the OS - the wait
blocks until the IO request completes.
Note than an Oracle read request to the OS may be satisfied from an
OS file system cache so the wait time may be very small.
If you see this wait event then general you are in a good position. All the databases (especially the very big systems) have some wait events and doing IO for an index scan is usual.
But SEC (seconds in waiting) from this query must be 0 or close to zero (1,2,3,4). If you see time waiting to increasing then you must tune the index I/O
How to improve performance for db file sequential read
The steps, starting first from that might have better results, are:
1. Rebuild the index (Fast index rebuild)
***To eliminate this case you have to examine the state of the index.
If it is "compact" with no empty holes then there is no need to rebuild it.
Find index skewed, rebuild
Find indexes browning, rebuild
2. Tune SQL to use a better index
3. Distribute the index in different filesystems to reduce contention for I/O (Disk I/O)
4. Increase the db_cache_size
Embercadero has really nice explanation on this,try below site:
https://sites.google.com/site/embtdbo/wait-event-documentation/oracle-io-waits
No comments:
Post a Comment