会话阻塞排查测试

另开一个会话

另开会话排错

[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的进程 阻塞了其他会话

查询到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。