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.***

Tuesday, 17 July 2018

DB File Sequential Read Wait/ DB File Scattered Read

A sequential read reads operation reads data into contiguous memory. Single block I/Os are usually the result of using indexes. This event is also used for rebuilding the controlfile and reading datafile headers (P2=1). In general, this event is indicative of disk contention on index reads. 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 is indicating disk contention on full table scans. Rarely, data from full table scans could be fitted into a contiguous buffer area, these waits would then show up as sequential reads instead of scattered reads. However, scattered read will never read just one block (p3 is always >= 2). 
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';
SEQ READ  SCAT READ
---------- ----------
       .74        1.6

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