Help Interpreting a HANGANALYZE trace file
Hi Friends
Its my first time on HANGANALYZE trace files. I need some tips. I searched on the forum but no thread matched my doubt
I'm running Oracle 10.2.0.1.0 on Windows 2003 server x64. Just because i'm investigating some instance hangs. During the hangs, we see no blockers, just sometime a row lock contention. But sometimes.
We have 4 or 5 hangs during the day. AWR reports very high "latch library cache" and "latch: shared pool", "latche free" and "latch: cache buffers chains". During the period of hang, these are 4 of the Top 5 Timed Events.
So I could generate a HANGANALYZE trace file since I could connect on the sql plus to see the primary blocker.
SQL> oradebug setmypid
SQL> oradebug hanganalyze 3;
Hang Analysis in c:\oracle\product\10.2.0\admin\orcl2000\udump\oraprod_ora_50.trc
SQL> oradebug hanganalyze 3;
Hang Analysis in c:\oracle\product\10.2.0\admin\orcl2000\udump\oraprod_ora_50.trc
Here is the output I would like help on interpreting
==============
HANG ANALYSIS:
==============
Open chains found:
Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/1408/38922/0x3a63e638/6128/No Wait> -- <0/1547/18677/0x3a627730/6556/latch: library cache>
Chain 2 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/1510/64865/0x3e60cae8/1264/No Wait> -- <0/1412/11994/0x39634db8/4552/enq: TX - row lock contention>
Other chains found:
Chain 3 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/1308/44023/0x3e5fe688/2720/No Wait>
Chain 4 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/1316/41853/0x3e6124a0/4148/No Wait>
Chain 5 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
... and so on....
Extra information that will be dumped at higher levels:
[level 4] : 2 node dumps -- [REMOTE_WT] [LEAF] [LEAF_NW]
[level 5] : 18 node dumps -- [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP]
[level 6] : 2 node dumps -- [NLEAF]
[level 10] : 230 node dumps -- [IGN]
State of nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):
[1284]/0/1285/16090/0x3e8fd3f0/5328/IGN/1/2//none
[1285]/0/1286/48916/0x3994b358/3432/IGN/3/4//none
[1286]/0/1287/21534/0x3a946c38/5836/IGN/5/6//none
[1288]/0/1289/35409/0x3994c790/4252/IGN/7/8//none
[1293]/0/1294/13836/0x3e901098/6176/IGN/9/10//none
[1296]/0/1297/1361/0x3e9024d0/5176/IGN/11/12//none
[1298]/0/1299/10760/0x3a94bd18/6028/IGN/13/14//none
[1300]/0/1301/19757/0x39951870/5912/IGN/15/16//none
[1305]/0/1306/29005/0x3e906178/5880/IGN/17/18//none
[1306]/0/1307/53304/0x399540e0/5900/IGN/19/20//none
... and so on...
My doubt is.
The Chain1 might have blocked Chain2 on a library cache latch event. Perfect.
How to track down the sql id that held the lock to work on?
tks a lot
Edited by: KeenOnOracle on May 21, 2013 2:49 PM