Oracle HANGANALYZE

环境:
OS:Oracle Linux As 5.5
DB:11.2.0.1 RAC


如下我们通过使用hangalyze获取到两个会话更新同一行数据的等待事件情况.

1.session1更新行数据

SQL> create table tb_test (id number,name varchar2(10)) tablespace users;

Table created.

SQL> insert into tb_test values(1,'hang test');

1 row created.

SQL> commit;

Commit complete.

SQL> select userenv('sid') from dual;

USERENV('SID')
--------------
            69

SQL> update tb_test set name='hang' where id=1;

1 row updated.

SQL>

注意：这里不执行commit动作

2.session2同样更新session1更新的行

SQL> conn scott/oracle
Connected.
SQL> select userenv('sid') from dual;

USERENV('SID')
--------------
            33

SQL> update tb_test set name='hang' where id=1;

注意：这个时候已经hang住了

3.session3使用hangalyze生成trace文件
SQL> connect / as sysdba
Connected.

SQL> oradebug -g def hanganalyze 3;
Hang Analysis in /u01/app/oracle/diag/rdbms/racdb/racdb1/trace/racdb1_diag_6042.trc
SQL>

4.查看trace文件的内容

[oracle@host01-racdb1 ~]$ cat /u01/app/oracle/diag/rdbms/racdb/racdb1/trace/racdb1_diag_6042.trc
Trace file /u01/app/oracle/diag/rdbms/racdb/racdb1/trace/racdb1_diag_6042.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name:    Linux
Node name:      host01.example.com
Release:        2.6.18-194.el5
Version:        #1 SMP Mon Mar 29 22:10:29 EDT 2010
Machine:        x86_64
Instance name: racdb1
Redo thread mounted by this instance: 0 <none>
Oracle process number: 5
Unix process pid: 6042, image: oracle@host01.example.com (DIAG)


*** 2017-09-17 20:25:29.980
*** SESSION ID:(5.1) 2017-09-17 20:25:29.980
*** CLIENT ID:() 2017-09-17 20:25:29.980
*** SERVICE NAME:() 2017-09-17 20:25:29.980
*** MODULE NAME:() 2017-09-17 20:25:29.980
*** ACTION NAME:() 2017-09-17 20:25:29.980
 
Node id: 0
List of nodes: 0,

*** 2017-09-17 20:25:29.981
Reconfiguration starts [incarn=0]

*** 2017-09-17 20:25:29.981
I'm the master node

*** 2017-09-17 20:25:29.981
Reconfiguration completes [incarn=1]
Group reconfiguration cleanup

*** 2017-09-17 20:25:34.375
DIAG attached to DLM

*** 2017-09-17 20:27:18.434
cluster reconfiguration is ongoing: 0, 1,

*** 2017-09-17 20:27:18.467
Reconfiguration starts [incarn=2]

*** 2017-09-17 20:27:18.467
I'm the master node
Group reconfiguration cleanup

*** 2017-09-17 20:27:18.954
A rcfg proposal from node 1 is received

*** 2017-09-17 20:27:18.954
Reconfiguration completes [incarn=2]

*** 2017-09-17 21:30:25.497
DIAG sends out oradebug command to nodes [0,1]

*** 2017-09-17 21:30:26.223
****************************************************************
PORADEBUG BEGIN ORIGINATING INST:1 SERIAL:0 PID:19045
PORADATA COMMAND:hanganalyze 3
session (kjzddbx) switches to a new action
PORADATA TRACEFILE /u01/app/oracle/diag/rdbms/racdb/racdb1/trace/racdb1_diag_6042.trc
PORADEBUG END ORIGINATING INST:1 SERIAL:0 PID:19045
****************************************************************

*** 2017-09-17 21:30:28.293
===============================================================================
HANG ANALYSIS:
  instances (db_name.oracle_sid): racdb.racdb1, racdb.racdb2
  oradebug_node_dump_level: 3
  analysis initiated by oradebug
===============================================================================
 
Chains most likely to have caused the hang:
 [a] Chain 1 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'
     Chain 1 Signature Hash: 0x38c48850
 [b] Chain 2 Signature: 'Streams AQ: waiting for messages in the queue'
     Chain 2 Signature Hash: 0xa00e2e8