Why hanganalyze diagnoses database hang

Environment: RedHat 6.1 + 11.2.0.3rac

Lab:

Node 1 sessions:

SQL> select userenv ('sid ') from dual;

Userenv ('sid ')
--------------
62

SQL> Create Table Binbin (ID number, name varchar2 (20 ));

Table created.

SQL> insert into Binbin values (1, 'binbinbin ');

1 row created.

SQL> commit;

Commit complete.

SQL> Update Binbin set name = 'hang' where id = 1;

1 row updated.
Node 2 sessions:

SQL> select userenv ('sid ') from dual;

Userenv ('sid ')
--------------
69

 

SQL> Update Binbin set name = 'hang' where id = 1;

Now hang is in place.

 

Node 1 sessions 2 for hanganlyze analysis:

 

The hanganlyze analysis command in the RAC environment is as follows:

SQL> oradebug setmypid
Statement processed.
SQL> oradebug setinst all
Statement processed.
SQL> oradebug-G def hanganalyze 3
Hang Analysis in/u01/APP/Oracle/diag/rdbms/Dave/dave1/Trace/dave1_diag_8273.trc

The hanganlyze analysis command for a single instance environment is as follows:

Oradebug hanganalyze 3 ----- equivalent to alter session set events 'immediate trace name hanganalyze level 3 ';

 

View trace files

More/u01/APP/Oracle/diag/rdbms/Dave/dave1/Trace/dave1_diag_8273.trc

extra information that will be dumped at higher levels:
[Level 4]: 1 node dumps -- [Leaf] [leaf_nw]
[Level 5]: 13 node dumps -- [no_wait] [invol_wt] [single_node] [nleaf] [single_node_nw]

State of all nodes
([Nodenum]/cNode/SID/sess_srno/session/ospid/State/[adjlist]):
[1]/1/2/1/0x91048db8/8261/single_node_nw/
[10]/1/11/1/0x91ff9fa8/8283/single_node_nw/
[11]/1/12/1/0x917fbcf0/8285/single_node_nw/
[26]/1/27/1/0x917cf218/8332/single_node_nw/
[31]/1/32/5/0x917c03d0/8384/single_node_nw/
[34]/1/35/7/0x917b74d8/9573/single_node_nw/
[50]/1/51/15/0 x91787a58/10085/single_node_nw/
[61]/1/62/191/0 x91766e20/20465/leaf/
[72]/1/73/117/0 x917461e8/20861/single_node/
[256]/2/10/1/0x91ffcf50/7872/single_node_nw/
[257]/2/11/1/0x91ff9fa8/7876/single_node_nw/
[258]/2/12/1/0x917fbcf0/7878/single_node_nw/
[303]/2/57/21/0 x91775c68/13052/single_node/
[315]/2/69/15/0x91752088/13344/nleaf/[61]

 

From the trace above, we can see that the session with nodenum 61 and session ID 62 blocks the session with nodenum 315 session ID 69.

[Nodenum]/cNode/SID/sess_srno/session/ospid/State/[adjlist:

Nodenum: defines the serial number of each session.
CNode is the node ID.

Sid: Session Sid
Sess_srno: Session serial #
Ospid: OS process ID
State: node status
Adjlist: Indicates blocker Node
In_hang: This indicates that the node is in a deadlock state, and other nodes (blocker) are also in this state.
Leaf/leaf_nw: this node is usually a blocking operator. Leaf indicates that this node does not wait for other resources, and leaf_nw may be not waiting for other resources or is using CPU
Nleaf: Generally, it can be seen that these sessions are congested resources. This generally indicates that the database has performance problems rather than database hang. adjlist can be seen as a blocking person.
IGN/ign_dmp: This type of session is generally considered as an idle session, unless the node exists in its adjlist column. If the session is not idle, the node in its adjlist is waiting for other nodes to release resources.
Single_node/single_node_nw: similar to idle sessions.

 

[Oracle @ Rac1 ~] $ Kill-9 20465 -- kill Blocking

Node 2 Session:

SQL> select userenv ('sid ') from dual;

Userenv ('sid ')
--------------
69

SQL> Update bnbin set name = 'hang' where id = 1;

 

1 row updated.