Db file sequential read: Which object is it waiting on?

If you encounter a ‘db file sequential read’ wait, the P1 and P2 parameters from v$session_wait can be used to identify which object the process is waiting on. This may be helpful during troubleshooting. Here’s a sample query that looks at all ‘db file sequential read’ waits:

column owner format a10
column object_name format a15
column object_type format a11

select w.sid,
       w.p1, w.p2, w.p3,
       o.owner,
       substr(o.object_name,1,30) object_name,
       o.object_type
  from dba_objects o, v$session_wait w, x$bh bh
 where bh.obj  = o.object_id(+)
   and w.p1    = bh.file#(+)
   and w.p2    = bh.dbablk(+)
   and w.event = 'db file sequential read'
union
select w.sid,
       w.p1, w.p2, w.p3,
       o.owner,
       substr(o.object_name,1,30) object_name,
       o.object_type
  from dba_objects o, v$session_wait w, x$bh bh
 where bh.obj  = o.data_object_id(+)
   and w.p1    = bh.file#(+)
   and w.p2    = bh.dbablk(+)
   and w.event = 'db file sequential read'
 order by 1
/


       SID         P1         P2         P3 OWNER      OBJECT_NAME     OBJECT_TYPE
---------- ---------- ---------- ---------- ---------- --------------- -----------
       113        308    2345988          1 HR         EMP             TABLE

       978        242    3909403          1 HR         DEPT            TABLE

      1159        187   52585099          1 HR         EMP_IDX1        INDEX

      1159        187   56776227          1 HR         DEPT_IDX1       INDEX