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 sqlplus@opcbsol 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 sqlplus@opcbsol 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 sqlplus@opcbsol 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 .
where rowid like DBMS_ROWID.ROWID_CREATE(1,&Object_No,&Rfile_No, &Block_No, &Row_Number) / From query 3 and 4 we will get the value for all variables. Owner = AKDAS Table_Name = AKDAS Object_No = 7261 Rfile_No = 9 Block_No = 12346 Row_Number = 1
Output Will be here =============== A1 Col1 Col2 ---------- --------------- ---------- 6 Hello Hi
So we can drag down to the row value where TX Enqueue contention exists.
6. Let’s find the user activity that is "Holder" and "Waiter" ====================================================
set linesize 120 set pagesize 66 col c0 for 999 col c0 heading "INS" col c1 for a9 col c1 heading "OS User" col c2 for a9 col c2 heading "Oracle User" col c3 for a15 col c3 heading "Program Name" col b1 for a9 col b1 heading "Unix PID" col b2 for 9999 justify left col b2 heading "ORA SID" col b3 for 999999 justify left col b3 heading "SERIAL#" col sql_text for a45 set space 1 break on b1 nodup on c0 nodup on c3 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 2 select a.inst_id c0,b.sid b2,c.spid b1, b.program c3, b.username c2,b.serial# b3, a.sql_text from gv$sql a, gv$session b, gv$process c where a.address = b.sql_address and b.paddr = c.addr and a.hash_value = b.sql_hash_value and a.inst_id=b.inst_id and a.inst_id=c.inst_id and a.inst_id like '&inst_id' and b.sid like '&sid' order by c.spid,a.hash_value /
This query asks the Instance Number and Sid number, which you can get from step 1 and 2. But remember , you can see the waiter activity, but you may not see the holder activity. Reason is, the holder is sitting idle after doing the DML operation. So SQL for Holder should not be seen under gv$sql.
This all query can be run for single instance database, but all GV$ view need to replace to V$ and there is no INST_ID for V$ View, that part need to be taken care.
