您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 教學

Oracle 單實例 Library Cache Pin 等待事件模擬與深度分析

編輯：Oracle 教學

Oracle 單實例 Library Cache Pin 等待事件模擬與深度分析

Library Cache Pin 是 Oracle 共用池中管理程式單元（PL/SQL 程序、套件、觸發器等）並發存取的關鍵鎖機制。當 Session 需要修改或重編譯一個正在被執行的程式單元時，會發生 library cache pin 等待。本文在 Oracle 10.2.0.5 單實例環境中，完整模擬並分析此等待事件的產生條件、阻塞鏈與診斷方法。

實驗環境

資料庫：Oracle 10.2.0.5 x86-64bit
作業系統：RHEL 5.8 x86-64bit

場景模擬：三個 Session 互動

Session 1：建立並執行長時間運行的程序

[oracle@localhost ~]$ sqlplus / as sysdba
SQL> startup
SQL> create user lc0019999 identified by aaaaaa;
SQL> grant dba to lc0019999;
SQL> create user lc0029999 identified by aaaaaa;
SQL> grant dba to lc0029999;
SQL> create user lc0039999 identified by aaaaaa;
SQL> grant dba to lc0039999;

SQL> conn lc0019999/aaaaaa
SQL> Create or replace procedure dummy is
  2  begin
  3    null;
  4  end;
  5  /

SQL> Begin
  2    Dummy;
  3    Dbms_lock.sleep(1000);
  4  End;
  5  /  ------------> 持續睡眠，持有 Library Cache Pin (Mode 2 = Share)

Session 2：嘗試重編譯同一程序（被阻塞）

[oracle@localhost ~]$ sqlplus lc0029999/aaaaaa
SQL> alter procedure lc0019999.dummy compile;  ------------> 持續 Hang 住，請求 Exclusive Pin (Mode 3)

Session 3：SYS 診斷等待鏈

[oracle@localhost ~]$ sqlplus / as sysdba
SQL> set line 200
SQL> select sid, serial#, event from v$session where event like '%library cache pin%';
SID    SERIAL# EVENT
---------- ---------- ----------------------------------------------------------------
158    28       library cache pin

SQL> select sid, serial#, p1raw, event from v$session where event like '%library cache pin%';
SID    SERIAL# P1RAW              EVENT
---------- ---------- ---------------- --------------------------------------------------
158    28       000000006BFF19B0   library cache pin

深度診斷：定位物件與持有者

1. 透過 P1RAW (KGLHDADR) 查詢物件

SQL> col owner for a30
SQL> col object for a30
SQL> SELECT kglnaown "Owner", kglnaobj "Object"
  2  FROM x$kglob
  3  WHERE kglhdadr='000000006BFF19B0';

Owner                          Object
------------------------------ ------------------------------
LC0019999                      DUMMY

確認等待發生在 LC0019999.DUMMY 程序上。

2. 查詢持有者與等待者模式

SQL> SELECT s.sid, kglpnmod "Mode", kglpnreq "Req"
  2  FROM x$kglpn p, v$session s
  3  WHERE p.kglpnuse=s.saddr
  4  AND kglpnhdl='000000006BFF19B0';

SID    Mode    Req
---------- ---------- ----------
158    0       3       <-- 等待者，請求 Exclusive (3)
159    2       0       <-- 持有者，持有 Share (2)

模式對照表：

0 = None
1 = Null
2 = Share（執行程式時持有）
3 = Exclusive（DDL/重編譯時請求）

等待鏈圖解

Session 1 (SID 159)                    Session 2 (SID 158)
┌─────────────────────────┐            ┌─────────────────────────┐
│ 執行 DUMMY 程序         │            │ ALTER PROCEDURE COMPILE │
│ 持有 Library Cache Pin  │───────────▶│ 等待 Library Cache Pin  │
│ Mode = 2 (Share)        │  阻塞      │ Req = 3 (Exclusive)     │
│ kglpnmod=2, kglpnreq=0  │            │ kglpnmod=0, kglpnreq=3  │
└─────────────────────────┘            └─────────────────────────┘

關鍵知識點總結

Pin vs Lock：Library Cache Lock 保護物件定義結構（Handle），Library Cache Pin 保護物件執行體（Heap）。執行 PL/SQL 取 Pin (Share)；DDL/重編譯需 Pin (Exclusive)。
不相容性：Share 與 Exclusive 互斥。長時間運行的 PL/SQL 會阻塞該物件的 DDL 操作。
診斷鍵值：
- v$session.p1raw = x$kglob.kglhdadr (Handle Address)
- x$kglpn.kglpnhdl = Handle Address，連接 v$session.saddr 可得 SID
X$ 表權限：需 SELECT_CATALOG_ROLE 或 SYS 權限查詢 x$kglob、x$kglpn。
10g/11g 差異：11g 引入 v$mutex_sleep_history、v$library_cache_lock 等新視圖，診斷更便利，但 X$ 核心邏輯不變。