db file sequential read 系列事件的定位

1、查看数据库的等待事件：

         INST_ID EVENT                                                     Prev             Curr              Tot  SECONDS_IN_WAIT
---------------- --------------------------------------------- ---------------- ---------------- ---------------- ----------------
               1 db file sequential read                                      0                9                9                0
               1 jobq slave wait                                              0                3                3                0
               1 LNS ASYNC end of log                                         0                1                1                0
               1 SQL*Net message to client                                    1                0                1                0

2、查出等待事件发生所在的文件号和块号

11:44:46 sys@ORADB1>SELECT p1 "file#", p2 "block#", p3 "class#"      FROM v$session_wait WHERE event = 'db file sequential read';

           file#           block#           class#
---------------- ---------------- ----------------
              72           373068                1

3、找出等待事件发生所在的对象

11:47:21 sys@ORADB1>SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME  
11:47:41   2   FROM DBA_EXTENTS A  
11:47:41   3   WHERE FILE_ID = &FILE_ID  
11:47:41   4   AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS + 1;  
Enter value for file_id: 72
old   3:  WHERE FILE_ID = &FILE_ID
new   3:  WHERE FILE_ID = 72
Enter value for block_id: 373068
old   4:  AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS + 1
new   4:  AND 373068 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS + 1

OWNER                          SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ ------------------------------
PARTITION_NAME
------------------------------
HTGPRS                         IDX_NTS_BAGGING_CREATETIME     INDEX              GPRS_IDX

4、找出造成等待事件的sql语句

select * from v$sql where upper(sql_text) like '%IDX_NTS_BAGGING_CREATETIME%';  

5、找出sql对应的会话

select SID, SERIAL# from v$session where sql_id='&sql_id';

6、必要时可以kill会话