oracle oradebug hanganalyze简单使用

先做一个简单事务锁等待：

窗口1：

窗口2：修改同样的数据

这时候我们会发现窗口2 的语句被卡住了。下面做个oradebug：

窗口3：

根据提示，打开所生成的trace文件，寻找有用的信息。

Chain 1:
-------------------------------------------------------------------------------
    Oracle session identified by:
    {
                instance: 1 (orcl.orcl)
                   os id: 4768
              process id: 21, ORACLE.EXE (SHAD)
            session id: 155
        session serial #: 28
    }
   is waiting for 'enq: TX - row lock contention' with wait info:
    {
                      p1: 'name|mode'=0x54580006
                      p2: 'usn<<16 | slot'=0x7000b
                      p3: 'sequence'=0x9fd
            time in wait: 2 min 2 sec
           timeout after: never
                 wait id: 24
                blocking: 0 sessions
             current sql: update test set id=1111 where id=2
             short stack: <-_ksedsts()+286<-_ksdxfstk()+14<-_ksdxcb()+1780<-_ssthreadsrgruncallback()+432<-_OracleOradebugThreadStart@4()+825<-7C80B726<-00000000<-7C92E514<-7C80253D<-_skgpwwait()+128<-_ksliwat()+1440<-_kslwaitctx()+146<-_ksqcmi()+10381<-_ksqgtlctx()+1980<-_ksqgelctx()+502<-_ktcwit1()+316<-_kdddgb()+2378<-_kdusru()+407<-_updrowFastPath()+2375<-__VInfreq__qerupFetch()+1247<-_updaul()+1314<-_updThreePhaseExe()+272<-_updexe()+373<-_opiexe()+14214<-_kpoal8()+2292<-_opiodr()+1299<-_ttcpip()+2790<-_opitsk()+1278<-_opiino()+1067<-
            wait history:
              1.     event: 'SQL*Net message from client'
                   wait id: 23              p1: 'driver id'=0x42455100
               time waited: 15.706029 sec   p2: '#bytes'=0x1
              2.     event: 'SQL*Net message to client'
                   wait id: 22              p1: 'driver id'=0x42455100
               time waited: 0.000002 sec    p2: '#bytes'=0x1
              3.     event: 'SQL*Net message from client'
                   wait id: 21              p1: 'driver id'=0x42455100
               time waited: 0.000026 sec    p2: '#bytes'=0x1
    }
    and is blocked by
 => Oracle session identified by:
    {
                instance: 1 (orcl.orcl)
                   os id: 4812
              process id: 18, ORACLE.EXE (SHAD)
            session id: 170
        session serial #: 9
    }
   which is waiting for 'SQL*Net message from client' with wait info:
    {
                      p1: 'driver id'=0x42455100
                      p2: '#bytes'=0x1
            time in wait: 2 min 29 sec
           timeout after: never
                 wait id: 38
                blocking: 1 session
             current sql: <none>
             short stack: <-_ksedsts()+286<-_ksdxfstk()+14<-_ksdxcb()+1780<-_ssthreadsrgruncallback()+432<-_OracleOradebugThreadStart@4()+825<-7C80B726<-00000000<-7C92E514<-07F9535B<-07F93160<-06A31A0C<-06A321E1<-0699FF8F<-__PGOSF35__opikndf2()+989<-_opitsk()+584<-_opiino()+1067<-_opiodr()+1299<-_opidrv()+1319<-_sou2o()+45<-_opimai_real()+130<-_opimai()+92<-_OracleThreadStart@4()+792<-7C80B726<-00000000
            wait history:
              1.     event: 'SQL*Net message to client'
                   wait id: 37              p1: 'driver id'=0x42455100
               time waited: 0.000005 sec    p2: '#bytes'=0x1
              2.     event: 'SQL*Net message from client'
                   wait id: 36              p1: 'driver id'=0x42455100
               time waited: 6.980115 sec    p2: '#bytes'=0x1
              3.     event: 'SQL*Net message to client'
                   wait id: 35              p1: 'driver id'=0x42455100
               time waited: 0.000003 sec    p2: '#bytes'=0x1
    }

在trace文件里面可以清晰的看到

sid 155的会话在等待：

enq: TX - row lock contention

该会话被sid 170阻塞，sid 170的会话在等待

SQL*Net message from client

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

其实在实际生产环境中，不像我实验的那么简单，多是数据库内部发生了死锁(争夺内核资源级别，比如pins和latch)，而不是我实验中这种简单的dml锁。

其中hanganalyze也是具有级别的：

1-2:只有hanganalyze输出，不dump任何进程
3：Level2+Dump出在IN_HANG状态的进程
4：Level3+Dump出在等待链里面的blockers(状态为LEAF/LEAF_NW/IGN_DMP)
5：Level4+Dump出所有在等待链中的进程（状态为NLEAF）

oracle官方建议不超过level 3，超过level 3会对系统带来额外的负担，一般level 3即可解决问题，定位到阻塞源头。

对于集群范围的debug，要使用如下命令：

和单实例的区别就是多了两条命令：

oradebug setinst all

oradebug -g def hanganalyze 3;

由于我是单实例环境，报错了。。。。。

---------------------------------------------------------------------------------------------------------------------------------------------------------------

将hanganalyze知识简单整理下，以备后用。