热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

howTofindtheTXEnqueuecontentioninaRACorOPS

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




  • 博文量

    125


  • 访问量

    125526


最新文章



  • 规范流程的重要性总结



  • 深入解析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/



推荐阅读
author-avatar
xh7212176
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有