產(chǎn)生hanganalyze跟蹤文件

54xw

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網(wǎng)站上提供的方式產(chǎn)生一個library cache pin:

 

建立一個空的procedure, pinning :

 

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

Procedure created.

 

建立一個procedure, calling ,調(diào)用pinning 

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

Procedure created.

執(zhí)行calling

SYS@test SQL> exec calling

=>暫停了

 

在另一個session 重建pinning

SYS@test SQL> create or replace procedure pining
  2  is begin
  3  null;
end;  4
  5  /  ＝》這時候library cache pin就產(chǎn)生了

 

[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來產(chǎn)生hanganalyze跟蹤文件 ，因為測試機是單節(jié)點，所以只需這樣執(zhí)行

 

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

 

對于產(chǎn)生的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還能做很多事情，還得好好學(xué)習(xí)一下。

 

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