Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 1 April 2013

DB File Sequential Read Wait/ DB File Scattered Read

An Oracle session logs the db file sequential read wait event when it has to wait for a single-block I/O read request to complete. Oracle issues single-block I/O read requests when reading from indexes, rollback segments, sort segments, control files, datafile headers and tables (when tables are accessed via rowids). A sequential read is usually a single-block read, although it is possible to see sequential reads for more than one block (See P3).  To determine the actual object being waited can be checked by the p1, p2, p3 info in v$session_wait.
Select * from   v$session_event
where  event = 'db file sequential read'
order by time_waited;

Select segment_name, partition_name, segment_type, tablespace_name
from   dba_extents a, v$session_wait b
where  b.p2 between a.block_id and (a.block_id + a.blocks - 1)
and    a.file_id  = b.p1
and    b.event    = 'db file sequential read';

Select a.sid, a.serial#, a.username, a.osuser, b.sql_text
from   v$session a, v$sqltext b
where  a.sql_hash_value = b.hash_value
and    a.sql_address    = b.address and    a.sid in (select sid from   v$session_wait
where  event = 'db file sequential read')
order by a.sid, b.hash_value, b.piece;
Note: Where P1 = file#, P2 = block#, P3 = blocks 9 (should be 1)
Generally the entire database having some wait event doing IO for index scan usually. But if you see seconds in waiting greater then 0, you must tune index I/O.
To reduce this wait event follow the below points:
1.      Tuning SQL statements to reduce unnecessary I/O request is the only guaranteed way to reduce "db file sequential read" wait time.
2.      Distribute the index in different file system to reduce the contention for I/O
Tuning Physical devices, the data on different disk to reduce the I/O.
3.      Use of Faster disk reduces the unnecessary I/O request.
Increase db_block_buffers or larger buffer cache sometimes can help.
DB File Scattered Read:
This generally indicates waits related to full table scans. As full table scans are pulled into memory, they rarely fall into contiguous buffers but instead are scattered throughout the buffer cache. A large number here indicates that your table may have missing or suppressed indexes. Although it may be more efficient in your situation to perform a full table scan than an index scan, check to ensure that full table scans are necessary when you see these waits. Try to cache small tables to avoid reading them in over and over again, since a full table scan is put at the cold end of the LRU (Least Recently Used) list. 
The DBA should be concerned with average I/O time and session that spend time on this event. The average multi block I/O should not exceeds 1/100 sec.
SELECT a.average_wait "SEQ READ", b.average_wait "SCAT READ"
FROM sys.v_$system_event a, sys.v_$system_event b
WHERE a.event = 'db file sequential read' AND b.event = 'db file scattered read';
select * from v$system_event where event = 'db file sequential read';

Select * from   v$session_event
where  event = 'db file scattered read'
order by time_waited;

Select a.sid, b.name, a.value
from   v$sesstat a, v$statname b
where  a.statistic# = b.statistic#
and    a.value     <> 0 and    b.name = 'table scan blocks gotten'
order by 3,1;
If the average I/O wait time for the db file scattered read event is acceptable, but the event indicates waits in a certain session, then this is an application issue.
In this case DBA needs to determine which objects is being read the most from P1 and P2 values, check the relevant SQL statement, explain plan for that SQL, Perform SQL tuning. The motive is to reduce both the logical and physical I/O calls: link to check SQL or Application Tuning.
If an application that has been running fine for suddenly starts indicating the db file scattered read event then this could be an index issue. One or more indexes may have been dropped or become unusable.
To determine which indexes have been dropped, the DBA can compare the development, test, and production databases. The ALTER TABLE MOVE command marks all indexes associated with the table as unusable. Certain partitioning operations can also cause indexes to be marked unusable. This includes adding a new partition or coalescing partitions in a hash-partitioned table, dropping a partition from a partitioned table or global partitioned index, modifying partition attributes, and merging, moving, splitting or truncating table partitions. A direct load operation that fails also leaves indexes in an unusable state. This can easily be fixed by rebuilding the indexes.

5 comments:

  1. If the 2nd query for "DB File Sequential Read Wait" give me only table extents, what can we do?

    ReplyDelete
  2. Dear,

    Can you send me the output?

    ReplyDelete
    Replies
    1. hi Can u give exact solution for db sequential read pls

      Delete
  3. Check Indexing or try to increase buffer cache.

    ReplyDelete
  4. I got this and is there any issue with this?

    Select * from v$session_event where event = 'db file sequential read' order by time_waited;

    SID EVENT
    ---------- ----------------------------------------------------------------
    TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT TIME_WAITED_MICRO
    ----------- -------------- ----------- ------------ ---------- -----------------
    EVENT_ID WAIT_CLASS_ID WAIT_CLASS#
    ---------- ------------- -----------
    WAIT_CLASS
    ----------------------------------------------------------------
    1152 db file sequential read
    46 0 7 .16 1 74671
    2652584166 1740759767 8
    User I/O

    ReplyDelete