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
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';