oracle hang analyze,什么是HANGANALYZE，如何使用HANGANALYZE

数据库HANG住是计较头痛的事情，如何找到HANG住的原因，是DBA必须面临的课题。当数据库HANG住的时候，大多数DBA往往是通过V$SESSION_WAIT视图来进行分析。实际上Oracle有一个十分有效的工具----hanganalyze。HANGANALYZE可以十分清晰的将HANG住的信息告诉DBA，便于DBA进行进一步分析。

Hanganalyze是从Oracle 8i r2(8.1.6)开始提供的，其用法十分简单：

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

或者

ORADEBUG hanganalyze

比如：

sql>oradebug setmypid;

sql>oradebug hanganalyze 3;

对于：

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级的HANGANALYZE(一般情况下使用3级就可以了)

/oracle/app/oracle/admin/bill/udump/ora_4550_bill.trc

Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production

With the Partitioning option

JServer Release 8.1.7.4.0 - 64bit Production

ORACLE_HOME = /oracle/app/oracle/product/8.1.7

System name: HP-UX

Node name: yzstat

Release: B.11.11

Version: U

Machine: 9000/800

Instance name: bill

Redo thread mounted by this instance: 2

Oracle process number: 75

Unix process pid: 4550, image: (TNS V1-V3)

*** SESSION ID:(98.8617) 2008-02-23 17:45:07.011

*** 2008-02-23 17:45:07.010

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

HANG ANALYSIS:

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

Open chains found:

Chain 1 : :

<48/29750/0x3ed60060/7856/PX Deq: Join ACK>

-- <141/31823/0x3ed5d680/2291/library cache pin>Chain 2 : :

<76/16377/0x3ed554b0/6507/No Wait>

Chain 3 : :

<98/8617/0x3ed65c80/4550/No Wait>

Chain 4 : :

<117/43613/0x3ed55080/6505/No Wait>

Other chains found:

Extra information that will be dumped at higher levels:

[level  4] :   4 node dumps -- [LEAF] [LEAF_NW] [IGN_DMP]

[level  5] :   1 node dumps -- [NLEAF]

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

State of nodes

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

[0]/1/1/0x3ee65290/IGN/1/2//none

[1]/2/1/0x3ee65c10/IGN/3/4//none

[2]/3/1/0x3ee66590/IGN/5/6//none

[3]/4/1/0x3ee66f10/IGN/7/8//none

[4]/5/1/0x3ee67890/IGN/9/10//none

[5]/6/1/0x3ee68210/IGN/11/12//none

[6]/7/1/0x3ee68b90/IGN/13/14//none

[8]/9/28264/0x3ee69e90/IGN/15/16//none

[9]/10/45631/0x3ee6a810/IGN/17/18//none

[10]/11/53468/0x3ee6b190/IGN/19/20//none

[11]/12/1/0x3ee6bb10/IGN/21/22//none

[12]/13/1/0x3ee6c490/IGN/23/24//none

[13]/14/39041/0x3ee6ce10/IGN/25/26//none

[16]/17/12002/0x3ee6ea90/IGN/27/28//none

[17]/18/26556/0x3ee6f410/IGN/29/30//none

[19]/20/7116/0x3ee70710/IGN/31/32//none

[20]/21/57406/0x3ee71090/IGN/33/34//none

[21]/22/43997/0x3ee71a10/IGN/35/36//none

[22]/23/38835/0x3ee72390/IGN/37/38//none

[23]/24/50707/0x3ee72d10/IGN/39/40//none

[24]/25/37623/0x3ee73690/IGN/41/42//none

[27]/28/25010/0x3ee75310/IGN/43/44//none

[28]/29/11415/0x3ee75c90/IGN/45/46//none

[30]/31/56343/0x3ee76f90/IGN/47/48//none

[31]/32/32598/0x3ee77910/IGN/49/50//none

[33]/34/60664/0x3ee78c10/IGN/51/52//none

[39]/40/15619/0x3ee7c510/IGN/53/54//none

[40]/41/5750/0x3ee7ce90/IGN/55/56//none

[43]/44/37048/0x3ee7eb10/IGN/57/58//none

[44]/45/10277/0x3ee7f490/IGN/59/60//none

[46]/47/49868/0x3ee80790/IGN/61/62//none

[47]/48/29750/0x3ee81110/LEAF/63/64//140

[50]/51/19008/0x3ee82d90/IGN/65/66//none

[54]/55/45303/0x3ee85390/IGN/67/68//none

[55]/56/12036/0x3ee85d10/IGN/69/70//none

[56]/57/35536/0x3ee86690/IGN/71/72//none

[59]/60/16507/0x3ee88310/IGN/73/74//none

[61]/62/1474/0x3ee89610/IGN/75/76//none

[62]/63/11770/0x3ee89f90/IGN/77/78//none

[64]/65/6967/0x3ee8b290/IGN/79/80//none

[65]/66/22994/0x3ee8bc10/IGN/81/82//none

[66]/67/29782/0x3ee8c590/IGN/83/84//none

[69]/70/8179/0x3ee8e210/IGN/85/86//none

[70]/71/64787/0x3ee8eb90/IGN/87/88//none

[73]/74/14398/0x3ee90810/IGN/89/90//none

[74]/75/52024/0x3ee91190/IGN/91/92//none

[75]/76/16377/0x3ee91b10/LEAF_NW/93/94//none

[76]/77/13846/0x3ee92490/IGN/95/96//none

[80]/81/8490/0x3ee94a90/IGN/97/98//none

[81]/82/14268/0x3ee95410/IGN/99/100//none

[83]/84/15851/0x3ee96710/IGN/101/102//none

[84]/85/46327/0x3ee97090/IGN/103/104//none

[88]/89/30265/0x3ee99690/IGN/105/106//none

[91]/92/43886/0x3ee9b310/IGN/107/108//none

[94]/95/38899/0x3ee9cf90/IGN/109/110//none

[95]/96/53153/0x3ee9d910/IGN/111/112//none

[97]/98/8617/0x3ee9ec10/LEAF_NW/113/114//none

[98]/99/23949/0x3ee9f590/IGN/115/116//none

[99]/100/18928/0x3ee9ff10/IGN/117/118//none

[100]/101/8726/0x3eea0890/IGN/119/120//none

[106]/107/26774/0x3eea4190/IGN/121/122//none

[107]/108/11313/0x3eea4b10/IGN/123/124//none

[108]/109/53920/0x3eea5490/IGN/125/126//none

[109]/110/43621/0x3eea5e10/IGN/127/128//none

[111]/112/45774/0x3eea7110/IGN/129/130//none

[113]/114/10520/0x3eea8410/IGN/131/132//none

[114]/115/41649/0x3eea8d90/IGN/133/134//none

[116]/117/43613/0x3eeaa090/LEAF_NW/135/136//none

[118]/119/18074/0x3eeab390/IGN/137/138//none

[119]/120/44156/0x3eeabd10/IGN/139/140//none

[120]/121/25600/0x3eeac690/IGN/141/142//none

[122]/123/60946/0x3eead990/IGN/143/144//none

[124]/125/26026/0x3eeaec90/IGN/145/146//none

[126]/127/28459/0x3eeaff90/IGN/147/148//none

[128]/129/22272/0x3eeb1290/IGN/149/150//none

[129]/130/19774/0x3eeb1c10/IGN/151/152//none

[130]/131/2674/0x3eeb2590/IGN/153/154//none

[134]/135/36030/0x3eeb4b90/IGN/155/156//none

[135]/136/48766/0x3eeb5510/IGN/157/158//none

[137]/138/60856/0x3eeb6810/IGN/159/160//none

[138]/139/37634/0x3eeb7190/IGN/161/162//none

[140]/141/31823/0x3eeb8490/NLEAF/163/164/[47]/none

[142]/143/8778/0x3eeb9790/IGN/165/166//none

*** WARNING: no system state dumped ***

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

END OF HANG ANALYSIS

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

我们可以看到在OPEN CHAINS里红色部分就是可能HANG住的环。对于Oracle确定的内部死锁，Oracle会在CYCLES里列出，比如：Cycle 1 : :

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

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

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

根据HANGANALYZE的结果，我们再去分析原因就容易多了

hangfg是Oracle的hanganalyze工具。安装十分简单，只需要tar xvf hangfg.tar就可以了。

运行hangfg.sh加上参数ARG1就可以了。

ARG1：

1：轻量级采集，采集2个3级的HANGANALYZE Trace，并且判断是否可以采集一个LEVEL 4的HANGANALYZE TRACE。如果可以就采集，否则就不采集。

2：中等级别采集，采集1个LEVEL 3的HANGANALYZE TRACE，并判断是否可以采集2个LEVEL 4的HANGANALYZE TRACE,如果不能采集，就再采集一个LEVEL 3的HANGANALYZE TRACE.无论如何，都回采集一个266级的SYSTEMSTATE DUMP

3：重量级采集，开销较大。采集2个LEVEL 4的HANGANALYZE TRACE和2个266级的SYSTEMSTATE DUMP

本工具支持RAC，在RAC环境下，使用-g参数可以采集RAC环境的信息。这对于某个实例HANG住无法做任何操作的情况，十分有用，可以从另外一个实例进行采集。

采集后生成：

hangfiles.out：包含采集的文件清单

hangfg.log：工具的日志

hfiles.tar.Z：采集的所有文件的tar包(压缩格式)