hanganalyz和ssd测试记录

第一个窗口
查询sid

SQL>  select distinct sid from v$mystat;

       SID
----------
        34

手动创造latch

SQL> select name,addr,gets from v$latch_children where name like '%row cache%' and gets >200 order by 3;

NAME                                                             ADDR                   GETS
---------------------------------------------------------------- ---------------- ----------
row cache objects                                                000000008D249E40        207
row cache objects                                                000000008D534140        669
row cache objects                                                000000008D8D4328       1510
row cache objects                                                000000008D752640       1623
row cache objects                                                000000008D9D6868       8072
row cache objects                                                000000008DAD6A28       9685
row cache objects                                                000000008DAD8570      14339
row cache objects                                                000000008DBD8730      41058
row cache objects                                                000000008D537E80      72324

SQL> oradebug setmypid
Statement processed.
将latch置1
SQL> oradebug call kslgetl 0x8D537E80 1
Function returned 1

另一个窗口执行

SQL>  select distinct sid from v$mystat;

       SID
----------
        37
执行查询hang住        
SQL> select * from test;
        

新开窗口做hanganalyze和ssd

sqlplus / as sysdba如果无法登陆用sqlplus -prelim '/ as sysdba'
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
oradebug dump systemstate 266
间隔几分钟再做一次，做对比
oradebug dump systemstate 266
oradebug tracefile_name

生成文件/u01/app/oracle/diag/rdbms/rac11/RAC111/trace/RAC111_ora_7594.trc

使用工具分析(分析ssd工具ass1046.awk)发现pid为41: waiting for 'latch: row cache objects' ,Final Blocker: inst: 1, sid: 34, ser: 21

awk -f ass1046.awk /u01/app/oracle/diag/rdbms/rac11/RAC111/trace/RAC111_ora_7594.trc

awk: ass1046.awk:463: warning: escape sequence `\+' treated as plain `+'

Starting Systemstate 1
...........................................
Starting Systemstate 2
...........................................
Ass.Awk Version 1.0.46
~~~~~~~~~~~~~~~~~~~~~~
Source file : /u01/app/oracle/diag/rdbms/rac11/RAC111/trace/RAC111_ora_7594.trc

System State 1  (2020-04-06 16:43:05.574)
~~~~~~~~~~~~~~   ~~~~~~~~~~~~~~~~~~~~~~~
1:                                     
2: waiting for 'pmon timer'            
3: waiting for 'rdbms ipc message'     
4: waiting for 'VKTM Logical Idle Wait' 
5: waiting for 'rdbms ipc message'     
6: waiting for 'DIAG idle wait'        
7: waiting for 'rdbms ipc message'     
8: waiting for 'PING'                  
9: waiting for 'rdbms ipc message'     
10: waiting for 'DIAG idle wait'        
11: waiting for 'rdbms ipc message'     
12: waiting for 'ges remote message'    
13: waiting for 'gcs remote message'    
14: waiting for 'rdbms ipc message'     
15: waiting for 'GCR sleep'             
16: waiting for 'rdbms ipc message'     
17: waiting for 'rdbms ipc message'     
18: waiting for 'rdbms ipc message'     
19: waiting for 'rdbms ipc message'     
20: waiting for 'smon timer'            
21:                                     
22: waiting for 'rdbms ipc message'     
23: waiting for 'ASM background timer'  
24: waiting for 'rdbms ipc message'     
25: waiting for 'rdbms ipc message'     
26:                                     
27:                                     
28: waiting for 'wait for unread message on broadcast channel' 
29: waiting for 'rdbms ipc message'     
30: waiting for 'rdbms ipc message'     
31: waiting for 'rdbms ipc message'     
32: waiting for 'Streams AQ: qmn slave idle wait' 
33: waiting for 'SQL*Net message from client' 
34: waiting for 'rdbms ipc message'     
35: waiting for 'Space Manager: slave idle wait' 
36: waiting for 'wait for unread message on broadcast channel' 
37: waiting for 'Streams AQ: qmn coordinator idle wait' 
38: waiting for 'SQL*Net message from client' 
39: waiting for 'SQL*Net message from client' 
40: waiting for 'rdbms ipc message'     
41: waiting for 'latch: row cache objects' 
    Final Blocker: inst: 1, sid: 34, ser: 21
42:                                     
43:                                     

NO BLOCKING PROCESSES FOUND

System State 2  (2020-04-06 16:43:18.879)
~~~~~~~~~~~~~~   ~~~~~~~~~~~~~~~~~~~~~~~
1:                                     
2: waiting for 'pmon timer'            
3: waiting for 'rdbms ipc message'     
4: waiting for 'VKTM Logical Idle Wait' 
5: waiting for 'rdbms ipc message'     
6: waiting for 'DIAG idle wait'        
7: waiting for 'rdbms ipc message'     
8: waiting for 'PING'                  
9: waiting for 'rdbms ipc message'     
10: waiting for 'DIAG idle wait'        
11: waiting for 'rdbms ipc message'     
12: waiting for 'ges remote message'    
13: waiting for 'gcs remote message'    
14: waiting for 'rdbms ipc message'     
15: waiting for 'GCR sleep'             
16: waiting for 'rdbms ipc message'     
17: waiting for 'rdbms ipc message'     
18: waiting for 'rdbms ipc message'     
19: waiting for 'rdbms ipc message'     
20: waiting for 'smon timer'            
21: waiting for 'rdbms ipc message'     
22: waiting for 'rdbms ipc message'     
23: waiting for 'ASM background timer'  
24: waiting for 'rdbms ipc message'     
25: waiting for 'rdbms ipc message'     
26:                                     
27:                                     
28: waiting for 'wait for unread message on broadcast channel' 
29: waiting for 'rdbms ipc message'     
30: waiting for 'rdbms ipc message'     
31: waiting for 'rdbms ipc message'     
32: waiting for 'Streams AQ: qmn slave idle wait' 
33: waiting for 'SQL*Net message from client' 
34: waiting for 'rdbms ipc message'     
35: waiting for 'Space Manager: slave idle wait' 
36: waiting for 'wait for unread message on broadcast channel' 
37: waiting for 'Streams AQ: qmn coordinator idle wait' 
38: waiting for 'SQL*Net message from client' 
39: waiting for 'SQL*Net message from client' 
40: waiting for 'rdbms ipc message'     
41: waiting for 'latch: row cache objects' 
    Final Blocker: inst: 1, sid: 34, ser: 21
42: waiting for 'Streams AQ: waiting for time management or cleanup tasks' 
43:                                     

NO BLOCKING PROCESSES FOUND

                   ------------------ooOoo------------------
For the LATEST version of this utility see
  http://gbr30026.uk.oracle.com:81/Public/Utils.html#ass

For additional documentation see
  http://gbr30026.uk.oracle.com:81/Public/TOOLS/Ass.html

Suggested improvements, bugs etc. should be sent to kevin.p.quinn@oracle.com

End of report. 96903 Lines Processed.

查看hanganalyze的trace中State of ALL nodes发现也是1节点sid为34的会话阻塞37

State of ALL nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
[33]/1/34/21/0x91beae28/6049/LEAF/
[36]/1/37/37/0x91be1b88/6192/NLEAF/[33]

大概解释
session           = session的saddr
Nodenum       = This is sequential number used by HANGANALYZE to identify each session
sid                  = Session ID
sess_srno      = Serial#
ospid              = OS Process Id
state               = State of the node
adjlist              = adjacent node   (Usually represents a  blocker node)
predecessor   = predecessor node (Usually represents a waiter node)
cnode             = Node number (Only available since Oracle9i)