Reference: Dealing with hang situation

When I opened a SR with Oracle about a slow performance problem due to blocking sessions, the support guy gave me an action plan about how to deal with hanging sql. It makes no sense to implement this action plan since problem has already gone. So I blog these commands here for furture reference. Hopefully when needed I can quickly find these commands.


ACTION PLAN
============
When the SQL appears to be hanging, Please generate system state and hanganalyze (should do hanganlyze first?):

$ sqlplus /nolog
connect / as sysdba
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 266
wait 90 seconds
oradebug dump systemstate 266
wait 90 seconds
oradebug dump systemstate 266

In another session, generate hanganalyze:

1-Using SQL*Plus connect as "/ AS SYSDBA"
2- Execute: oradebug hanganalyze 3
3- Wait 1 minute....
4- Execute: oradebug hanganalyze 3


Feb 5, 2010 Updated:

Kyle Hailey mentioned: 

If you have such an instance hang you can use a preliminary connection (which starts the process and attaches to SGA, but doesn't initialize SGA structs nor allocate any state objects):

sqlplus -prelim "/as sysdba"
oradebug dump latches 1
oradebug dump hanganalyze 4

Anup Nanda described a real case hung scenario here.

Related MOS notes:

1. Interpreting HANGANALYZE trace files to diagnose hanging and performance problems [ID 215858.1]
2. Steps to generate HANGANALYZE trace files [ID 175006.1]
3. Systemstate dump when connection to the instance is not possible [ID 359536.1]