产生hanganalyze跟踪文件
<DIV>Metalink的对hanganalyze的描述是：</DIV><DIV> </DIV>
<DIV><FONT face=Arial color=#808080 size=1>When a database has a severe performance response time, the cause of the problem can be a HANG. <BR>Using HANGANALYZE trace files you can determine whether an Oracle process is blocking other processes, <BR>before starting a performance analysis. <BR><BR>Caution: HANGANALYZE run at levels higher that 3 may generate a huge number of trace files for large systems. <BR>Do not use levels higher than 3 without discussing their effects with Oracle Support.</FONT></DIV>
<DIV><FONT face="Courier New"></FONT> </DIV>
<DIV><FONT face="Courier New"></FONT> </DIV>
<DIV>使用eygle网站上提供的方式产生一个library cache pin:</DIV>
<DIV> </DIV>
<DIV>建立一个空的procedure, pinning : </DIV>
<DIV> </DIV>
<DIV><A href="mailto:SYS@test" target=_blank><FONT face=Arial color=#808080 size=1>SYS@test</FONT></A><FONT face=Arial color=#808080 size=1> SQL> create or replace procedure pinning<BR> 2 is begin<BR> 3 null;<BR> 4 end;<BR> 5 /</FONT></DIV>
<DIV><FONT face=Arial color=#808080 size=1>Procedure created.</FONT></DIV>
<DIV> </DIV>
<DIV>建立一个procedure, calling ,调用pinning </DIV>
<DIV><BR><A href="mailto:SYS@test" target=_blank><FONT face=Arial color=#808080 size=1>SYS@test</FONT></A><FONT face=Arial color=#808080 size=1> SQL> create or replace procedure calling<BR> 2 is<BR> 3 begin<BR> 4 pining;<BR> 5 dbms_lock.sleep(600);<BR> 6 end;<BR> 7 /</FONT></DIV>
<DIV><FONT face=Arial color=#808080 size=1>Procedure created.<BR></FONT></DIV>
<DIV>执行calling </DIV>
<DIV><BR><A href="mailto:SYS@test" target=_blank><FONT face=Arial size=1>SYS@test</FONT></A><FONT face=Arial size=1> SQL> exec calling</FONT></DIV>
<DIV><BR><FONT face=Arial size=1>=>暂停了</FONT></DIV>
<DIV> </DIV>
<DIV>在另一个session 重建pinning </DIV>
<DIV><BR><A href="mailto:SYS@test" target=_blank><FONT face=Arial color=#808080 size=1>SYS@test</FONT></A><FONT face=Arial><FONT size=1><FONT color=#808080> SQL> create or replace procedure pining<BR> 2 is begin<BR> 3 null;<BR>end; 4<BR> 5 / ＝</FONT><FONT color=#808080>》这时候library cache pin就产生了</FONT></FONT></FONT> </DIV>
<DIV> </DIV>
<DIV><FONT face=Arial color=#808080 size=1>$ dbi sessions</FONT></DIV>
<DIV><FONT face=Arial color=#808080 size=1> SID SERIAL# SPID USERNAME STATUS SQL_HASH_VALUE EVENT<BR>------ ---------- -------- --------------- -------- -------------- -------------<BR> 143 4743 10717 SYS ACTIVE 3918317640 PL/SQL lock t<BR> 142 2407 10782 SYS ACTIVE 0 library cache<BR></FONT></DIV>
<DIV> </DIV>
<DIV>使用oradebug来产生hanganalyze跟踪文件 ，因为测试机是单节点，所以只需这样执行 </DIV>
<DIV> </DIV>
<DIV><A href="mailto:SYS@test" target=_blank><FONT face=Arial color=#808080 size=1>SYS@test</FONT></A><FONT face=Arial color=#808080 size=1> SQL> oradebug hanganalyze 3;<BR>Hang Analysis in /u01/app/oracle/admin/test/udump/test_ora_10875.trc<BR></FONT><A href="mailto:SYS@test" target=_blank><FONT face=Arial color=#808080 size=1>SYS@test</FONT></A><FONT face=Arial color=#808080 size=1> SQL> oradebug hanganalyze 3;<BR>Hang Analysis in /u01/app/oracle/admin/test/udump/test_ora_10875.trc<BR></FONT><A href="mailto:SYS@test" target=_blank><FONT face=Arial color=#808080 size=1>SYS@test</FONT></A><FONT face=Arial color=#808080 size=1> SQL> exit</FONT></DIV>
<DIV> </DIV>
<DIV>对于产生的trace文件就要具体分析了。</DIV>
<DIV> </DIV>
<DIV>metalink 上的使用说明：</DIV>
<DIV> </DIV>
<DIV><FONT face=Arial color=#808080 size=1>STEPS TO GENERATE A HANGANALYZE TRACE FILE:<BR>======================================== <BR>Use the following set of commands to generate HANGANALYZE trace files.<BR><BR><BR>1- Using SQL*Plus connect as "INTERNAL" (Oracle8i) or "/ AS SYSDBA" (Oracle9i)<BR><BR>2- Execute the following commands:<BR> SQL> oradebug hanganalyze 3 <BR> ... Wait at least 1 minutes to give time to identify process state changes.<BR> SQL> oradebug hanganalyze 3 <BR><BR>3- Open a separate SQL session and immediately generate a system state dump.<BR><BR> $ sqlplus '/ as sysdba'<BR> oradebug setmypid<BR> oradebug unlimit<BR> oradebug dump systemstate 266<BR> wait 90 seconds<BR> oradebug dump systemstate 266<BR> wait 90 seconds<BR> oradebug dump systemstate 266<BR> quit<BR><BR><BR>Note: Starting with Oracle 9.2 HANGANALYZE can generate HANGANALYZE cluster wide.<BR>Using this feature you can generate session dependencies to all the sessions <BR>connected to all the instances of the RAC cluster. Use the following set of commands <BR>to generate a RAC cluster wide HANGANALYZE:<BR><BR>1- Using SQL*Plus connect as "/ AS SYSDBA" <BR>2- Execute the following commands:<BR> SQL> oradebug setmypid <BR> SQL> oradebug setinst all<BR> SQL> oradebug -g def hanganalyze 3<BR> ... Wait at least 1 minutes to give time to identify process state changes.<BR> SQL> oradebug -g def hanganalyze 3<BR></FONT></DIV>
<DIV><FONT face=Arial color=#808080 size=1><BR> </DIV></FONT>
<DIV>除此之外oradebug还能做很多事情，还得好好学习一下。</DIV>
<DIV> </DIV>
<DIV>参考：<STRONG>Steps to generate HANGANALYZE trace files </STRONG></DIV>
<DIV> </DIV>
<DIV><BR> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
页:
[1]