DFS lock handle事件處理

環境: Oracle 11.2.0.1 RAC AXI 6.3
情況:工具通過INSRT INTO TAB1@DB_LINK SELECT * FROM TAB_B往插入資料時，HAND住，會話事件為DFS lock handle，DB_LINK為本地例項，且TAB2表中有clob欄位。

1.在使用程式匯入深圳資料時,會話hang,以下sql模擬出當時hang住情形.
 select sid from v$mystat where rownum =1; --確認會話sid
 insert into tab1@db_link select * from tab2; --產生迴環問題sql 

2.確認產生DFS lock handle事件
select sid,username,event from v$sessoin where sid =&sid;

3.檢視會話請求鎖的name和mode
select chr(bitand(p1,-16777216)/16777215) || chr(bitand(p1, 16711680)/65535) "Lock",
to_char(bitand(p1, 65536)) "Mode",
p2, p3 , seconds_in_wait
from v$session_wait
where event = ‘DFS lock handle’;

--發現是name :DX   mode:5
 
4.通過鎖資源檢視是否被其他會話佔用

select inst_id, sid, type, id1, id2, lmode, request, block
from gv$lock where type='DX' and id1=&id1 and id2=&id2;

--發現並無存在DX鎖,至此,可以推斷是又Oracle bug引起.
References
BUG:10282287 - RAC / LOOPBACK CONNECTION / LOB ACCESS / HANG : SQL*NET MORE DATA TO CLIENT
BUG:11736004 - INSERT BY SELECTING CLOB DATA LARGER THAN 32K OVER LOOKBACK DATABASE LINK HANGS

5.通過修改隱含引數規避此bug
sqlplus / as sysdba
alter system set "_clusterwide_global_transactions"=false scope=spfile;

6.重起兩個節點例項,使引數生效.

7.執行sql驗證,成功執行.
insert into tab1@db_link select * from tab2;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22990797/viewspace-1335041/，如需轉載，請註明出處，否則將追究法律責任。