第一个窗口
查询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.trcawk: 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.trcSystem 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#assFor additional documentation see
http://gbr30026.uk.oracle.com:81/Public/TOOLS/Ass.htmlSuggested 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)