oracle hang analyze,oracle hanganalyze初步研究

当系统出现问题时,我们的首要任务是查找原因,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

初级连接已建立