为什么要使用hanganalyze
Oracle 数据库“真的”hang住了，可以理解为数据库内部发生死锁。因为普通的DML死锁，oracle服务器会自动监测他们的依赖关系，并回滚其中一个操作， 终止这种相互等待的局面。而当这种死锁发生在争夺内核级别的资源（比如说是pins或latches）时，Oracle并不能自动的监测并处理这种死锁。
其实很多时候数据库并没有hang住，而只是由于数据库的性能问题，处理的时间比较长而已。
Hanganalyze工具使用内核调用检测会话在等待什么资源，报告出占有者和等待者的相互关系。另外，它还会将一些比较”interesting”的进程状态dump出来，这个取决于我们使用hanganalyze的分析级别。
hanganalyze工具从oracle8i第二版开始提供，到9i增强了诊断RAC环境下的“集群范围”的信息，这意味着它将会报告出整个集群下的所有会话的信息。
目前有三种使用hanganalyze的方法:
一种是会话级别的：
SQL>ALTER SESSION SET EVENTS 'immediate trace name HANGANALYZE level ';
一种是实例级别:
SQL>ORADEBUG hanganalyze
一种是集群范围的：
SQL>ORADEBUG setmypid
SQL>ORADEBUG setinst all
SQL>ORADEBUG -g def hanganalyze
各个level的含义如下：
1-2:只有hanganalyze输出，不dump任何进程
3：Level2+Dump出在IN_HANG状态的进程
4：Level3+Dump出在等待链里面的blockers(状态为LEAF/LEAF_NW/IGN_DMP)
5：Level4+Dump出所有在等待链中的进程（状态为NLEAF）
Oracle官方建议不要超过level 3，一般level 3也能够解决问题，超过level 3会给系统带来额外负担。

hanganalyze实验

1.session1更新行数据
SQL> connect scott/scott
Connected.
SQL> create table tb_hang(id number,remark varchar2(20));
Table created.
SQL> insert into tb_hang values(1,'test');
1 row created.
SQL> commit;
Commit complete.
SQL> select USERENV('sid') from dual;
USERENV('SID')
--------------
           146
SQL> update tb_hang set remark='hang' where id=1;
1 row updated.
这个时候不提交
2.session2同样更新session1更新的行
SQL> select USERENV('sid') from dual;            
USERENV('SID')
--------------
           154
SQL>  update tb_hang set remark='hang' where id=1;
这个时候已经hang住了
3.session3使用hangalyze生成trace文件
SQL> connect / as sysdba
Connected.
SQL> oradebug hanganalyze 3;
Hang Analysis in /u01/app/oracle/admin/oracl/udump/oracl_ora_3941.trc
4.查看trace文件的内容
$ more /u01/app/oracle/admin/oracl/udump/oracl_ora_3941.trc
/u01/app/oracle/admin/oracl/udump/oracl_ora_3941.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      hxl
Release:        2.6.18-8.el5xen
Version:        #1 SMP Fri Jan 26 14:42:21 EST 2007
Machine:        i686
Instance name: oracl
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 3941, image: oracle@hxl (TNS V1-V3)
*** SERVICE NAME:(SYS$USERS) 2012-06-16 01:13:29.241
*** SESSION ID:(144.14) 2012-06-16 01:13:29.241
*** 2012-06-16 01:13:29.241
==============
HANG ANALYSIS:
==============
Open chains found:
Chain 1 : :
    <0/146/5/0x7861b254/3858/SQL*Net message from client>
 -- <0/154/5/0x7861c370/3903/enq: TX - row lock contention>
Other chains found:
Chain 2 : :
    <0/144/14/0x7861d48c/3941/No Wait>
Chain 3 : :
    <0/149/1/0x7861ced8/3806/Streams AQ: waiting for time man>
Chain 4 : :
    <0/151/1/0x7861c924/3804/Streams AQ: qmn coordinator idle>
Chain 5 : :
    <0/158/5/0x7861da40/3810/Streams AQ: qmn slave idle wait>
Extra information that will be dumped at higher levels:
[level  4] :   1 node dumps -- [REMOTE_WT] [LEAF] [LEAF_NW]
[level  5] :   4 node dumps -- [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP]
[level  6] :   1 node dumps -- [NLEAF]
[level 10] :  13 node dumps -- [IGN]
 
State of nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predec
essor):
[143]/0/144/14/0x786fa3dc/3941/SINGLE_NODE_NW/1/2//none
[145]/0/146/5/0x786fc944/3858/LEAF/3/4//153
[148]/0/149/1/0x78700160/3806/SINGLE_NODE/5/6//none
[150]/0/151/1/0x787026c8/3804/SINGLE_NODE/7/8//none
[153]/0/154/5/0x78705ee4/3903/NLEAF/9/10/[145]/none
[154]/0/155/1/0x78707198/3797/IGN/11/12//none
[155]/0/156/1/0x7870844c/3799/IGN/13/14//none
[157]/0/158/5/0x7870a9b4/3810/SINGLE_NODE/15/16//none
[159]/0/160/1/0x7870cf1c/3782/IGN/17/18//none
[160]/0/161/1/0x7870e1d0/3784/IGN/19/20//none
[161]/0/162/1/0x7870f484/3788/IGN/21/22//none
[162]/0/163/1/0x78710738/3786/IGN/23/24//none
[163]/0/164/1/0x787119ec/3774/IGN/25/26//none
[164]/0/165/1/0x78712ca0/3780/IGN/27/28//none
[165]/0/166/1/0x78713f54/3778/IGN/29/30//none
[166]/0/167/1/0x78715208/3776/IGN/31/32//none
[167]/0/168/1/0x787164bc/3770/IGN/33/34//none
[168]/0/169/1/0x78717770/3772/IGN/35/36//none
[169]/0/170/1/0x78718a24/3768/IGN/37/38//none
====================
END OF HANG ANALYSIS
====================

Trace文件内容的解释如下：

CYCLES: This section reports the process dependencies between sessions that are in a deadlock condition. Cycles are considered   “true” hangs.

Cycle 1 : :

    <980/3887/0xe4214964/24065/latch free>

 -- <2518/352/0xe4216560/24574/latch free>

 -- <55/10/0xe41236a8/13751/latch free>

BLOCKER OF MANY SESSIONS: This section is found when a process is blocking a lot of other sessions. Usually when a process is blocking more that 10 sessions this section will appear in the trace file.

Found 21 objects waiting for

    <55/10/0xe41236a8/13751/latch free>

Found 12 objects waiting for

    <2098/2280/0xe42870d0/3022/db file scattered read>

Found 12 objects waiting for

    <1941/1783/0xe41ac9e0/462/No Wait>

Found 12 objects waiting for

    <980/3887/0xe4214964/24065/latch free>

OPEN CHAINS: This section reports sessions involved on a wait chain. A wait chains means that one session is blocking one or more other sessions.

Open chains found:

Chain 1 : :

    <2/1/0xe411b0f4/12280/db file parallel write>

Chain 2 : :

    <3/1/0xe411b410/12282/No Wait>

Chain 6 : :

    <18/1631/0xe4243cf8/25457/db file scattered read>

 -- <229/1568/0xe422b84c/8460/buffer busy waits>

Chain 17 : :

    <56/11/0xe4123ce0/13755/latch free>

 -- <2384/599/0xe41890dc/22488/latch free>

 -- <32/2703/0xe41fa284/25693/latch free>

OTHER CHAINS: It refers to chains of blockers and waiters related to other sessions identified under “open chains”, but not blocked directly by the process reported on the "open chain". 

Other chains found:

Chain 676 : :

    <20/93/0xe411d644/13597/latch free>

Chain 677 : :

    <27/1201/0xe41d3188/15809/latch free>

Chain 678 : :

    <36/1532/0xe428be8c/4232/latch free>

 -- <706/1216/0xe4121aac/23317/latch free>

Chain 679 : :

    <43/12/0xe4122d54/13745/latch free>

Chain 680 : :

    <80/2/0xe41290d4/13811/library cache pin>

 -- <1919/1134/0xe421fdbc/3343/enqueue>

STATE OF NODES

nodenum：定义每个session的序列号，trace文件内部使用
sid: session的sid
sess_srno: session的Serial＃
ospid: OS的进程ID
state: node的状态
adjlist: 表示blocker node
predecessor: 表示waiter node
IN_HANG:这表示该node处于死锁状态,通常还有其他node(blocker)也处于该状态
LEAF/LEAF_NW:该node通常是blocker.通过条目的”predecessor”列可以判断这个node是否是blocker。LEAF说明该NODE没有等待其他资源，而LEAF_NW则可能是没有等待其他资源或者是在使用CPU
NLEAF:通常可以看作这些会话是被阻塞的资源.发生这种情况一般说明数据库发生性能问题而不是数据库hang
IGN/IGN_DMP:这类会话通常被认为是空闲会话，除非其adjlist列里存在node。如果是非空闲会话则说明其adjlist里的node正在等待其他node释放资源。
SINGLE_NODE/SINGLE_NODE_NW:近似于空闲会话.
1.trace中最重要的部分
State of nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):
[145]/0/146/5/0x786fc944/3858/LEAF/9/10//153 --这里的LEAF表示该SID阻塞了predec
essor=153对应的SID=154,即为被阻塞者
[148]/0/149/1/0x78700160/3806/SINGLE_NODE/11/12//none
[150]/0/151/1/0x787026c8/3804/SINGLE_NODE/13/14//none
[153]/0/154/5/0x78705ee4/3903/NLEAF/15/16/[145]/none --这里的NLEAF表示该SID被阻塞了,adjlist对应的145对应的SID=146,即为阻塞者.

实际案例

执行一个split分区的脚本时长时间没有响应。登录上去查看，手工执行了split脚本，发现确实会hang住：
SQL>ALTER TABLE A_PDA_SP_STAT SPLIT PARTITIONP_MAXAT(20090609)
INTO (PARTITIONP_20090608 TABLESPACE TS_DATA_A,PARTITIONP_MAX TABLESPACE TS_DATA_A)
检查该session的等待事件：
EVENT                                 P1         P2         P3
------------------------------ ---------- ---------- ----------
rowcachelock                         8         0         5
查 了网上的一些资料，说和sga的shared pool大小不足有关，或者和sequence的cache不大有关。经过分析，这2个原因应该都不是。因为1、如果是shared pool不足，这样的现象一般是某个sql执行的比较慢，但是还是会执行完，而不是像现在这样的挂住；2，只是执行split分区，并没有和 sequence相关。
在这里，我们用hanganalyze来进行分析。
我们现在来看看出来的trace文件：
SQL> select spid from v$session a,v$process b where a.paddr=b.addr and a.sid=295;
SPID
------------
19237
SQL> oradebug SETOSPID 19237
Oracle pid: 235, Unix process pid: 19237, image: oracle@hl_rdb01 (TNS V1-V3)
SQL> oradebug hanganalyze 3;
Cycle 1: (0/295)
Cycle 2: (0/254)--(0/239)
Hang Analysis in /oracle/app/oracle/admin/hlreport/udump/hlreport_ora_25247.trc
SQL> !
$ more /oracle/app/oracle/admin/hlreport/udump/hlreport_ora_25247.trc
Dump file /oracle/app/oracle/admin/hlreport/udump/hlreport_ora_25247.trc
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
ORACLE_HOME = /oracle/app/oracle/product/9.2.0
System name:    HP-UX
Node name:      hl_rdb01
Release:        B.11.11
Version:        U
Machine:        9000/800
Instance name: hlreport
Redo thread mounted by this instance: 1
Oracle process number: 157
Unix process pid: 25247, image: oracle@hl_rdb01 (TNS V1-V3)
 
*** SESSION ID:(312.10459) 2009-05-20 16:21:58.423
*** 2009-05-20 16:21:58.423
==============
HANG ANALYSIS:
==============
 Cycle 1 ::
    <0/329/43816/0x4d6b5638/23487/rowcachelock>
 --<0/254/19761/0x4d687438/23307/librarycachelock>
Cycle 2 ::
    <0/295/57125/0x4d6b8978/19237/rowcachelock>
Cycle 3 ::
    <0/295/57125/0x4d6b8978/19237/rowcachelock>
Open chains found:
Other chains found:
Chain 1 ::
    <0/312/10459/0x4d69f9b8/25247/NoWait>
Extra information that will be dumped at higher levels:
[level  3] :   4 node dumps -- [IN_HANG]
[level  5] :   1 node dumps -- [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP]
[level 10] : 223 node dumps -- [IGN]
 
State of nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):
[0]/0/1/1/0x4d7146c0/5132/IGN/1/2//none
……………………………………………………
[238]/0/239/57618/0x4d7b18a0/13476/IN_HANG/395/402/[294][238][328][253]/none
……………………………………………………
[253]/0/254/19761/0x4d7bb710/23307/IN_HANG/397/400/[328][238][294]/294
………………………………………………………………
[294]/0/295/57125/0x4d7d6820/19237/IN_HANG/396/401/[294][238][253]/238
[328]/0/329/43816/0x4d7ecf40/23487/IN_HANG/398/399/[253]/253
………………………………………………………………
Dumping System_State and Fixed_SGA in process with ospid 13476
Dumping Process information for process with ospid 13476
Dumping Process information for process with ospid 23307
Dumping Process information for process with ospid 19237
Dumping Process information for process with ospid 23487
====================
END OF HANG ANALYSIS
====================
*** 2009-05-20 16:48:20.686
现在我们来看看我们的trace出来的文件：
Cycle 1 ::
<0/329/43816/0x4d6b5638/23487/row cache lock>
— <0/254/19761/0x4d687438/23307/library cache lock>
Cycle 2 ::
<0/295/57125/0x4d6b8978/19237/row cache lock>
Cycle 3 ::
<0/295/57125/0x4d6b8978/19237/row cache lock>
cycle表示oracle内部确定的死锁。其中我们的当前手工执行split的295进程也在里面。我们观察其他的进程在做什么，如329：
SQL>selectmachine,status,program,sql_textfromv$sessiona,v$sqlareab
 2  wherea.sql_address=b.addressanda.sid=329;
MACHINE   STATUS    PROGRAM                              SQL_TEXT                                      
--------- -------  ---------         -------------------------------------------------------------------
hl_rdb01  ACTIVE   sqlplus@hl_rdb01(TNSV1-V3)   ALTER TABLEA_PDA_SP_STATS PLITPARTITION P_MAXAT(20090609) INTO(PARTITION P_20090608 TABLESPACETS_DATA_A  ,PARTITION P_MAX TABLESPACETS_DATA_A)
SQL>select event from v$session_wait wheresid=329;
EVENT
--------------------------------------------
row cache lock
发现也是在执行split语句，但是问了同事，他已经把之前运行失败的脚本完全kill掉了。估计在数据库中进程挂死了，没有完全的释放。
kill掉329号进程后，发现还是挂住，所以我们继续做hanganlyze：
==============
HANG ANALYSIS:
==============
Cycle 1 ::
    <0/295/57125/0x4d6b8978/19237/rowcachelock>
Cycle 2 ::
    <0/254/19761/0x4d687438/23307/librarycachelock>
 --<0/239/57618/0x4d6b74f8/13476/rowcachelock>
我们继续把其他的进程杀掉。终于295的split执行成功。
SQL>ALTER TABLEA_PDA_SP_STAT SPLIT  PARTITIONP_MAXAT(20090609)
 INTO(PARTITIONP_20090608 TABLESPACETS_DATA_A  ,PARTITION P_MAX TABLESPACETS_DATA_A)
Table altered.
Elapsed:00:31:03.21
继续执行split下一个分区，也很快完成。
SQL>ALTER TABLEA_PDA_SP_STATS PLITPARTITION P_MAXAT(20090610)
 2   INTO(PARTITIONP_20090609 TABLESPACETS_DATA_A
 3   ,PARTITIONP_MAX TABLESPACETS_DATA_A);
Table altered.
Elapsed:00:00:00.02
至此，问题解决.
[238]/0/239/57618/0x4d7b18a0/13476/IN_HANG/395/402/[294][238][328][253]/none
[253]/0/254/19761/0x4d7bb710/23307/IN_HANG/397/400/[328][238][294]/294
[294]/0/295/57125/0x4d7d6820/19237/IN_HANG/396/401/[294][238][253]/238
[328]/0/329/43816/0x4d7ecf40/23487/IN_HANG/398/399/[253]/253
329堵塞住了254
254堵塞住了295
295堵塞住了239
杀掉的应该是329,254,295

 

参考至：http://space.itpub.net/9399028/viewspace-687640/

                   http://dev.21tx.com/2007/08/18/10786.html

                   http://blog.chinaunix.net/uid-77311-id-3245150.html

                   http://blog.csdn.net/tianlesoftware/article/details/6525628

                   http://blog.csdn.net/tianlesoftware/article/details/6321961

                   http://blog.csdn.net/zhangout/article/details/6331656

Hanganalyze 的用法

顾名思义，hanganalyze 是用来在 oracle hang 的时候分析的。对于 oracle 来说，hang 的表现 基本上是 由于 internal deadlock，对于这种 deadlock ，oracle 是没办法自动判断并清除的，而对于 TM 级别的 deadlock，oracle 能自动判断并且能主动停止其中一个。

这里，我简单说下 hanganalyze 的用法，并举一个例子：

1、 介绍下 hanganalyze 级别的定义

    10     Dump all processes (IGN state)

5     Level 4 + Dump all processes involved in wait chains (NLEAF state)

4   Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state)

3          Level 2 + Dump only processes thought to be in a hang (IN_HANG state)

1-2        Only HANGANALYZE output, no process dump at all

一般情况下，我们都定义到 级别 3，metalink 上说，级别 3 以上，需要 oracle support 的人告知后，才能用。

2、 hanganalyze 一些术语的解释

Examples of session “headers”:

Oracle 8.x chain header:  

Oracle9i chain header:   :

Where:

 sid        = Session ID

 sess_srno  = Serial#

 proc_ptr   = Process Pointer

 ospid      = OS Process Id

 wait_event = Waitevent

 cnode      = Node Id (Only available since Oracle9i) 节点代号,如果为rac,其值就存在,单节点的值为0

 

Examples of STATE OF THE NODES DESCRIPTION[番茄花园1] 

Oracle 8.x : [nodenum]/sid/sess_srno/session/state/start/finish/[adjlist]/predecessor

Oracle9i: [nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor

 

Where:

 Nodenum[番茄花园2]  = 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)

 

3、 hanganalyze 的用法

单节点下，可以：

HANGANALYZE may be executed using the following syntax:

ALTER SESSION SET EVENTS 'immediate trace name HANGANALYZE level ';

Or when logged in with the “SYSDBA” role,

ORADEBUG hanganalyze

Rac 下可以：

To perform. cluster wide HANGANALYZE use the following syntax:

 ORADEBUG setmypid

 ORADEBUG setinst all

 ORADEBUG -g def hanganalyze

 

4、 hanganalyze 的解释

hanganalyze 由几个主要部分组成

CYCLES: This section reports the process dependencies between sessions that are in a deadlock condition. Cycles are considered   “true” hangs.  –出现 cycle 表明，系统hang住了

Cycle 1 : :

    <980/3887/0xe4214964/24065/latch free>

 -- <2518/352/0xe4216560/24574/latch free>

  -- <55/10/0xe41236a8/13751/latch free>

 

OPEN CHAINS: This section reports sessions involved on a wait chain. A wait chains means that one session is blocking one or more other sessions.  –出现 open chains 系统不一定会 hang，只是表明系统有可能变慢，也就是 某些session 阻塞了 另外一些 session

Open chains found:

Chain 1 : :

    <2/1/0xe411b0f4/12280/db file parallel write>

Chain 2 : :

    <3/1/0xe411b410/12282/No Wait>

Chain 6 : :

    <18/1631/0xe4243cf8/25457/db file scattered read>

 -- <229/1568/0xe422b84c/8460/buffer busy waits>

Chain 17 : :

    <56/11/0xe4123ce0/13755/latch free>

 -- <2384/599/0xe41890dc/22488/latch free> --这个表明 56的sid 阻塞了 下面两个sid[番茄花园3] 

 -- <32/2703/0xe41fa284/25693/latch free>

 

OTHER CHAINS: It refers to chains of blockers and waiters related to other sessions identified under “open chains”, but not blocked directly by the process reported on the "open chain".

Other chains found:

Chain 676 : :

    <20/93/0xe411d644/13597/latch free>

Chain 677 : :

    <27/1201/0xe41d3188/15809/latch free>

Chain 678 : :

    <36/1532/0xe428be8c/4232/latch free>

 -- <706/1216/0xe4121aac/23317/latch free>

Chain 679 : :

    <43/12/0xe4122d54/13745/latch free>

Chain 680 : :

    <80/2/0xe41290d4/13811/library cache pin>

 -- <1919/1134/0xe421fdbc/3343/enqueue>

 

State of nodes：  --这部分内容比较重要，列出了具体的 session 信息，和阻塞信息

The following describes the important states to be considered:

 

 

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:

 

 [nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor

 [16]/0/17/154/0x24617be0/26800/IN_HANG/29/32/[185]/19

 [185]/1/16/4966/0x24617270//IN_HANG/30/31/[16]/16

 

In this example the node [16] is waiting for node [185], and the other way around; this is  a cyclical condition (deadlock).

 

 

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. A typical representation of these nodes when they are considered blockers is:

 

 [ nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor

 [16]/0/17/154/0x24617be0/26800/LEAF/29/30//19

 [19]/0/20/13/0x24619830/26791/NLEAF/33/34/[16]/186

 

In this example, node [16] is blocking node [19]. Notice that node [16] has node [19] in the predecessor field.

Also notice that node [19] has node [16] in the adjacent list.

 

 

NLEAF : These sessions are usually considered as “stuck” sessions. It means that there is another session holding a resource needed by the session in this state. By using the adjlist, you can determine which node is the blocker of this process. When many sessions are found in this state, it is likely the database is experiencing a performance problem rather than a hang problem.

 

 

 

IGN and IGN_DMP : Sessions in this state are usually considered as IDLE sessions, unless they reference a node in the “ adjlist” field. In this case, the node is waiting for another node, so it will be considered as a ‘stuck’ session as well.

Extending the previous example,

 

 [nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor

 [16]/0/17/154/0x24617be0/26800/LEAF/29/30//19

 [19]/0/20/13/0x24619830/26791/NLEAF/33/34/[16]/186

 [189]/1/20/36/0x24619830//IGN/95/96/[19]/none

 [176]/1/7/1/0x24611d80//IGN/75/76//none

 

You may notice that node [189] is waiting for node [19] which in turn is waiting for node [16], while node [176] is an IDLE session . This maybe the case when a session has a DML lock but never finished the transaction.

 

In Oracle9i, two new states were introduced to differentiate between LEAF nodes that have other nodes waiting behind them (i.e., LEAF nodes that are blockers) vs. LEAF nodes that are not affecting other nodes.

 

 

SINGLE_NODE and SINGLE_NODE_NW:

This can be described the same as LEAF and LEAF_NW, except that they don't have processes depending on them.

 

5、 hanganalyze 的例子

以下是一个例子，先主要说明下这个例子：

Session 1（sid 71）：执行 update 语句，跟新表中的一行，但是不提交

UPDATE Test_c c SET c.ch1='bb' WHERE c.ch1='b'

Session 2-14：分别都执行上面的语句，由于 sid 71 未提交，导致这几个 session 出现等待，这时候，执行 hanganalyze，可看到如下内容：

==============

HANG ANALYSIS:

==============

Found 13 objects waiting for

    <0/71/2410/0x20d85bc4/3568/SQL*Net message from client>  --当一个 session 导致了超过 10 个的session 去等待的时候，就会出现上面的内容

Open chains found:

Chain 1 : :

    <0/71/2410/0x20d85bc4/3568/SQL*Net message from client>

 -- <0/42/2/0x20d8cd2c/6112/enq: TX - row lock contention>  --这里 只记录了一个 session 去等待 sid 71 这个session

Other chains found:

Chain 2 : :

    <0/52/6959/0x20d855cc/5024/jobq slave wait>

Chain 3 : :

    <0/58/2141/0x20d88b84/4300/No Wait>

Chain 4 : :

    <0/59/1/0x20d8799c/2608/Streams AQ: waiting for time man>

Chain 5 : :

    <0/60/1/0x20d873a4/2604/Streams AQ: qmn slave idle wait>

Chain 6 : :

    <0/62/1/0x20d86dac/2452/Streams AQ: qmn coordinator idle>

Extra information that will be dumped at higher levels:

[level  4] :   1 node dumps -- [REMOTE_WT] [LEAF] [LEAF_NW]

[level  5] :   6 node dumps -- [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP]

[level 10] :  26 node dumps -- [IGN]

 

State of nodes

([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):

[41]/0/42/2/0x20dd427c/6112/IGN_DMP/1/4/[70]/none

[42]/0/43/2/0x20dd5664/4288/IGN/5/6/[70]/none

[43]/0/44/2/0x20dd6a4c/4688/IGN/7/8/[70]/none

[44]/0/45/2/0x20dd7e34/1448/IGN/9/10/[70]/none

[45]/0/46/2/0x20dd921c/2596/IGN/11/12/[70]/none

[46]/0/47/938/0x20dda604/4256/IGN/13/14/[70]/none

[47]/0/48/229/0x20ddb9ec/6028/IGN/15/16/[70]/none

[48]/0/49/3/0x20ddcdd4/5296/IGN/17/18/[70]/none

[49]/0/50/11/0x20dde1bc/2984/IGN/19/20/[70]/none

[50]/0/51/548/0x20ddf5a4/3156/IGN/21/22/[70]/none

[51]/0/52/6959/0x20de098c/5024/SINGLE_NODE/23/24//none

[52]/0/53/1426/0x20de1d74/1100/IGN/25/26//none

[53]/0/54/8445/0x20de315c/3536/IGN/27/28/[70]/none

[57]/0/58/2141/0x20de80fc/4300/SINGLE_NODE_NW/29/30//none

[58]/0/59/1/0x20de94e4/2608/SINGLE_NODE/31/32//none

[59]/0/60/1/0x20dea8cc/2604/SINGLE_NODE/33/34//none

[61]/0/62/1/0x20ded09c/2452/SINGLE_NODE/35/36//none

[63]/0/64/116/0x20def86c/5080/IGN/37/38/[70]/none

[66]/0/67/3/0x20df3424/3188/IGN/39/40//none

[67]/0/68/3/0x20df480c/3764/IGN/41/42//none

[69]/0/70/1227/0x20df6fdc/4500/IGN/43/44/[70]/none

[70]/0/71/2410/0x20df83c4/3568/LEAF/2/3//41

[71]/0/72/1/0x20df97ac/3572/IGN/45/46//none

[72]/0/73/1/0x20dfab94/3564/IGN/47/48//none

[73]/0/74/1/0x20dfbf7c/3556/IGN/49/50//none

[74]/0/75/1/0x20dfd364/3548/IGN/51/52//none

[75]/0/76/1/0x20dfe74c/3540/IGN/53/54//none

[76]/0/77/1/0x20dffb34/3532/IGN/55/56//none

[77]/0/78/1/0x20e00f1c/3528/IGN/57/58//none

[78]/0/79/1/0x20e02304/3524/IGN/59/60//none

[79]/0/80/1/0x20e036ec/3496/IGN/61/62//none

[80]/0/81/1/0x20e04ad4/3492/IGN/63/64//none

[81]/0/82/1/0x20e05ebc/3488/IGN/65/66//none

====================

END OF HANG ANALYSIS

====================

如果出现的事态紧急，这时，我们可以 观察下 出现 blocker 的那个 sid 的语句，如果有必要，就kill 掉

 

参考文档：

Metalink：ID 215858.1