This event signifies that the user process is reading
buffers into the SGA buffer cache and is waiting for a physical I/O call to
return. A db file scattered read issues a scattered read to read the data into
multiple discontinuous memory locations. A scattered read is usually a
multiblock read. It can occur for a fast full scan (of an index) in addition to
a full table scan.
The db file scattered read wait event identifies that a
full scan is occurring. When performing a full scan into the buffer cache, the
blocks read are read into memory locations that are not physically adjacent to
each other. Such reads are called scattered read calls, because the blocks are
scattered throughout memory. This is why the corresponding wait event is called
'db file scattered read'. multiblock (up to DB_FILE_MULTIBLOCK_READ_COUNT
blocks) reads due to full scans into the buffer cache show up as waits for 'db
file scattered read'
In one line we can describe scattered read The
Oracle session has requested and is waiting for multiple contiguous
database blocks to be read into the SGA from disk.
Cuase :
· Full Table scans
· Fast Full Index Scans
· Missing or unselective or unusable index
· Table not analyzed after created index or lack of accurate
statistics for the optimizer
If an application that has been running fine for a while
suddenly clocks a lot of time on the db file scattered read event and there
hasn’t been a code change, you might want to check to see if one or more
indexes has been dropped or become unusable.
SELECT p1 "file#", p2 "block#", p3 "class#",event
FROM v$session_wait
WHERE event IN ('db file scattered read');
Where P1,P2,P3 are
P1 - The absolute file number
P2 - The block being read
P3 - The number of blocks (should be greater than 1)
SELECT relative_fno, owner, segment_name, segment_type
FROM DBA_EXTENTS
WHERE file_id = &FILE
AND &BLOCK BETWEEN block_id
AND block_id + blocks - 1;
SELECT
s.p1
file_id, s.p2 block_id,o.object_name
obj,
o.object_type
otype,
s.SQL_ID,
w.CLASS,event
FROM v$session s,
( SELECT ROWNUM CLASS#, CLASS FROM v$waitstat ) w,
ALL_OBJECTS
o
WHERE
event IN ('db file scattered read')
AND
w.CLASS#(+)=s.p3
AND o.object_id (+)= s.row_wait_OBJ#
ORDER BY 1;
Find the related SQL statement using sql_id
SELECT sql_text FROM v$sqltext WHERE sql_id=&sq_id ORDER BY piece
You can also find the objects using below sql :-
Finding the SQL Statement executed by Sessions Waiting for
I/O
SELECT SQL_ADDRESS, SQL_HASH_VALUE
FROM V$SESSION
WHERE EVENT ='read by other session';
Finding the Object Requiring I/O
SELECT row_wait_obj#
FROM V$SESSION
WHERE EVENT = 'db file scattered read';
To identify the object and object type contended for, query
DBA_OBJECTS using the value for ROW_WAIT_OBJ# that is returned from V$SESSION.
For example:
SELECT owner, object_name, subobject_name, object_type
FROM DBA_OBJECTS
WHERE data_object_id = &row_wait_obj;
Once you identify the hot blocks and the segments they
belong to, and related quires then you reduce the using following solutions
1.
Optimize the SQL statement
that initiated most of the waits. The goal is to minimize the number
of physical and logical reads.
2. Optimize multi-block I/O by setting the parameter
DB_FILE_MULTIBLOCK_READ_COUNT i.e if
DB_FILE_MULTIBLOCK_READ_COUNT initialization
parameter values too high which favors full scans reduce it.
3 Partition pruning to reduce
number of blocks visited Consider the usage of multiple buffer pools and cache
frequently used indexes/tables in the KEEP pool Make sure
that the query use the right driving table?
4 Are the SQL predicates appropriate for hash or merge
join?
5 If full scans are appropriate, can parallel query
improve the response time?
6 The objective is to reduce the
demands for both the logical and physical I/Os, and this is best achieved through SQL
and application tuning.
7 Gather statistics the related
objects if they are missing .
8 Check the LAST_ANALYZED date from
user_tables view
No comments:
Post a Comment