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完成了才能继续工作。一次400行SQL的优化过程Oracle 11g R1 数据字典的 latch: cache buffers chains问题相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）