If rac database instance is slow due to deadlock or other blocking ,we can use oradebug hang analyzer to deep dive into the root of cause.
Lets troubleshoot with a simulation
I create a sample table for testing purpose
SQL> CREATE TABLE regions
( region_id NUMBER
CONSTRAINT region_id_nn NOT NULL
, region_name VARCHAR2(25)
);
CREATE UNIQUE INDEX reg_id_pk
ON regions (region_id);
ALTER TABLE regions
ADD ( CONSTRAINT reg_id_pk
PRIMARY KEY (region_id)
) ; 2 3 4 5
Table created.
SQL> 2
Index created.
SQL> 2 3 4
Table altered.
I insert rows on node 1 and donot commit the changes
SQL> SQL> SQL> SQL>
SQL>
SQL> INSERT INTO regions VALUES
( 1
, 'Europe'
);
INSERT INTO regions VALUES
( 2
, 'Americas'
);
INSERT INTO regions VALUES
( 3
, 'Asia'
);
INSERT INTO regions VALUES
( 4
, 'Middle East and Africa'
); 2 3 4
1 row created.
SQL> 2 3 4
1 row created.
SQL> 2 3 4
1 row created.
SQL> 2 3 4
1 row created.
At the same time i insert the same rows on the same table with different user but from node 2!
SQL> select name from v$database;
NAME
---------
RACDB
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
RACDB2
SQL> show user
USER is "TEST"
SQL> INSERT INTO sys.regions VALUES
( 1
, 'Europe'
);
INSERT INTO sys.regions VALUES
( 2
, 'Americas'
);
INSERT INTO sys.regions VALUES
( 3
, 'Asia'
);
INSERT INTO sys.regions VALUES
( 4
, 'Middle East and Africa'
); 2 3 4
The database is hung as node 1 blocks node 2 to insert rows
Enable the hang analyzer on one of the RAC nodes to generate trace files for analyzing
SQL> oradebug setmypid
oStatement processed.
SQL> radebug unlimit
oradebugStatement processed.
SQL> oradebug setmypid
SP2-0734: unknown command beginning "oradebugor..." - rest of line ignored.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug setinst all
Statement processed.
SQL> oradebug -g all hanganalyze 3
Hang Analysis in /grid/base/diag/rdbms/racdb/RACDB1/trace/RACDB1_diag_3917.trc
SQL> oradebug -g all hanganalyze 3
Hang Analysis in /grid/base/diag/rdbms/racdb/RACDB1/trace/RACDB1_diag_3917.trc
SQL> oradebug -g all hanganalyze 3
Hang Analysis in /grid/base/diag/rdbms/racdb/RACDB1/trace/RACDB1_diag_3917.trc
In the hang trace file ,we can find the diagnostic details of RAC1 blocking RAC2 with wait event ,blocking id,waiting time etc which is pretty much valuable information!!
Chains most likely to have caused the hang:
[a] Chain 1 Signature: <not in a wait><='rdbms ipc reply'<='enq: TX - row lock contention'
Chain 1 Signature Hash: 0x7ba3a1e1
===============================================================================
Non-intersecting chains:
-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 2 (racdb.racdb2)
os id: 5922
process id: 40, oracle@RAC2 (TNS V1-V3)
session id: 60
session serial #: 67
}
is waiting for 'enq: TX - row lock contention' with wait info:
{
p1: 'name|mode'=0x54580004
p2: 'usn<<16 | slot'=0x6000e
p3: 'sequence'=0x32b
time in wait: 5 min 50 sec
timeout after: never
wait id: 97
blocking: 0 sessions
current sql: INSERT INTO sys.regions VALUES
( 1
, 'Europe'
)
short stack: ksedsts()+265<-ksdxfstk()+19<-ksdxcb()+1646<-sspuser()+100<-semtimedop()+36<-sskgpwwait()+202<-skgpwwait()+138<-ksliwat()+1602<-kslwaitctx()+138<-kjusuc()+6900<-ksipgetctxi()+1641<-ksqcmi()+23174<-ksqgtlctx()+3204<-ksqgelctx()+469<-ktuGetTxForXid()+95<-ktcwit1()+272<-ktbgtl0()+958<-kdiins0()+46577<-kdiinsp()+61<-kauxsin()+1634<-qesltcLoadIndexList()+718<-qesltcLoadIndexes()+43<-qerltcSimpleSingleInsRowCBK()+65<-qerltcSingleRowLoad()+228<-qerltcFetch()+301<-insexe()+567<-opiexe()+4953<-kpoal8()+1811<-opiodr()+962<-
wait history:
* time between current wait and wait #1: 0.002054 sec
1. event: 'SQL*Net message from client'
time waited: 26.277790 sec
wait id: 96 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
* time between wait #1 and #2: 0.000002 sec
2. event: 'SQL*Net message to client'
time waited: 0.000001 sec
wait id: 95 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
* time between wait #2 and #3: 0.000031 sec
3. event: 'SQL*Net message from client'
time waited: 0.000146 sec
wait id: 94 p1: 'driver id'=0x62657100
p2: '#bytes'=0x1
}
and is blocked by
=> Oracle session identified by:
{
instance: 1 (racdb.racdb1)
os id: 11456
oradebug is the advisable tool in times of performance issues!!
Until the session 1 in node 1 releases the lock from the rows in the table,the other session couldnot modify the rows in the same table.So if the user doesnot commit and flush the block changes to the disk ,other sessions have to sit idle and wait in the queue!!
