DB File Sequential Read Wait/ DB File Scattered Read -pt

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.