oradebug hanganalyze

三个session依次执行：

session 130:

exec p0001:

--create or replace procedure p0001 as
--begin
--   dbms_lock.sleep(300); --1000 seconds
--end;

session 141:

alter procedure p0001 compile;

session 131:

alter procedure p0001 compile;

执行hanganalyze，分析结果

SQL> oradebug hanganalyze 3;

Hang Analysis in /u01/app/oracle/admin/orcl/udump/orcl_ora_12928.trc

[oracle@localhost ~]$ more /u01/app/oracle/admin/orcl/udump/orcl_ora_12928.trc

/u01/app/oracle/admin/orcl/udump/orcl_ora_12928.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/11.2.0/db_1
System name:    Linux
Node name:      localhost.localdomain
Release:        2.6.18-164.el5
Version:        #1 SMP Tue Aug 18 15:51:54 EDT 2009
Machine:        i686
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 24
Unix process pid: 12928, image: oracle@localhost.localdomain (TNS V1-V3)


*** SERVICE NAME:(SYS$USERS) 2013-10-15 21:23:56.904
*** SESSION ID:(134.14) 2013-10-15 21:23:56.904
*** 2013-10-15 21:23:56.904
==============
HANG ANALYSIS:
==============
Open chains found:
Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/130/38/0x3ae22464/13009/PL/SQL lock timer>                                   --阻塞者
 -- <0/141/14/0x3ae218fc/12991/library cache pin>                                       --  被 阻塞者    
 -- <0/131/13/0x3ae22a18/13013/library cache lock>                                    --被 阻塞者      
Other chains found:
Chain 2 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/132/1/0x3ae21348/9857/Streams AQ: waiting for time man>
Chain 3 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/134/14/0x3ae1e5a8/12928/No Wait>
Chain 4 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/135/20/0x3ae207e0/12820/jobq slave wait>
Chain 5 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/145/1/0x3ae1eb5c/9834/Streams AQ: qmn coordinator idle>
Chain 6 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/148/11/0x3ae2022c/12818/jobq slave wait>
Chain 7 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/153/71/0x3ae1c924/12758/Streams AQ: qmn slave idle wait>
Chain 8 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/154/37/0x3ae20d94/12822/jobq slave wait>
Chain 9 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/158/185/0x3ae1f110/9839/jobq slave wait>
Extra information that will be dumped at higher levels:
[level  4] :   1 node dumps -- [REMOTE_WT] [LEAF] [LEAF_NW] 
[level  5] :   8 node dumps -- [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP] 
[level  6] :   2 node dumps -- [NLEAF] 
[level 10] :  22 node dumps -- [IGN] 
 

State of nodes             ----重点

([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):
[128]/0/129/3/0x3aee8b50/9933/IGN/1/2//none
[129]/0/130/38/0x3aee9e04/13009/LEAF/3/4//140
[130]/0/131/13/0x3aeeb0b8/13013/NLEAF/5/8/[140]/none
[131]/0/132/1/0x3aeec36c/9857/SINGLE_NODE/9/10//none
[132]/0/133/18/0x3aeed620/11596/IGN/11/12//none
[133]/0/134/14/0x3aeee8d4/12928/SINGLE_NODE_NW/13/14//none
[134]/0/135/20/0x3aeefb88/12820/SINGLE_NODE/15/16//none
[136]/0/137/19/0x3aef20f0/9964/IGN/17/18//none
[139]/0/140/9/0x3aef590c/9967/IGN/19/20//none
[140]/0/141/14/0x3aef6bc0/12991/NLEAF/6/7/[129]/130
[141]/0/142/2/0x3aef7e74/9880/IGN/21/22//none
[144]/0/145/1/0x3aefb690/9834/SINGLE_NODE/23/24//none
[147]/0/148/11/0x3aefeeac/12818/SINGLE_NODE/25/26//none
[148]/0/149/30/0x3af00160/12843/IGN/27/28//none
[149]/0/150/25/0x3af01414/11311/IGN/29/30//none
[150]/0/151/1/0x3af026c8/9824/IGN/31/32//none
[151]/0/152/1/0x3af0397c/9822/IGN/33/34//none
[152]/0/153/71/0x3af04c30/12758/SINGLE_NODE/35/36//none
[153]/0/154/37/0x3af05ee4/12822/SINGLE_NODE/37/38//none
[155]/0/156/1/0x3af0844c/9797/IGN/39/40//none
[156]/0/157/1/0x3af09700/9793/IGN/41/42//none
[157]/0/158/185/0x3af0a9b4/9839/SINGLE_NODE/43/44//none
[159]/0/160/1/0x3af0cf1c/9786/IGN/45/46//none
[160]/0/161/1/0x3af0e1d0/9782/IGN/47/48//none
[161]/0/162/1/0x3af0f484/9784/IGN/49/50//none
[162]/0/163/1/0x3af10738/9778/IGN/51/52//none
[163]/0/164/1/0x3af119ec/9780/IGN/53/54//none
[164]/0/165/1/0x3af12ca0/9776/IGN/55/56//none
[165]/0/166/1/0x3af13f54/9772/IGN/57/58//none
[166]/0/167/1/0x3af15208/9774/IGN/59/60//none
[167]/0/168/1/0x3af164bc/9770/IGN/61/62//none
[168]/0/169/1/0x3af17770/9768/IGN/63/64//none
[169]/0/170/1/0x3af18a24/9766/IGN/65/66//none
====================
END OF HANG ANALYSIS

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

HANGANALYZE 的使用方式

 SQL>ORADEBUG hanganalyze <level>

       --for 单实例

 

       To perform cluster wide HANGANALYZE use the following syntax:

              SQL>ORADEBUG setmypid
              SQL>ORADEBUG setinst all
              SQL>ORADEBUG -g def hanganalyze <level>

       --for RAC 实例

说明：

这个trace 文件中最重要的部分是：

State of nodes

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

[123]/0/124/42982/0x30ee25bc/24834/IGN/1/2//none

[124]/0/125/52764/0x30ee3870/20962/SINGLE_NODE/3/4//none

[128]/0/129/26/0x30ee8340/4576/SINGLE_NODE/5/6//none

在上面的实例中的解释如下：

 

nodenum：定义每个session的序列号

sid：session的sid

sess_srno：session的Serial＃

ospid：OS的进程ID

state：node的状态

adjlist：表示blocker node

predecessor：表示waiter node

 

State有如下几种状态：

       （1）IN_HANG：如果Session处于这种状态，表示Session遇到deadlock或者处于hung状态。

       （2）LEAF/LEAF_NW：这些Session通常是“blocker”或者是等待某些资源的“slow” node，通过字段“predecessor” 可以很容易标识出这些node。

       （3）NLEAF：这些Session通常被认为是“stuck”会话，意味着这些Session在等待某些Session的资源。通过字段“adjlist”可以很容易的定义该进程的blocker。

       （4）IGN/IGN_DMP：这些Session通常是IDLE Session。