Oracle easily handles DML locks. There will be a certain situation when the database will be in the hung mode and at that particular time to detect the proper blocking and waiting for sessions. Oracle has provided with Hanganalyze utility which will help in collecting Lite Onboard Monitor (LTOM) across the server, which will provide detailed information about network, disks, RAM and CPU.
Oracle support usually asks to collect Hanganalyze and Systemstat dump from the system. Both Hanganalyze and Systemstate dump can be collected using SQL* plus utility. Rather than giving more theoretical knowledge, I will keep it more practical. It will help you to understand how to collect it both across RAC and standalone databases.
Check the screenshot below for the level and description of Hanganalyze.
To generate it across RAC, detailed step by step.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug setinst all;
Statement processed.
SQL> oradebug -g all dump systemstate 258;
Statement processed.
SQL>oradebug all hanganalyze 4;
Hang Analysis in /usr/rdbms/test/test1/trace/test1_ora_43219.trc
SQL>oradebug tracefile_name;
/usr/rdbms/test/test1/trace/test1_ora_43219.trc
SQL>exit
To generate it on standalone database, detailed step by step.
SQL>oradebug setmypid;
Statement processed.
SQL>oradebug unlimit;
Statement processed.
SQL>oradebug hanganalyze 4;
Hang Analysis in /usr/rdbms/test/test1/trace/test1_ora_56429.trc
SQL>exit
SQL>oradebug setmypid;
Statement processed.
SQL>oradebug dump systemstate 258;
Statement processed.
SQL>oradebug tracefile_name;
/usr/rdbms/test/test1/trace/test1_ora_56623.trc
SQL>exit
Remmember it will generate huge trace files. Certain scenarious you will not be able to invoke SQL* plus, at that time you need to connect with sqlplus -prelim. Happy Troubleshooting!.
