RAC 環境Library Cache Lock的處理方法

我要監控某個Job,該session 處於 Library cache lock 等待中

資料庫有2個節點
SQL> select inst_id from gv$instance;

INST_ID
----------
2

1

SQL> select inst_id,sid,serial#,event ,p1raw,machine,status from gv$session where username='BX5685';

INST_ID SID SERIAL# EVENT P1RAW MACHINE STATUS
---------- ---------- ---------- ------------------------------ ---------------- -------------------- ---------------
1 4538 39833 library cache lock C000000346FBA458 bdhp4462 ACTIVE

在Node1上面查詢
SQL> select * from dba_kgllock where kgllkreq > 0;

KGLLKUSE KGLLKHDL KGLLKMOD KGLLKREQ KGLLKTYPE
---------------- ---------------- ---------- ---------- ------------
C0000004789EF9D0 C000000346FBA458 0 2 Lock

SQL> select kglnaown, kglnaobj from x$kglob where kglhdadr = 'C000000346FBA458';

KGLNAOWN KGLNAOBJ
-------------------- --------------------
IDWSU1 PROD_ASSOC_DNORM

SQL> select kglhdadr, kglnaown, kglnaobj from x$kglob where kglnaobj = 'PROD_ASSOC_DNORM' and KGLNAOWN='IDWSU1';

KGLHDADR KGLNAOWN KGLNAOBJ
---------------- -------------------- --------------------
C000000346FBA458 IDWSU1 PROD_ASSOC_DNORM

在Node2上面查詢
SQL> select kglhdadr, kglnaown, kglnaobj from x$kglob where kglnaobj = 'PROD_ASSOC_DNORM' and KGLNAOWN='IDWSU1';

KGLHDADR KGLNAOWN KGLNAOBJ
------------------------------ -------------------- ------------------------------
C000000443267070 IDWSU1 PROD_ASSOC_DNORM
C00000035C33E248 IDWSU1 PROD_ASSOC_DNORM

SQL> col event format a30
select sid, serial#,s.event, sql_text from dba_kgllock w, v$session s, v$sqlarea a
where w.kgllkuse = s.saddr and w.kgllkhdl='C000000443267070'
and s.sql_address = a.address
and s.sql_hash_value = a.hash_value;SQL> 2 3 4

SID SERIAL# EVENT SQL_TEXT
---------- ---------- ------------------------------ --------------------------------------------------
4774 36583 db file scattered read ALTER TABLE PROD_ASSOC_DNORM ENABLE CONSTRAINT PRO
D_ASSOC_DNORM_PK USING INDEX STORAGE ( INITIAL 419
4304 NEXT 4194304 PCTINCREASE 0 ) TABLESPACE CDW_R
EFERENCE01M LOCAL
很明顯了，節點1的session 在等待節點2 的session 進行alter table操作，那麼節點1的 Job 也只能等待節點2的session完成了才能繼續工作。

本篇文章來源於 Linux公社網站() 原文連結：

[@more@]