随着Oracle的技术广泛应用于各行各业，学习Oracle技术的人越来越多。这篇文章主要是南京万和Oracle认证培训通过例子简要介绍了如何排查Oracle会话阻塞的情况，学习oracle的朋友可以参考下。

会话阻塞排查测试

另开一个会话

另开会话排错      南京万和Oracle认证培训

[Oracle@T9DB02 ~]$ awk -f ass109.awk /u01/app/oracle/diag/rdbms/bap/BAP_1/trace/BAP_1_ora_15119.trc

Starting Systemstate 1
................................................
Ass.Awk Version 1.0.9 - Processing /u01/app/oracle/diag/rdbms/bap/BAP_1/trace/BAP_1_ora_15119.trc

System State 1
~~~~~~~~~~~~~~~~
1:                                      
2:  0: waiting for 'pmon timer'         
3:  0: waiting for 'rdbms ipc message'  
4:  0: waiting for 'VKTM Logical Idle Wait' 
5:  0: waiting for 'rdbms ipc message'  
6:  0: waiting for 'DIAG idle wait'     
7:  0: waiting for 'rdbms ipc message'  
8:  0: waiting for 'PING'               
9:  0: waiting for 'rdbms ipc message'  
10: 0: waiting for 'DIAG idle wait'     
11: 0: waiting for 'rdbms ipc message'  
12: 0: waiting for 'ges remote message'[Latch sent-location:] 
13: 0: waiting for 'gcs remote message' 
14: 0: waiting for 'gcs remote message' 
15: 0: waiting for 'gcs remote message' 
16: 0: waiting for 'rdbms ipc message'  
17: 0: waiting for 'GCR sleep'          
18: 0: waiting for 'rdbms ipc message'  
19: 0: waiting for 'rdbms ipc message'  
20: 0: waiting for 'rdbms ipc message'  
21: 0: waiting for 'rdbms ipc message'  
22: 0: waiting for 'rdbms ipc message'  
23: 0: waiting for 'rdbms ipc message'  
24: 0: waiting for 'rdbms ipc message'  
25: 0: waiting for 'smon timer'         
26: 0: waiting for 'rdbms ipc message'  
27: 0: waiting for 'rdbms ipc message'  
28: 0: waiting for 'ASM background timer' 
29: 0: waiting for 'rdbms ipc message'  
30: 0: waiting for 'rdbms ipc message'  
31: 0: waiting for 'wait for unread message on broadcast channel' 
32: 0: waiting for 'Space Manager: slave idle wait' 
33: 0: waiting for 'rdbms ipc message'  
34: 0: waiting for 'rdbms ipc message'  
35: 0: waiting for 'SQL*Net message from client' 
36: 0: waiting for 'Streams AQ: qmn slave idle wait' 
37: 0: waiting for 'rdbms ipc message'  
38: 0: waiting for 'class slave wait'   
39: 0: waiting for 'wait for unread message on broadcast channel' 
40: 0: waiting for 'Streams AQ: qmn coordinator idle wait' 
41: 0: waiting for 'SQL*Net message from client' 
42: 0: waiting for 'SQL*Net message from client' 
43: 0: waiting for 'enq: TX - row lock contention'[Enqueue TX-0009000B-00001D87] 
44: 0: waiting for 'SQL*Net message from client' 
45:                                     
46: 6: waited for 'Streams AQ: waiting for time management or cleanup tasks' 
49: 0: waiting for 'SQL*Net message from client' 
50: 0: waiting for 'rdbms ipc message'  
Blockers
~~~~~~~~

Above is a list of all the processes. If they are waiting for a resource
then it will be given in square brackets. Below is a summary of the
waited upon resources, together with the holder of that resource.
Notes:
~~~~~
o A process id of '???' implies that the holder was not found in the
systemstate.

Resource Holder State
Latch sent-location:    ??? Blocker
Enqueue TX-0009000B-00001D87    35: 0: waiting for 'SQL*Net message from client'

Object Names
~~~~~~~~~~~~
Latch sent-location:                  last post sent-location: kji.h LINE:

Enqueue TX-0009000B-00001D87                                  

157156 Lines Processed.

通过以上发现 ：PID=35的进程 阻塞了其他会话           南京万和Oracle认证培训
查询到sid=216  与我们开始做的环境一致 

另外的方法更简单

1）开一个session， select  * from v$mystat where rownum=1; 获取该会话的sid。

2）在这个session中试跑慢的sql。

3）开另外一个session。 select * from v$lock where sid=前面那个会话的sid。

等待锁资源的会话，在v$lock 中会显示为request字段等于6(6的意思是排他锁）。

那么就需要找到对应拥有着。记录下request=6的记录的id1、id2数值，在v$lock中以id1、id2等于记录的数值为查询条件查出其余记录，并在其余记录中看是否有lmode＝6的（对应锁拥有者）。然后就可以顺藤摸瓜找出对应的锁拥有者的sid。 

接下来还有某大神提供的方法：

我一直都用TOM的方法：

with vw_lock AS (SELECT * FROM v$lock)

select

a.sid,

'is blocking',

(select 'sid:'||s.sid||' object:'||do.object_name||' rowid:'||

    dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )

    ||' sql_id:'||s.sql_id

   from v$session s, dba_objects do

    where s.sid=b.sid

    and s.ROW_WAIT_OBJ# = do.OBJECT_ID

) blockee,

b.sid,b.id1,b.id2

from vw_lock a, vw_lock b

where a.block = 1

and b.request > 0

and a.id1 = b.id1

and a.id2 = b.id2;

     以上文章主要是南京Oracle认证培训对Oracle会话阻塞的排查介绍 ，希望大家能有所收获。想和兴趣相投的朋友们一起交流吗？那就来江苏万和计算机培训中心吧。而江苏万和作为南京专业的Oracle认证培训中心，也是Oracle等众多厂商的授权培训、考试机构，欢迎广大有志于此行业发展的学员们加入。