HanganalyzeThis is a featured page

1.A Hanganalyze

Oracle solves automatically several dead locks types, but some kind of hangs requires an additional help.
Then you can use hanganalyze to get the hangs status, and system state to get all the additional information you could need to complete the information you got through hanganalyze.
You can use
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 3';
or
ORADEBUG HANGANALYZE 3
The strategy of hanganalyze depends on the situation, to detect and differentiate the real hangs from aparently hangs.
You can get one wait a pair of minutes and get other again. Or you can get several hanganalyzes every 30 seconds, etc.
The system state dump you get finally is to gather a complete system state information, to find additional information for you or for support to get more information.
More information on:
Metalink Note 215858.1: Interpreting HANGANALYZE trace files to diagnose hanging and performance problems.
Metalink Note 175006.1 : Steps to generate HANGANALYZE trace files

Oracle 10g has in the web database console an option to monitor hangs and to solve them.

For example from sqlplus:
This hang was caused for using several dblinks on procedures on an import, this was solved setting COMPILE=N in the import process. Usually this kind of dumps are more useful for support than for you, because if they are related to a bug, frequently there is few what you can do.

SQL> CONN SYS @SERPG AS SYSDBA
Conectado.
SQL> ORADEBUG HANGANALYZE 3
Hang Analysis in e:\oraserpg\trace\user\serpg_ora_3272.trc
Wait at least 2 minutes to give time to identify process state changes.
SQL> ORADEBUG HANGANALYZE 3
Hang Analysis in e:\oraserpg\trace\user\serpg_ora_3272.trc
SQL> alter session set events 'immediate trace name SYSTEMSTATE level 10';
Then you get the following information:
Dump file e:\oraserpg\trace\user\serpg_ora_3272.trc
Mon Jan 30 15:12:20 2006
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Release 10.2.0.1.0 - Production
Windows 2000 Version V5.0 Service Pack 4
CPU : 1 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:19M/479M, Ph+PgF:1359M/4902M, VA:1030M/2047M
Instance name: serpg

Redo thread mounted by this instance: 1

Oracle process number: 16

Windows thread id: 3272, image: ORACLE.EXE (SHAD)


*** SERVICE NAME:(SERPG) 2006-01-30 15:12:20.852
*** SESSION ID:(96.294) 2006-01-30 15:12:20.852
*** 2006-01-30 15:12:20.852
==============
HANG ANALYSIS:
==============
Open chains found:
Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/93/15/0x3bdcd568/3296/SQL*Net message from dblink>
-- <0/83/60052/0x3bdce738/7788/library cache lock>
Other chains found:
Chain 2 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/80/431/0x3bdcdb58/11392/jobq slave wait>
Chain 3 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/88/1/0x3bdcf318/3320/Streams AQ: qmn slave idle wait>
Chain 4 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/89/2/0x3bdced28/2944/Streams AQ: waiting for time man>
Chain 5 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/96/294/0x3bdccf78/3272/No Wait>
Chain 6 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/100/1/0x3bdcc988/3300/Streams AQ: qmn coordinator idle>
Extra information that will be dumped at higher levels:
[level 4] : 1 node dumps -- [REMOTE_WT] [LEAF] [LEAF_NW]
[level 5] : 5 node dumps -- [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP]
[level 6] : 1 node dumps -- [NLEAF]
[level 10] : 15 node dumps -- [IGN]

State of nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):
[79]/0/80/431/0x3be5002c/11392/SINGLE_NODE/1/2//none
[82]/0/83/60052/0x3be53884/7788/NLEAF/3/6/[92]/none
[83]/0/84/28353/0x3be54b4c/7628/IGN/7/8//none
[84]/0/85/118/0x3be55e14/7828/IGN/9/10//none
[87]/0/88/1/0x3be5966c/3320/SINGLE_NODE/11/12//none
[88]/0/89/2/0x3be5a934/2944/SINGLE_NODE/13/14//none
[92]/0/93/15/0x3be5f454/3296/LEAF/4/5//82
[93]/0/94/310/0x3be6071c/7820/IGN/15/16//none
[95]/0/96/294/0x3be62cac/3272/SINGLE_NODE_NW/17/18//none
[96]/0/97/11068/0x3be63f74/7804/IGN/19/20//none
[99]/0/100/1/0x3be677cc/3300/SINGLE_NODE/21/22//none
[104]/0/105/1/0x3be6d5b4/3356/IGN/23/24//none
[105]/0/106/3/0x3be6e87c/3288/IGN/25/26//none
[106]/0/107/1/0x3be6fb44/3280/IGN/27/28//none
[107]/0/108/1/0x3be70e0c/3284/IGN/29/30//none
[108]/0/109/3/0x3be720d4/3244/IGN/31/32//none
[109]/0/110/1/0x3be7339c/3316/IGN/33/34//none
[110]/0/111/5/0x3be74664/3252/IGN/35/36//none
[111]/0/112/1/0x3be7592c/3080/IGN/37/38//none
[112]/0/113/1/0x3be76bf4/3032/IGN/39/40//none
[113]/0/114/1/0x3be77ebc/3312/IGN/41/42//none
[114]/0/115/1/0x3be79184/3240/IGN/43/44//none
====================
END OF HANG ANALYSIS
====================
*** 2006-01-30 15:18:38.913
==============
HANG ANALYSIS:
==============
Open chains found:
Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/93/15/0x3bdcd568/3296/SQL*Net message from dblink>
-- <0/83/60052/0x3bdce738/7788/library cache lock>
Other chains found:
Chain 2 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/80/477/0x3bdcdb58/11732/jobq slave wait>
Chain 3 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/88/1/0x3bdcf318/3320/Streams AQ: qmn slave idle wait>
Chain 4 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/89/2/0x3bdced28/2944/Streams AQ: waiting for time man>
Chain 5 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/96/294/0x3bdccf78/3272/No Wait>
Chain 6 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
<0/100/1/0x3bdcc988/3300/Streams AQ: qmn coordinator idle>
Extra information that will be dumped at higher levels:
[level 4] : 1 node dumps -- [REMOTE_WT] [LEAF] [LEAF_NW]
[level 5] : 5 node dumps -- [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP]
[level 6] : 1 node dumps -- [NLEAF]
[level 10] : 15 node dumps -- [IGN]

State of nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):
[79]/0/80/477/0x3be5002c/11732/SINGLE_NODE/1/2//none
[82]/0/83/60052/0x3be53884/7788/NLEAF/3/6/[92]/none
[83]/0/84/28353/0x3be54b4c/7628/IGN/7/8//none
[84]/0/85/118/0x3be55e14/7828/IGN/9/10//none
[87]/0/88/1/0x3be5966c/3320/SINGLE_NODE/11/12//none
[88]/0/89/2/0x3be5a934/2944/SINGLE_NODE/13/14//none
[92]/0/93/15/0x3be5f454/3296/LEAF/4/5//82
[93]/0/94/310/0x3be6071c/7820/IGN/15/16//none
[95]/0/96/294/0x3be62cac/3272/SINGLE_NODE_NW/17/18//none
[96]/0/97/11068/0x3be63f74/7804/IGN/19/20//none
[99]/0/100/1/0x3be677cc/3300/SINGLE_NODE/21/22//none
[104]/0/105/1/0x3be6d5b4/3356/IGN/23/24//none
[105]/0/106/3/0x3be6e87c/3288/IGN/25/26//none
[106]/0/107/1/0x3be6fb44/3280/IGN/27/28//none
[107]/0/108/1/0x3be70e0c/3284/IGN/29/30//none
[108]/0/109/3/0x3be720d4/3244/IGN/31/32//none
[109]/0/110/1/0x3be7339c/3316/IGN/33/34//none
[110]/0/111/5/0x3be74664/3252/IGN/35/36//none
[111]/0/112/1/0x3be7592c/3080/IGN/37/38//none
[112]/0/113/1/0x3be76bf4/3032/IGN/39/40//none
[113]/0/114/1/0x3be77ebc/3312/IGN/41/42//none
[114]/0/115/1/0x3be79184/3240/IGN/43/44//none
====================
END OF HANG ANALYSIS
====================
*** 2006-01-30 15:19:41.551
===================================================
SYSTEM STATE
------------
System global information:
processes: base 3BDBD5C8, size 100, cleanup 3BDC8248
allocation: free sessions 3BDF32A4, free calls 00000000
control alloc errors: 0 (process), 0 (session), 0 (call)
PMON latch cleanup depth: 0
seconds since PMON's last scan for dead processes: 17
system statistics:
3142 logons cumulative
. . . . . .


No user avatar
juancarlosreyesp 		Latest page update: made by juancarlosreyesp , Sep 30 2008, 9:55 PM EDT (about this update About This Update juancarlosreyesp Edited by juancarlosreyesp

718 words added

view changes
- complete history)
Keyword tags: None
More Info: links to this page

Threads for this page

There are no threads for this page.  Be the first to start a new thread.