Top 5 Timed Events - db file sequential read

Top 5 Timed Events - db file sequential read

The db file sequential read wait event occurs 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 always confusing for everybody, as the sequential read is associated with index read and the scattered read has to do with full table scans. It seems like they would have 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.

 

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.