|
Category :
Database
Resources -> Database Administration -> Wait Events
DB Version
:
Oracle 8i /
Oracle 9i
OS Details :
Sun Solaris9
Reference
: Oracle Metalink
Wait
Event
:
db file Scattered read
This
signifies a wait for a multi-block I/O read request to complete and
mostly
occurs when oracle's foreground process is doing a multi-block read
from the database files. Full table scans or index fast full scans are
the main causes for this wait event. Oracle reads up to
DB_FILE_MULTIBLOCK_READ_COUNT consecutive blocks at a time and
scatters them into buffers in the buffer cache.
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.
blocks => This parameter
specifies the number of blocks that Oracle is trying to read from the
file# starting at block#. This value can potentially match the
parameter value of DB_FILE_MULTIBLOCK_READ_COUNT for the session or
the instance.
Wait Time => The wait blocks
until all blocks in the IO request have been read..
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 doing FULL table scans or index fast full scans
and tune them if possible to use more selective index scans.
- If FULL scans cannot be avoided
then increase the value for DB_FILE_MULTIBLOCK_READ_COUNT parameter at
the session level to have oracle read more blocks per read.
- If frequent FULL scans are
being done on a certain table or an index then place the datafiles on which the
table or 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.
- Consider table partitioning so
that only a subset of data will be queried.
|
