当前位置:操作系统 > Unix/Linux >>

Hang易做图yze的使用

Hang易做图yze的使用
 
It is  important to find the that the reason hangs the database.
 
How can we do, is a headache thing. And we can use oracle internal tool to find the cause, which is hang易做图yze.
 
Level of hang易做图yze
 
[sql] 
10   Dump all processes (IGN state)  
5    Level 4 + Dump all processes involved in wait chains (NLEAF state)  
4    Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state)  
3    Level 2 + Dump only processes thought to be in a hang (IN_HANG state)  
1-2  Only HANGANALYZE output, no process dump at all  

How to use hang易做图yze

[sql] 
Single node  
ALTER SESSION SET EVENTS 'immediate trace name HANGANALYZE level <level>';  
ORADEBUG hang易做图yze <level>  
Rac:  
ORADEBUG setmypid  
ORADEBUG setinst all  
ORADEBUG -g def hang易做图yze <level>  
  
  
For example  
  
  
SQL> update test set empno=9999 where empno=7788;  
  
1 row updated.  
  
Another session  
  
SQL> update test set empno=9111 where empno=7788;  
  
  
<pre name="code" class="sql">SQL> oradebug setmypid  
SQL> oradebug hang易做图yze 3  
Hang Analysis in /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_55.trc</pre><br>  
<pre></pre>  
<pre></pre>  
<pre></pre>  
<pre></pre>  


The contents of /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_55.trc


[sql] 
Trace file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_55.trc  
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
With the Partitioning, OLAP, Data Mining and Real Application Testing options  
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1  
System name:    SunOS  
Node name:      Solaris10  
Release:        5.10  
Version:        Generic_142910-17  
Machine:        i86pc  
Instance name: prod  
Redo thread mounted by this instance: 1  
Oracle process number: 33  
Unix process pid: 55, image: oracle@Solaris10 (TNS V1-V3)  
  
  
*** 2013-08-05 03:33:20.007  
*** SESSION ID:(34.21990) 2013-08-05 03:33:20.007  
*** CLIENT ID:() 2013-08-05 03:33:20.007  
*** SERVICE NAME:(SYS$USERS) 2013-08-05 03:33:20.007  
*** MODULE NAME:(sqlplus@Solaris10 (TNS V1-V3)) 2013-08-05 03:33:20.007  
*** ACTION NAME:() 2013-08-05 03:33:20.007  
   
Processing Oradebug command 'hang易做图yze 3'  
  
*** 2013-08-05 03:33:20.303  
===============================================================================  
HANG ANALYSIS:  
  instances (db_name.oracle_sid): prod.prod  
  oradebug_node_dump_level: 3  
  易做图ysis 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: 0xa00e2e87  
   
===============================================================================  
Non-intersecting chains:  
   
-------------------------------------------------------------------------------  
Chain 1:  
-------------------------------------------------------------------------------  
    Oracle session identified by:  
    {  
                instance: 1 (prod.prod)  
                   os id: 29878  
              process id: 32, oracle@Solaris10 (TNS V1-V3)  
              session id: 5  
        session serial #: 20196  
    }  
    is waiting for 'enq: TX - row lock contention' with wait info:  
    {  
                      p1: 'name|mode'=0x54580006  
                      p2: 'usn<<16 | slot'=0x4001e  
                      p3: 'sequence'=0x430  
            time in wait: 1 min 20 sec  
           timeout after: never  
                 wait id: 27  
                blocking: 0 sessions  
             current sql: update test set empno=9111 where empno=7788  
             short stack: ksedsts()+279<-ksdxfstk()+33<-ksdxcb()+928<-sspuser()+127<-__sighndlr()+6<-call_user_handler()+594<-sigacthandler()+238<-_syscall6()+27<-sskgp  
wwait()+293<-ksliwat()+3146<-kslwaitctx()+147<-ksqcmi()+4000<-ksqgtlctx()+2538<-ksqgelctx()+590<-ktcwit1()+377<-kdddgb()+2953<-kdusru()+6214<-updrowFastPath()+1696<-qer  
upFetch()+2551<-updaul()+1265<-updThreePhaseExe()+470<-updexe()+531<-opiexe()+9433<-kpoal8()+4217<-opiodr()+1087<-ttcpip()+1417<-opitsk()+1589<-opiino()+1777<-opiodr()+  
1087<-opidrv()+858<-sou2o()+87<-opimai_re  
            wait history:  
              * time between current wait and wait #1: 0.005578 sec  
              1.       event: 'Disk file operations I/O'  
                 time waited: 0.000045 sec  
                     wait id: 26              p1: 'FileOperation'=0x2  
                                              p2: 'fileno'=0x4  
                                              p3: 'filetype'=0x2  
              * time between wait #1 and #2: 0.005175 sec  
              2.       event: 'SQL*Net message from client'  
                 time waited: 1 min 42 sec  
                     wait id: 25              p1: 'driver id'=0x62657100  
                                              p2: '#bytes'=0x1  
              * time between wait #2 and #3: 0.000002 sec  
              3.       event: 'SQL*Net message to client'  
                 time waited: 0.000002 sec  
                     wait id: 24              p1: 'driver id'=0x62657100  
                                              p2: '#bytes'=0x1  
    }  
    and is blocked by  
 => Oracle session identified by:  
    {  
                instance: 1 (prod.prod)  
                   os id: 29867  
              process id: 35, oracle@Solaris10 (TNS V1-V3)  
              session id: 99  
        session serial #: 4407  
    }  
    which is waiting for 'SQL*Net message from client' with wait info:  
    {  
                      p1: 'driver id'=0x62657100  
                      p2: '#bytes'=0x1  
            time in wait: 1 min 37 sec  
           timeout after: never  
                 wait id: 86  
                blocking: 1 session  
             current sql: <none>  
             short stack: ksedsts()+279<-ksdxfstk()+33<-ksdxcb()+928<-sspuser()+127<-__sighndlr()+6<-call_user_handler()+594<-sigacthandler()+238<-_read()+10<-sntpread(  
)+28<-ntpfprd()+91<-nsbasic_brc()+411<-nioqrc()+718<-opikndf2()+763<-opitsk()+764<-opiino()+1777<-opiodr()+1087<-opidrv()+858<-sou2o()+87<-opimai_real()+541<-ssthrdmain  
()+295<-main()+203<-_start()+108  
            wait history:  
              * time between current wait and wait #1: 0.000009 sec  
              1.       event: 'SQL*Net message to client'  
                 time waited: 0.000002 sec  
                     wait id: 85              p1: 'driver id'=0x62657100  
                                              p2: '#bytes'=0x1  
              * time between wait #1 and #2: 0.001566 sec  
              2.       event: 'db file sequential read'  
                 time waited: 0.000022 sec  
                     wait id: 84              p1: 'file#'=0x4  
                                              p2: 'block#'=0xab  
                                              p3: 'blocks'=0x1  
              * time between wait #2 and #3: 0.004235 sec  
              3.       event: 'SQL*Net message from client'  
                 time waited: 23.537121 sec  
                     wait id
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,