分类：
2009-05-26 01:21:52
数据库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>
<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里列出，比如：
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包（压缩格式）
 |
|
