最近客戶一個生產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]）

Oracle 9i RAC enqueue等待測試

相關文章