当系统出现问题时,我们的首要任务是查找原因,hanganalyze是一种性能分析工具,可以用于查找原因,正如其意思用于分析数据库hang时,当然也可以用于其他一些等待事件的查找例如library cache pin.其具体语法如下
级别大概分为1-2,3,4,5,10 个人觉得级别3应该足够了
单节点
SQL>alter session set events 'immediate trace name hanganalyze 3';
SQL>oradebug hanganalyze 3;
rac操作
SQL>oradebug setinst all
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> oradebug hanganalyze 3;
Hang Analysis in /opt/oracle/admin/crm/udump/crm_ora_32640.trc
[oracle(hqcrmdb01)@~]$ cat /opt/oracle/admin/crm/udump/crm_ora_32640.trc|less
/opt/oracle/admin/crm/udump/crm_ora_32640.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 = /opt/oracle/product/10.2.0/db_1
System name: Linux
Node name: hqcrmdb01
Release: 2.6.9-55.ELsmp
Version: #1 SMP Fri Apr 20 16:36:54 EDT 2007
Machine: x86_64
Instance name: crm
Redo thread mounted by this instance: 1
Oracle process number: 143
*** ACTION NAME:() 2010-03-17 15:41:51.054
*** MODULE NAME:( (TNS V1-V3)) 2010-03-17 15:41:51.054
*** SERVICE NAME:(SYS$USERS) 2010-03-17 15:41:51.054
*** SESSION ID:(38.5960) 2010-03-17 15:41:51.054
*** 2010-03-17 15:41:51.054
==============
HANG ANALYSIS:
==============
Found 18 objects waiting for <0/829/8609/0x5e945e10/12399/SQL*Net message from client>Open chains found:
Chain 1 : :
<0/829/8609/0x5e945e10/12399/SQL*Net message from client>
-- <0/85/54191/0x5f90e280/27085/enq: TX - row lock contention>
Other chains found:
Chain 2 : :
<0/38/5960/0x5e8f5558/32640/No Wait>
Chain 3 : :
<0/230/54208/0x5e918e98/32259/No Wait>
Chain 4 : :
<0/288/59028/0x5e9a0cd0/32601/No Wait>
Chain 5 : :
<0/338/42490/0x5e8da280/7785/Streams AQ: qmn slave idle wait>
Chain 6 : :
<0/348/4963/0x5f9a5db8/32098/No Wait>
其内容意思大概如下
cnode--节点代号,如果为rac,其值就存在,单节点的值为0
sid---session的sid
sess_srno---session的serial#
proc_ptr--系统进程指向的address
ospid ----进程号
wait_event---session的等待事件
从红色部分可以看出
session-829/8609阻塞了18个session.类型为enq: TX - row lock contention
查询得知该session为user session,kill掉就ok了
当数据库hang住了,无法正常登录数据库时,我们可以使用prelim参数
C:\Documents and Settings\169505>sqlplus -prelim /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on 星期三 3月 17 16:24:23 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> conn /as sysdba
初级连接已建立