Oracle Wait Events – db file sequential 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, controlfiles, datafile headers and tables (when tables are accessed via rowids).

The db file sequential read and db file scattered read events can be confusing at first, as the sequential read is associated with index read and the scattered read has to do with full table scans. It seems like they ought to be the other way around. The db file sequential read is thus named because it reads blocks into contiguous memory, and the db file scattered read gets its name because it reads multiple blocks and scatters them into the Buffer Cache.

Wait Time:

The wait time is the actual time it takes to do the I/O.

Common Causes and Actions

The following discusses the common causes of db file sequential read waits that result from index reads.

Physical I/O requests for index blocks are perfectly normal and so the presence of the db file sequential read waits do not necessarily mean that there is something wrong with the database or the application. The issue is not index reads, rather it is the waits that are caused by excessive and unwarranted index reads. The performance degradation is magnified by slow I/O subsystem and/or poor datafiles layout. The DBA should be concerned with the average I/O time and SQL statements that spend a lot of time on this wait event.

Newer storage subsystems should offer an average single-block I/O wait that does not exceed 1 centisecond (1/100 second). Query the V$SYSTEM_EVENT view for the average wait of your database. If the average I/O wait far exceeds this allowance, this could mean the storage subsystem is slow or the database files are poorly placed. This is a database issue and the DBA should verify that the system is properly configured by examining the mapping of database files to mount points, and mount points to physical disks and I/O controllers. The idea is to avoid hot spots. Datafiles should be created on LUNs (Disk Logical Unit Number) that are striped over enough disks, and mount points should be distributed across all available I/O controllers and busses.

If the average I/O wait time for the db file sequential 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 index is being read the most from the P1 and P2 values, identify the SQL statement, generate and examine the explain plan against the SQL predicates, and advise on tuning. Should the query access data through an index? Would a full table scan 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.

The introduction of a new index in the database where the Stored Outline feature is not used may cause the optimizer to select a plan that uses the index, and depending on the SQL statement, could result in significant I/O wait time.

Out-of-date statistics can certainly induce the optimizer to generate poor execution plans, which may call for index reads when they shouldn't. Make sure all object statistics are kept up to date. Note: The ANALYZE INDEX or ANAYZE TABLE with the ESTIMATE option normally performs single block reads and will add to the db file sequential read statistics for the session (V$SESSION_EVENT) and instance (V$SYSTEM_EVENT).

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 sequential 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 using the query below. 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 sequential read'
order by time_waited;

The best cure for the db file sequential read waits is to find and tune the SQL statement that clocked the most time on this event. The goal of tuning is to minimize the number of logical and physical I/Os. To capture the SQL statement, the DBA may trace the identified session using Oracle event 10046, a third party monitoring tool, a homegrown monitoring process, or interactive monitoring. Regardless of the method, often the job must be rerun to properly identify the root cause of the bottleneck.

Below are some examples for interactive monitoring. The V$SESSION_WAIT view is the primary source for root cause analysis. Using the values from P1 and P2, the DBA can determine the object being read. If it is an index, and the plan calls for table access by index rowid, then it may be worthwhile to check the clustering factor. The clustering factor of an index defines how ordered the rows are in the index, and affects the number of I/Os required for the whole operation. If the DBA_INDEXES.CLUSTERING_FACTOR for the index approaches the number of blocks in the table (this is desirable), the rows are ordered. If it approaches the number of rows in the table, the rows are randomly ordered. In such a case, it is unlikely that index entries in the same leaf block will point to rows in the same data blocks.

Interactive monitoring has serious limitations. It is a slow manual process and many important events can potentially be missed between sampling. It is also not practical for the DBA to "baby sit" a long-running job.

Use this query to find the name of the object:

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';

The SQL statement associated with this event can be obtained using this query:

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;

The session waits while a sequential read from the database is performed. This event is also used for rebuilding the control file, dumping datafile headers, and getting the database file headers.