|
Category :
Database
Resources -> Database Administration -> Wait Events
DB Version
:
Oracle 8i /
Oracle 9i
OS Details :
Sun Solaris9
Reference
: Oracle Metalink
Wait
Event
:
db file sequential read
This
signifies a wait for an I/O read request to complete and usually
occurs when oracle's foreground process is doing a sequential read
from one of the database files. Usually this is a single block table
access as a result of getting a rowid from an index read. This wait
may also be seen for reads from datafile headers. A sequential read
reads data into contiguous memory.
Get details on the event:
Querying the V$SESSION_WAIT view
will give us the following details for this event:
P1 => file#
P2 => block#
P3 => blocks
file# => This is the ABSOLUTE file number of the file that Oracle is
trying to read from.
block# => This is the starting block number in the file from where
Oracle starts reading the blocks. Typically only one block will be
read. If the block# is 1 then this is typically a datafile header
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 multi-block read.
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.
Based on the information about
file# and block# gathered from the V$SESSION_WAIT view we can further
narrow down the tablespace and the segments on which the waits are
happening as follows:
-- To get the tablespace and File
details
SQL> SELECT tablespace_name,
file_name FROM dba_data_files WHERE file_id = <file#>
If the above query did not return
any rows then the wait could be on a tempfile in which case the
following query can be used to identify the tablespace and file
details:
SQL> SELECT tablespace_name,
file_name FROM dba_temp_files f, v$parameter p
WHERE p.name='db_files' AND f.file_id+p.value = <file#>
-- To get the segment details
SQL> SELECT owner ,
segment_name , segment_type, partition_name
FROM dba_extents WHERE file_id = <file#>
AND <block#> BETWEEN block_id AND (block_id +
blocks -1)
Reducing the waits:
- Run a 10046 event trace on the
session and capture the SQL statements being issued. Look for
statements that are using unselective index scans and tune them if
possible to use more selective index scans. It could be as simple as
making sure the query uses all the columns in a concatenated index.
- The clustering factor might be
an issue. The CLUSTERING FACTOR (as found in the dba_indexes view) is
an indication of how ordered the table data is in relation to the
index.
- If an index is heavily used for
query restrictions then place the datafiles on which the index lives
on disks which are buffered by an O/S file system cache. Often this
will allow some of Oracle read requests to be satisfied from the OS
cache rather than from a real disk IO.
|
