Oracle 9i RAC enqueue等待测试

最近客户一个生产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、查找具体的对象信息

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、再查处具体等待的具体行

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]）

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

转载于:http://blog.itpub.net/25834554/viewspace-709260/