As you monitor a session and come across the db file sequential read event, you should translate its P1 and P2 parameters into the object that they represent. You will find that the object is normally an index or a table. The DBA_EXTENTS view is commonly used for object name resolution. However, as mentioned in Chapter 4, the DBA_EXTENTS is a complex view and is not query-friendly in regards to performance. Object name resolution is much faster using the X$BH and DBA_OBJECTS views. The caveat is that you must wait for the block to be read into the buffer cache; otherwise the X$BH view has no information on the buffer that is referenced by the P1 and P2 parameters. Also, the DBA_OBJECTS view does not contain rollback or undo segment objects that the P1 and P2 parameters may be referencing.

要减少这个等待事件，要么减少它的次数，要么减少平均等待时间。通过调优SQL来减少逻辑读，留意效率低的大范围索引扫描回表（可能全表扫更好），可以减低次数。用更高响应时间的存储，分散热点文件，可以减轻平均等待时间。在新的存储子系统，平均单块读等待时间不应超过10ms（千分之一秒），如果用有大cache的SAN一般4-8ms为佳。通过p1与p2参数与dba_extents视图，我们定位到等待访问的段，然后来分散热点。

--这段SQL的找出频繁发生db file sequential read的对象。

select b.sid,
       nvl(substr(a.object_name,1,30),
                  'P1='||b.p1||' P2='||b.p2||' P3='||b.p3) object_name,
       a.subobject_name,
       a.object_type
from   dba_objects a, v$session_wait b, x$bh c
where  c.obj = a.object_id(+)
and    b.p1 = c.file#(+)
and    b.p2 = c.dbablk(+)
and    b.event = 'db file sequential read'
union
select b.sid,
       nvl(substr(a.object_name,1,30),
                  'P1='||b.p1||' P2='||b.p2||' P3='||b.p3) object_name,
       a.subobject_name,
       a.object_type
from   dba_objects a, v$session_wait b, x$bh c
where  c.obj = a.data_object_id(+)
and    b.p1 = c.file#(+)
and    b.p2 = c.dbablk(+)
and    b.event = 'db file sequential read'
order  by 1;

  SID OBJECT_NAME               SUBOBJECT_NAME            OBJECT_TYPE
----- ------------------------- ------------------------- -----------------
   12 DVC_TRX_REPOS             DVC_TRX_REPOS_PR64        TABLE PARTITION
  128 DVC_TRX_REPOS             DVC_TRX_REPOS_PR61        TABLE PARTITION
  154 ERROR_QUEUE               ERROR_QUEUE_PR1           TABLE PARTITION
  192 DVC_TRX_REPOS_1IX         DVC_TRX_REPOS_20040416    INDEX PARTITION
  194 P1=22 P2=30801 P3=1
  322 P1=274 P2=142805 P3=1
  336 HOLD_Q1_LIST_PK                                     INDEX

Sequential Reads Against Indexes

The main issue is not index access; it is waits that are caused by excessive and unwarranted index reads. If the db file sequential read event represents a significant portion of a session’s response time, all that tells you is that the application is doing a lot of index reads. This is an application issue. Inspect the execution plans of the SQL statements that access data through indexes. Is it appropriate for the SQL statements to access data through index lookups Is the application an online transaction processing (OLTP) or decision support system (DSS) Would full table scans be more efficient Do the statements use the right driving table And so on. The optimization goal is to minimize both the number of logical and physical I/Os.

If you have access to the application code, you should examine the application logic. Look at the overall logic and understand what it is trying to do. You may be able to recommend a better approach.