测试oracle的hanganalyze功能

测试场景:

sid 153, pid 19执行语句: update t set c = ‘a’ where n = 0;
sid 149, pid 22执行语句: update t set c = ‘a’ where n = 0;
在sid 153的session提交前,sid 149将处于等待行级TX锁状态,trace文件分析结果中的红色字体部分也表现出了这一点

产生trace文件:

SYS@orcl>oradebug setmypid
Statement processed.
SYS@orcl>oradebug unlimit;
Statement processed.
SYS@orcl>oradebug hanganalyze 3
Hang Analysis in /opt/ora10g/oracle/product/10.2.0/db_1/admin/orcl/udump/orcl_ora_8529.trc

trace文件内容如下:

oracle@adam-desktop:~$ more /opt/ora10g/oracle/product/10.2.0/db_1/admin/orcl/udump/orcl_ora_8529.trc
/opt/ora10g/oracle/product/10.2.0/db_1/admin/orcl/udump/orcl_ora_8529.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/ora10g/oracle/product/10.2.0/db_1
System name:	Linux
Node name:	adam-desktop
Release:	2.6.31-14-generic
Version:	#48-Ubuntu SMP Fri Oct 16 14:04:26 UTC 2009
Machine:	i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 23
Unix process pid: 8529, image: oracle@adam-desktop (TNS V1-V3)

*** SERVICE NAME:(SYS$USERS) 2011-09-10 22:02:02.976
*** SESSION ID:(145.11) 2011-09-10 22:02:02.976
*** 2011-09-10 22:02:02.976
==============
HANG ANALYSIS:
==============
Open chains found:
Chain 1 :  :
    <0/153/5/0x38e1c934/8480/SQL*Net message from client>
 -- <0/149/9/0x38e1da50/8429/enq: TX - row lock contention>
Other chains found:
Chain 2 :  :
    <0/142/100/0x38e1b264/9531/jobq slave wait>
Chain 3 :  :
    <0/145/11/0x38e1e004/8529/No Wait>
Chain 4 :  :
    <0/146/5/0x38e2023c/8399/Streams AQ: qmn slave idle wait>
Chain 5 :  :
    <0/152/7/0x38e1cee8/8366/Streams AQ: qmn coordinator idle>
Chain 6 :  :
    <0/158/7/0x38e1d49c/8386/Streams AQ: waiting for time man>
Extra information that will be dumped at higher levels:
[level  4] :   1 node dumps -- [REMOTE_WT] [LEAF] [LEAF_NW]
[level  5] :   5 node dumps -- [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP]
[level  6] :   1 node dumps -- [NLEAF]
[level 10] :  14 node dumps -- [IGN] 

State of nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):
[141]/0/142/100/0x38ef7690/9531/SINGLE_NODE/1/2//none
[144]/0/145/11/0x38efaeac/8529/SINGLE_NODE_NW/3/4//none
[145]/0/146/5/0x38efc160/8399/SINGLE_NODE/5/6//none
[148]/0/149/9/0x38eff97c/8429/NLEAF/7/10/[152]/none
[151]/0/152/7/0x38f03198/8366/SINGLE_NODE/11/12//none
[152]/0/153/5/0x38f0444c/8480/LEAF/8/9//148
[153]/0/154/1/0x38f05700/8340/IGN/13/14//none
[154]/0/155/1/0x38f069b4/8338/IGN/15/16//none
[155]/0/156/1/0x38f07c68/8335/IGN/17/18//none
[157]/0/158/7/0x38f0a1d0/8386/SINGLE_NODE/19/20//none
[159]/0/160/1/0x38f0c738/8307/IGN/21/22//none
[160]/0/161/1/0x38f0d9ec/8305/IGN/23/24//none
[161]/0/162/1/0x38f0eca0/8303/IGN/25/26//none
[162]/0/163/1/0x38f0ff54/8301/IGN/27/28//none
[163]/0/164/1/0x38f11208/8299/IGN/29/30//none
[164]/0/165/1/0x38f124bc/8297/IGN/31/32//none
[165]/0/166/1/0x38f13770/8295/IGN/33/34//none
[166]/0/167/1/0x38f14a24/8293/IGN/35/36//none
[167]/0/168/1/0x38f15cd8/8291/IGN/37/38//none
[168]/0/169/1/0x38f16f8c/8289/IGN/39/40//none
[169]/0/170/1/0x38f18240/8287/IGN/41/42//none
====================
END OF HANG ANALYSIS
====================

State of nodes输出列的含义:
Nodenum = This is secuencial 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)

state列的说明:

IN_HANG:This might be considered as the most critical STATE. Basically a node in this state is involved in a deadlock, or is hung. Usually there will be another “adjacent node” in the same status. For example:

LEAF and LEAF_NW:Leaf nodes are considered on top of the wait chain (usually blockers). They are considered “Blockers” when there is another session waiting. This can be easily identified using the “predecesor” field. If there is a node referenced in the ‘prdecessor’ field, the node is considered as “blocker”, otherwise it is considered as a “slow” session waiting for some resource.

The difference between LEAF and LEAF_NW is that LEAF nodes are not waiting for something, while LEAF_NW are not waiting or may be using the CPU.

参考资料:
oracle hanganalyze工具的使用

Comments are closed.