Oracle Wait Events – db file scattered read

The db file scattered read wait event is similar to the db file sequential read, except that the session is waiting for a multi-block I/O read request to complete. The db file scattered read waits are associated with full table scans and index fast full scans (FFS) operations. Oracle reads a set of contiguous blocks up to DB_FILE_MULTIBLOCK_READ_COUNT and scatters them into the Buffer Cache.

A DBA who monitors a full table scan operation closely by repeatedly querying the V$SESSION_WAIT view, may see some db file scattered read events that request fewer blocks than DB_FILE_MULTIBLOCK_READ_COUNT. Sometimes, there may even be a few db file sequential read events that show up within a single full table scan operation. These are mainly due to extent boundaries (i.e. when the last set of blocks in an extent is less than DB_FILE_MULTIBLOCK_READ_COUNT), or blocks that are already cached in the Buffer Cache.

Common Causes and Actions

It is common for full table scans or index fast full scans operations to wait on physical I/Os to complete. The DBA should not be alarmed just because this event shows up in the database. Rather, the DBA should be concerned with the average I/O time and sessions that spend a lot of time on this event.

The average multi-block I/O wait should not exceed 1 centisecond (1/100 second). If the db file scattered read is costly, this could indicate the storage subsystem is slow or the database files are poorly placed. The DBA should ensure that the system is properly configured. Follow the suggestions in the db file sequential read event section.

If the average I/O wait time for the db file scattered read event is acceptable, but the event represents a significant portion of waits in a certain session, then this is an application issue. In this case, the DBA needs to determine which object is being read the most from the P1 and P2 values, extract the relevant SQL statement, generate and examine the explain plan against the SQL predicates, and advise on tuning. Should the statement use full table scan/index FFS to access data? Would an index access be more efficient? Does the query use the right driving table? The objective is to reduce both the logical and physical I/O calls, and this can be best achieved through SQL and application tuning.

SQL plans that join tables using HASH JOIN or SORT MERGE operation, scan the tables, and I/O waits show up as db file scattered read events. In this case, the DBA also needs to evaluate the SQL predicates and determine if the HASH JOIN or SORT MERGE is appropriate.

If an application that has been running fine for awhile suddenly clocks a lot of time on the db file scattered read event and there is no code change, then this is certainly 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.

A DBA may have increased the value of the DB_FILE_MULTIBLOCK_READ_COUNT INIT.ORA parameter. The optimizer is more likely to choose a full table scan over an index access if the value of this parameter is high. Likewise, the HASH_AREA_SIZE and OPTIMIZER_INDEX_COST_ADJ parameters, when increased can skew the optimizer toward the full table scans. Make appropriate adjustments to these parameters so they do not adversely affect application runtimes.

Out-of-date statistics are yet another factor that can adversely affect the quality of execution plans causing excessive I/Os. Keep all object statistics up to date. Note: The ANALYZE TABLE command with the COMPUTE option normally performs full table scans and will add to the db file scattered read statistics for the session (V$SESSION_EVENT) and instance (V$SYSTEM_EVENT).

Tables with a non-default degree of parallelism also tend to move the optimizer in the way of full table scans. This, however, shows up as the direct path read event. Make sure the degrees of parallelism for tables are properly set.

Diagnosis

For system-level diagnosis, query the V$SYSTEM_EVENT view to determine if the AVERAGE_WAIT is an issue.

select * from v$system_event where event = 'db file scattered read';

For session-level diagnosis, query the V$SESSION_EVENT view and identify the live session that registers a significant amount of time on this event. Alternatively, the V$SESSTAT can also direct the DBA to the live session that performed the most full table scans. Once the session is identified, the DBA can take the necessary steps to find the root cause of this symptom.

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;

The cure for the db file scattered read waits is the same as the one for the db file sequential read: find and tune the SQL statement that clocked the most time on the event. The goal of tuning is also to minimize the number of logical and physical I/Os. Follow the examples in the db file sequential read event section.