Database Reference
In-Depth Information
or else a prolonged deadlock/block hurts overall database performance, it is advisable to perform a
hang analysis
,
which helps greatly in identifying the root cause of the problem. The following set of examples explains how to invoke
and use the
hang analysis
:
SQL> sqlplus / as sysdba
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug setinst all -- enables cluster-wide hang analysis
SQL> oradebug -g all hanganalyze 3 --is the most commonly used level
<< wait for couple of minutes >>
SQL> oradebug -g all hanganalyze 3
The hang analysis levels can be the currently set value between 1 to 5 and 10. When
hanganlyze
is invoked, the
diagnostic information will be written to a dump file under
$ORACLE_BASE/diag/rdbms/dbname/instance_name/trace
,
which can be used to troubleshoot the problem.
We have built the following test case to develop a blocking scenario in a RAC database to demonstrate the
procedure practically. We will then interpret the trace file to understand the contents to troubleshoot the issue. The
following steps were performed as part of the test scenario:
Create an EMP table:
SQL> create table emp (eno number(3),deptno number(2), sal number(9));
Load a few records in the table. From instance 1, execute an
update
statement:
SQL> update emp set sal=sal+100 where eno=101; -- not commit performed
From instance 2, execute an
update
statement for the same record to develop a blocking scenario:
SQL> update emp set sal=sal+200 where eno=101;
At this point, the session on instance 2 is hanging and the cursor doesn't return to the SQL prompt, as expected.
Now, from another session, run the
hang analysis
as follows:
SQL>oradebug setmypid
Statement processed.
SQL >oradebug setinst all
Statement processed.
SQL >oradebug -g all hanganalyze 3 <level 3 is most suitable in many circumstances>
Hang Analysis in /u00/app/oracle/diag/rdbms/rondb/RONDB1/trace/RONDB1_diag_6534.trc
Let's have a walk-through and interpret the contents of the trace file to identify the blocker and holder details in
context. Here is the excerpt from the trace file:
Node id: 1
List of nodes: 0, 1, << nodes (instance) count >>
*** 2012-12-16 17:19:18.630
===============================================================================
HANG ANALYSIS:
instances (db_name.oracle_sid): rondb.rondb2, rondb.rondb1
oradebug_node_dump_level: 3 << hanganlysis level >>
