hanganalyz 性能诊断工具
hanganalyze是ORACLE的一款性能診斷工具，這個款工具是從oracle 8.0.6開始可用，在oracle數據庫出現嚴重的性能問題的時候它可以幫助你定位問題所在。
?
1.首先說說hanganalyze工具的用法
對于單實例數據庫語法如下
alter session set events 'immediate trace name hanganalyze level <level>';
或則使用oradebug進行hanganalyze
conn /as sysdba
SQLPLUS>oradebug hanganalyze <level>;
?
對于RAC數據的語法如下
con /as sysda
SQLPLUS> oradebug setmypid
SQLPLUS>oradebug setinst all
SQLPLUS>oradebug -g def hanganalyze <level>
?
?
關于level的說明：
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
#############################
[level? 4] :? 23 node dumps -- [LEAF] [LEAF_NW] [IGN_DMP]
[level? 5] :? 36 node dumps -- [NLEAF]
[level 10] : 130 node dumps -- [IGN]
?
?
?
2.dump文件的分析
?
下面是一個例子：
[oracle@SHDBService01 ~]$ more /data/oracle/admin/ora10g/udump/ora10g_ora_28378.trc
/data/oracle/admin/ora10g/udump/ora10g_ora_28378.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /data/oracle/product/10.2.1
System name:
Linux
Node name:
SHDBService01
Release:
?
2.6.9-67.ELlargesmp
Version:
?
#1 SMP Wed Nov 7 14:07:22 EST 2007
Machine:
?
x86_64
Instance name: ora10g
Redo thread mounted by this instance: 1
Oracle process number: 62
Unix process pid: 28378, image: oracle@SHDBService01 (TNS V1-V3)
?
*** ACTION NAME:() 2009-08-21 13:36:46.238
*** MODULE NAME:(sqlplus@SHDBService01 (TNS V1-V3)) 2009-08-21 13:36:46.238
*** SERVICE NAME:(SYS$USERS) 2009-08-21 13:36:46.238
*** SESSION ID:(532.3192) 2009-08-21 13:36:46.238
*** 2009-08-21 13:36:46.238
==============
HANG ANALYSIS:
==============
Open chains found:
Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/542/2126/0xd1006f28/25642/SQL*Net message from client>
-- <0/1097/44386/0xd2001048/26064/enq: TX - row lock contention>
Other chains found:
Chain 2 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/532/3192/0xd1007710/28378/No Wait>
Chain 3 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/534/3/0xd10096b0/30838/Streams AQ: waiting for time man>
Chain 4 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/539/3/0xd1008ec8/30830/Streams AQ: qmn coordinator idle>
Chain 5 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/541/2497/0xd1005f58/16409/jobq slave wait>
Chain 6 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/1099/3/0xd2002fe8/30840/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] :?? 5 node dumps -- [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP]
[level? 6] :?? 1 node dumps -- [NLEAF]
[level 10] :? 17 node dumps -- [IGN]
?
State of nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):
[531]/0/532/3192/0xd13bd850/28378/SINGLE_NODE_NW/1/2//none
[532]/0/533/655/0xd13bedb8/19321/IGN/3/4//none
[533]/0/534/3/0xd13c0320/30838/SINGLE_NODE/5/6//none
[538]/0/539/3/0xd13c6e28/30830/SINGLE_NODE/7/8//none
[540]/0/541/2497/0xd13c98f8/16409/SINGLE_NODE/9/10//none
[541]/0/542/2126/0xd13cae60/25642/LEAF/11/12//1096
[542]/0/543/1/0xd13cc3c8/30755/IGN/13/14//none
[543]/0/544/1/0xd13cd930/30753/IGN/15/16//none
[544]/0/545/1/0xd13cee98/30751/IGN/17/18//none
[545]/0/546/1/0xd13d0400/30749/IGN/19/20//none
[546]/0/547/1/0xd13d1968/30746/IGN/21/22//none
[547]/0/548/1/0xd13d2ed0/30744/IGN/23/24//none
[548]/0/549/1/0xd13d4438/30734/IGN/25/26//none
[549]/0/550/1/0xd13d59a0/30732/IGN/27/28//none
[550]/0/551/1/0xd13d6f08/30730/IGN/29/30//none
[551]/0/552/1/0xd13d8470/30728/IGN/31/32//none
[1096]/0/1097/44386/0xd23cee98/26064/NLEAF/33/34/[541]/none
[1098]/0/1099/3/0xd23d1968/30840/SINGLE_NODE/35/36//none
[1099]/0/1100/6/0xd23d2ed0/30861/IGN/37/38//none
[1101]/0/1102/1/0xd23d59a0/30742/IGN/39/40//none
[1102]/0/1103/1/0xd23d6f08/30736/IGN/41/42//none
[1651]/0/1652/3/0xd13d3c50/30858/IGN/43/44//none
[1653]/0/1654/1/0xd13d6720/30738/IGN/45/46//none
[2204]/0/2205/1/0xd03d6720/30740/IGN/47/48//none
====================
END OF HANG ANALYSIS
====================
?
?
open chains部分例子
Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/542/2126/0xd1006f28/25642/SQL*Net message from client>
-- <0/1097/44386/0xd2001048/26064/enq: TX - row lock contention>
Other chains found:
?
sid??????? = Session ID
sess_srno? = Serial#
proc_ptr?? = Process Pointer
ospid????? = OS Process Id
wait_event = Waitevent
cnode????? = Node Id (Only available since Oracle9i)
?
State of nodes部分例子：
[nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor
[541]/0/542/2126/0xd13cae60/25642/LEAF/11/12//1096
[1096]/0/1097/44386/0xd23cee98/26064/NLEAF/33/34/[541]/none
?
?
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)
?
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
?
?
可以看出上面例子中看出
?
session? 542?? 2126阻塞了session 1097 44386.
?
?
?
3.在很多情況下如果數據庫HANG住則無法登陸sqlplus，這時如果想要對系統進行hanganalyze可以加參數登錄sqlplus
具體語法如下：
?
[oracle@SHDBService01 ~]$ sqlplus -prelim /nolog
?
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Aug 21 15:42:23 2009
?
Copyright (c) 1982, 2007, Oracle.? All Rights Reserved.
?
SQL> conn /as sysdba
Prelim connection established
SQL>
?
?
prelim參數只對10g 以后的版本有效。
?
10g以前的版本不能登錄SQLPLUS時可以使用dbx或則gdb。
?
總結
以上是生活随笔為你收集整理的hanganalyz 性能诊断工具的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 一个修改RAC REDO引起的DATAG
- 下一篇： oracle trace文件解读