Sequential Reads Against Tables

You may see db file sequential read wait events in which the P1 and P2 parameters resolve to a table instead of an index. This is normal for SQL statements that access tables by rowids obtained from the indexes, as shown in the following explain plan. Oracle uses single-block I/O when reading a table by rowids.

LVL OPERATION                         OBJECT                
--- --------------------------------- ---------------------
  1 SELECT STATEMENT 
  2   TABLE ACCESS BY INDEX ROWID     RESOURCE_ASGN_SNP   
  3     INDEX RANGE SCAN              RESOURCE_ASGN_SNP_4IX

System-Level Diagnosis

The V$SYSTEM_EVENT view provides the data for system-level diagnosis. For I/O related events, the two columns of interest are the AVERAGE_WAIT and TIME_WAITED.

Remember to eva luate the TIME_WAITED with the instance startup in mind. It is normal for an older instance to show a higher db file sequential read wait time. Also, always query the V$SYSTEM_EVENT view in the order of TIME_WAITED such as in the following example. This allows you to compare the db file sequential read waits with other significant events in the system. If the db file sequential read wait time is not in the top five category, don’t worry about it because you have bigger fish to fry. Even if the db file sequential read wait time is in the top five category, all it tells you is that the database has seen a lot of single-block I/O calls. The high wait time may be comprised of waits from many short-running OLTP sessions or a few long-running batch processes, or both. At the system level, there is no information as to who made the I/O calls, when the calls were made, what objects were accessed, and the SQL statements that initiated the calls. In other words, system-level statistics offer very limited diagnosis capability.

select a.event, 
       a.total_waits, 
       a.time_waited, 
       a.time_waited/a.total_waits average_wait,
       sysdate   b.startup_time days_old
from   v$system_event a, v$instance b
order by a.time_waited;

The AVERAGE_WAIT column is more useful. We showed what you should consider as normal in the preceding paragraphs. If your average single-block read wait time exceeds this allowance, you may have a problem in the I/O subsystem or hot spots on disk. If your database is built on file systems, make sure the database mount points contain only Oracle files. Do not share your database mount points with the application or another database. Also, if possible, avoid sharing I/O devices. Several mount points can be mapped to the same I/O device. According to the following Veritas vxprint output, mount points u02, u03, u04, and u05 are all mapped to device c2t2d0. You should find out how your database files are mapped to I/O controllers and I/O devices or physical disks. For databases on the Veritas file system, the vxprint ht command shows the mount point mappings.