PURPOSE-------------TofindtheTXEnqueuecontentioninaRACorOPSenvironmentWhatisTXEnqueue?Inon
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/，如需转载，请注明出处，否则将追究法律责任。
0
0
分享到：
上一篇：
分析函数的使用一例
下一篇：
安装11gr2遭遇bug
请登录后发表评论
登录
全部评论
<%=items[i].createtime%>
<%=items[i].content%>
<%if(items[i].items.items.length) { %>
<%for(var j=0;j
<%=items[i].items.items[j].createtime%>
回复
<%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>： <%=items[i].items.items[j].content%>
<%}%>
<%if(items[i].items.total > 5) { %>
还有<%=items[i].items.total-5%>条评论) data-count=1 data-flag=true>点击查看
<%}%>
<%}%>
<%}%>
syzxlyx_cu
注册时间：2009-09-28
最新文章
规范流程的重要性总结
深入解析10053事件
安装11gr2遭遇bug
分析函数的使用一例
ORA-12545错误的处理办法
rac进程（转）
REDO INTERNALS AND TUNING BY REDO
: Lists All Indexes that Benefit from a Rebuild
How to check RAC Option is currently linked into the Oracle Binary
关于parse count (failures）的含义
支持我们
作者招募
用户协议
FAQ
Contact Us
北京盛拓优讯信息技术有限公司. 版权所有 京ICP备09055130号-4 北京市***局海淀***网监中心备案编号：11010802021510
广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员
转载于:http://blog.itpub.net/20542911/viewspace-623453/
京公网安备 11010802041100号