最近客户一个生产9I RAC数据库时而出现enqueue等待事件，通过监控数据库中的enqueue等待，并且抓取了holder和waiter的所运行的SQL语句，提交至研发中心，通过近几日的监控再没有发现严重的enqueue等待事件了，看来是应用开发中心是做了调整了。
为了较为深入的了解9i RAC中的enqueue等待事件，因此做了如下一个小测试：
在数据库中创建表T1
SQL> create
table t1
2
(
3
id number,
4
col1 varchar2(20),
5
col2 varchar2(20)
6
);
Table created.
插入几行记录
SQL> insert into
t1 values (1,'china','beijing');
1 row created.
SQL> insert into t1 values (2,'england','london');
1 row created.
SQL> insert into t1 values (3,'america','washington');
1 row created.
SQL> insert into t1 values (4,'japan','tokyo001');
1 row created.
SQL> insert into t1 values (5,'vietnam','henei001');
1 row created.
SQL> commit;
开始更新数据
通过sqlplus连接到节点1
SQL> conn
oraht/oraht@xjdxkfdb1
已连接。
SQL> update t1 set col2='tokyo01' where id=4;
已更新
1 行。
注意，不要commit
通过sqlplus再连接到节点2
SQL> conn
oraht/oraht@xjdxkfdb2
已连接。
SQL> update t1 set col2='tokyo' where id=4;
发现语句hang被hang了
在数据库的节点2上查询当前数据库中的等待事件
SQL> select
event,count(*) from v$session_wait group by event;
EVENT COUNT(*)
------------------------------ ----------
SQL*Net message to client 1
buffer busy waits 1
enqueue 1
gcs remote message 4
ges remote message 1
global cache cr request 2
ksu process alloc latch yield 1
pmon timer 1
rdbms ipc message 9
smon timer 1
wakeup time manager 1
11 rows selected.
在节点2上发现已经存在了enqueue等待事件，现在通过几个sql诊断一下
1、Run the following query to track down the problem:Who is waiting
SQL> set
linesize 100
SQL> set pagesize 66
SQL> col c1 for a15
SQL> col c1 heading "Program Name "
SQL> select l.inst_id,l.SID,program 1,l.TYPE,l.ID1,l.ID2,l.LMODE,l.REQUEST
2
from gv$lock l,gv$session s
3
where l.type like 'TX' and l.REQUEST =6
4
and l.inst_id=s.inst_id and l.sid=s.sid
5
order by id1
6
/
INST_ID SID Program Name TY
ID1 ID2 LMODE
REQUEST
---------- ---------- --------------- -- ---------- ---------- ----------
----------
2 41 sqlplus.exe TX
65579 42531 0 6
根据结果可以知道，实例2上的会话41正在执行DML语句，且锁的请求模式为6。
2、Run the next query to find who is holding
SQL> set
linesize 100
SQL> set pagesize 66
SQL> col c1 for a15
SQL> col c1 heading "Program Name "
SQL> select l.inst_id,l.SID,program c1,l.TYPE,l.ID1,l.ID2,l.LMODE,l.REQUEST
2 from gv$lock l,gv$session s
3 where l.type like 'TX' and l.LMODE =6
and (l.ID1,l.ID2) in
4 (select id1,id2 from gv$lock where
type like 'TX' and REQUEST =6)
5 and l.inst_id=s.inst_id and
l.sid=s.sid
6 order by id1
7 /
INST_ID SID Program Name TY
ID1 ID2
LMODE REQUEST
---------- ---------- --------------- -- ---------- ---------- ----------
----------
1 34 sqlplus.exe TX
65579 42531 6 0
这里，可以知道，实例1上的会话34持有了TX锁，且lmode=6。
3、Find out the exact file#,block# and record# where it is waiting
SQL> set
linesize 110
SQL> col c0 for 999
SQL> col c0 heading "INS"
SQL> col c1 for a15
SQL> col c1 heading "Program Name "
SQL> select inst_id c0,sid,program c1,ROW_WAIT_OBJ# object_no,
ROW_WAIT_FILE# Rfile_no,
2
ROW_WAIT_BLOCK# Block_no ,ROW_WAIT_ROW# Row_no
3
from gv$session
4
where (inst_id,sid) in (select inst_id,sid from gv$session_wait where
p1='1415053318')
5
/
INS SID Program Name OBJECT_NO
RFILE_NO BLOCK_NO ROW_NO
---- ---------- --------------- ---------- ---------- ---------- ----------
2
41 sqlplus.exe 45593 4
17636 6
现在可以实例2上的会话41正在等待的对象的object#,file#,block#等。
4、Find the object details
SQL> set
linesize 100
SQL> set pagesize 100
SQL> col owner for a10
SQL> col object_name for a20
SQL> col object_type for a10
SQL> select owner,object_name,object_id,object_type
2
from dba_objects
3
where
4
object_id in (select ROW_WAIT_OBJ# from gv$session
5
where (inst_id, sid) in (select inst_id,sid from gv$session_wait where
p1='1415053318'))
6
/
OWNER OBJECT_NAME OBJECT_ID OBJECT_TYP
---------- -------------------- ---------- ----------
ORAHT T1 45593 TABLE
5、find the row value details
SQL> select *
from oraht.t1 where rowid like
2
DBMS_ROWID.ROWID_CREATE(1,&Object_No,&Rfile_No, &Block_No,
&Row_Number)
3
/
Enter value for object_no: 45593
Enter value for rfile_no: 4
Enter value for block_no: 17636
Enter value for row_number: 6
old 2:
DBMS_ROWID.ROWID_CREATE(1,&Object_No,&Rfile_No, &Block_No,
&Row_Number)
new 2:
DBMS_ROWID.ROWID_CREATE(1,45593,4, 17636, 6)
ID COL1 COL2
---------- -------------------- --------------------
4 japan tokyo001
本文涉及的SQL语句源于MOS（How to Find TX Enqueue Contention in RAC or OPS [ID 179582.1]）
[@more@]来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25834554/viewspace-1055743/ ，如需转载，请注明出处，否则将追究法律责任。
