Oracle Oradebug hanganalyze Simple to use ____oracle

First do a simple transaction lock wait:

Window 1:

Window 2: Modifying the same data

At this point we will find that the statement of window 2 is jammed. Let's do a oradebug:

Window 3:

Follow the prompts to open the generated trace file to find useful information.

Chain 1:-------------------------------------------------------------------------------Oracle Session identified By: {instance:1 (ORCL.ORCL) OS id:4768 process id:21, ORACLE. EXE (Shad) session id:155 session Serial #: ' Are 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 Wait id:24 blocking:0 Sessions current sql:update test set id=1111 where I d=2 Short Stack: <-_ksedsts () +286<-_ksdxfstk () +14&LT;-_KSDXCB () +1780<-_ssthreadsrgruncallback () +432& Lt;-_oracleoradebugthreadstart@4 () +825<-7c80b726<-00000000<-7c92e514<-7c80253d<-_skgpwwait () + 128<-_ksliwat () +1440<-_kslwaitctx () +146&LT;-_KSQCMI () +10381<-_ksqgtlctx () +1980<-_ksqgelctx () +502<-_ktcwit1 () +316&LT;-_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 t     IME 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 be blocked by => Oracle Session identified by: {instance:1 (ORCL.ORCL) OS id:4812 process Id:18, ORACLE. EXE (Shad) session id:170 Sessions Serial #: 9} which is waiting to ' sql*net message from CLI
            Ent ' with wait info: {p1: ' driver id ' =0x42455100 p2: ' #bytes ' =0x1 Time in Wait:2 min sec timeout after:never wait id:38 blocking:1 s Ession Current sql: <none> Short Stack: <-_ksedsts () +286&LT;-_KSDXFSTK () +14&LT;-_KSDXCB () +1780<-_ssthreadsrgruncallback () +432<-_oracleoradebugthreadstart@4 () +825<-7c80b726<-00000000< -7C92E514&LT;-07F9535B&LT;-07F93160&LT;-06A31A0C&LT;-06A321E1&LT;-0699FF8F&LT;-__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 ' =0x424     55100 Time waited:6.980115 sec P2: ' #bytes ' =0x1 3. Event: ' Sql*net message to client ' wait id:35 p1: ' driver id ' =0x42455100 t
 IME waited:0.000003 sec P2: ' #bytes ' =0x1}

In the trace file, you can see clearly

The session of SID 155 is waiting:

Enq:tx-row Lock contention

The session was blocked by Sid 170 and the SID 170 session was waiting

Sql*net Message from Client

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

In fact, in the actual production environment, not as simple as I experimented with, is the internal deadlock in the database (contention for kernel resource levels, such as pins and latch), rather than the simple DML lock in my experiment.

Where Hanganalyze is also of a level:

1-2: Only hanganalyze output, no dump any process
3:level2+dump the process out of the In_hang state
4:level3+dump the blockers in the waiting chain (the state is leaf/leaf_nw/ign_dmp)
5:level4+dump all processes in the wait chain (status Nleaf)

Oracle's official recommendation of no more than level 3, exceeding Level 3 will bring additional burden on the system, general level 3 can solve the problem, positioning to blocking the source.

For cluster-wide debug, use the following command:

The difference between the single instance and the singleton is two more commands:

Oradebug setinst All

oradebug-g def hanganalyze 3;

Because I am a single instance environment, the error ...

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

The Hanganalyze knowledge is simply sorted out for later use.