how To find the TX Enqueue contention in a RAC or OPS

PURPOSE
-------------
To find the TX Enqueue contention in a RAC or OPS environment

What is TX Enqueue ?
In one word oracle is maintaining queue for transaction.

How Many Resources ?
1/ active transaction

How Many Locks?
1/transaction + 1/process waiting for a locked row by that
transaction.

How Many Users?
1 + 1/ process waiting for something locked by this transaction.

Who Uses?
All processes

What need to investigate?
The mode of TX (6/4), Holding/Waiting/Requesting

SCOPE & APPLICATION
=====================

This document will help to analyze the application design related to transaction bottlenecks
and database performance tuning.

Let start with an example:
===================
create table akdas (A1 number, Col1 Varchar2(10), Col2 Varchar2(10));
insert into akdas values(5,'Hello','Hi');
insert into akdas values(6,'Sudip','Datta');
insert into akdas values(7,'Preetam','Roy');
insert into akdas values(8,'Michael','Polaski');

From Node 1:
==========
update akdas set a1=11 where a1=6;

From Node 2:
==========
update akdas set a1=12 where a1=7;
update akdas set a1=11 where a1=6; /* this will wait for Node1: to complete the transaction */

This Note Is Made To Analyzing Only the TX-Mode-6 (Exclusive).

1. Now run the following query to track down the problem: Who is waiting
===================================================================
prompt
prompt Query 1. Waiting for TX Enqueue where mode is Exclusive
prompt =====================================
prompt
set linesize 100
set pagesize 66
col c1 for a15
col c1 heading "Program Name "
select l.inst_id,l.SID,program c1,l.TYPE,l.ID1,l.ID2,l.LMODE,l.REQUEST
from gv$lock l,gv$session s
where l.type like 'TX' and l.REQUEST =6
and l.inst_id=s.inst_id and l.sid=s.sid
order by id1
/

Output will be here
===============
   INST_ID      SID     Program Name       TY     ID1     ID2       LMODE      REQUEST
----------- ---------- ------------------ ---   -------- -------- ---------- --------
         2           13 [email protected]   TX     393236 780       0          6
                         2 (TNS V1-V3)

It is clear that SID 12 of instance 2 is doing a DML and waiting on REQUEST Mode 6.

2. Let's run the next query to find who is holding
===========================================

prompt
prompt
prompt Query 2. Holding for TX Enqueue where mode greater than 6
prompt =======================================
prompt
set linesize 100
set pagesize 66
col c1 for a15
col c1 heading "Program Name "
select l.inst_id,l.SID,program c1,l.TYPE,l.ID1,l.ID2,l.LMODE,l.REQUEST
from gv$lock l,gv$session s
where l.type like 'TX' and l.LMODE =6 and (l.ID1,l.ID2) in
(select id1,id2 from gv$lock where type like 'TX' and REQUEST =6)
and l.inst_id=s.inst_id and l.sid=s.sid
order by id1
/

Output will be here
===============
   INST_ID      SID     Program Name      TY        ID1        ID2      LMODE    REQUEST
   ---------- ---------- -------------- ---   ---------- --------   ----------- --------
         1          12    [email protected] TX     393236        780      6          0
                          1 (TNS V1-V3)

So holder is SID 12 on instance 1. Where LMODE = 6.

3. Let's find out the exact file#, block# and Record# where it is waiting
===============================================================

prompt
prompt
prompt Query 3. Object# ,File#, Block# and Slot# TX Enqueue in detail
prompt ========================================
prompt
set linesize 110
col c0 for 999
col c0 heading "INS"
col c1 for a15
col c1 heading "Program Name "
select inst_id c0,sid,program c1,ROW_WAIT_OBJ# object_no, ROW_WAIT_FILE# Rfile_no,
ROW_WAIT_BLOCK# Block_no ,ROW_WAIT_ROW# Row_no
from gv$session
where (inst_id,sid) in (select inst_id,sid from gv$session_wait where p1='1415053318')
/

Output Will be here
===============
INS     SID    Program Name     OBJECT_NO RFILE_NO BLOCK_NO ROW_NO
----- ---------- -------------   ---------------    --------- -------
   2         13     [email protected] 7261      9        12346     1
                      2 (TNS V1-V3)

From the output, it is clear that it is waiting on Relative_File# 9, Block# 12346, Row Number 1.
Here Row Number 1 means the slot number in the block 12346. This Row_No start from 0 (zero).

4. Let's Find the object details
=============================

prompt
prompt
prompt Query 4. Object Involve for TX Enqueue in detail
prompt ===============================
prompt
set linesize 100
set pagesize 100
col owner for a10
col object_name for a20
col object_type for a10
select owner,object_name,object_id,object_type
from dba_objects
where
object_id in (select ROW_WAIT_OBJ# from gv$session
where (inst_id, sid) in (select inst_id,sid from gv$session_wait where p1='1415053318'))
/

Output Will be here
===============
OWNER      OBJECT_NAME OBJECT_ID   OBJECT_TYP
--------- ------------ --------    -----------
AKDAS      AKDAS        7261        TABLE

5. Let’s find the row value details
=============================

prompt
prompt
prompt Query 5. Finding the row value
prompt ====================
prompt
select * from .

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20542911/viewspace-623453/，如需转载，请注明出处，否则将追究法律责任。