产生hanganalyze跟踪文件

Metalink的对hanganalyze的描述是：

 

When a database has a severe performance response time, the cause of the problem can be a HANG.
Using HANGANALYZE trace files you can determine whether an Oracle process is blocking other processes,
before starting a performance analysis.

Caution: HANGANALYZE run at levels higher that 3 may generate a huge number of trace files for large systems.
Do not use levels higher than 3 without discussing their effects with Oracle Support.

 

 

使用eygle网站上提供的方式产生一个library cache pin:

 

建立一个空的procedure, pinning :

 

SQL> create or replace procedure pinning
  2  is begin
  3  null;
  4  end;
  5  /

Procedure created.

 

建立一个procedure, calling ,调用pinning 

SQL> create or replace procedure calling
  2  is
  3  begin
  4  pining;
  5  dbms_lock.sleep(600);
  6  end;
  7  /

Procedure created.

执行calling

SQL> exec calling

=>暂停了

 

在另一个session 重建pinning

SQL> create or replace procedure pining
  2  is begin
  3  null;
end;  4
  5  /  ＝》这时候library cache pin就产生了

 

[oracle@dbalinux1 ~]$ dbi sessions

   SID    SERIAL# SPID     USERNAME        STATUS   SQL_HASH_VALUE EVENT
------ ---------- -------- --------------- -------- -------------- -------------
   143       4743 10717    SYS             ACTIVE       3918317640 PL/SQL lock t
   142       2407 10782    SYS             ACTIVE                0 library cache

 

使用oradebug来产生hanganalyze跟踪文件 ，因为测试机是单节点，所以只需这样执行

 

SQL> oradebug hanganalyze 3;
Hang Analysis in /u01/app/oracle/admin/test/udump/test_ora_10875.trc
SQL> oradebug hanganalyze 3;
Hang Analysis in /u01/app/oracle/admin/test/udump/test_ora_10875.trc
SQL> exit

 

对于产生的trace文件就要具体分析了。

 

metalink 上的使用说明：

 

STEPS TO GENERATE A HANGANALYZE TRACE FILE:
========================================
Use the following set of commands to generate HANGANALYZE trace files.


1- Using SQL*Plus connect as "INTERNAL" (Oracle8i) or "/ AS SYSDBA" (Oracle9i)

2- Execute the following commands:
   SQL> oradebug hanganalyze 3
   ... Wait at least 1 minutes to give time to identify process state changes.
   SQL> oradebug hanganalyze 3

3- Open a separate SQL session and immediately generate a system state dump.

           $ sqlplus '/ as sysdba'
          oradebug setmypid
          oradebug unlimit
          oradebug dump systemstate 266
          wait 90 seconds
          oradebug dump systemstate 266
          wait 90 seconds
          oradebug dump systemstate 266
          quit


Note: Starting with Oracle 9.2 HANGANALYZE can generate HANGANALYZE cluster wide.
Using this feature you can generate session dependencies to all the sessions
connected to all the instances of the RAC cluster. Use the following set of commands
to generate a RAC cluster wide HANGANALYZE:

1- Using SQL*Plus connect as "/ AS SYSDBA"
2- Execute the following commands:
   SQL> oradebug setmypid
   SQL> oradebug setinst all
   SQL> oradebug -g def hanganalyze 3
   ... Wait at least 1 minutes to give time to identify process state changes.
   SQL> oradebug -g def hanganalyze 3

 

除此之外oradebug还能做很多事情，还得好好学习一下。

 

参考：Steps to generate HANGANALYZE trace files [ID 175006.1]