当系统出现问题时,我们的首要任务是查找原因,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:(sqlplus@hqcrmdb01 (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
初级连接已建立