Library Cache Pin 與 Procedure 重編譯：Oracle 10g 行為變化深度測試

現象重現

1. Session 1 建立 `pining`、`calling`（呼叫 `pining` 並 `DBMS_LOCK.SLEEP(60)`），執行 `calling` → 持有 `pining` Body 的 **Shared Pin**。
2. Session 2 執行 `CREATE OR REPLACE PROCEDURE pining ...` → **Hang**，等待 Library Cache Pin（Exclusive）。
3. Session 3 查 `V$SESSION` 見 `library cache pin` 等待事件。
4. Session 1 結束 → Session 2 繼續完成，**但 `LAST_DDL_TIME` 未變更**——實際未重編譯，僅等待 Pin 釋放。

根因分析

• 10g 引入「無效化判斷優化」：若新舊原始碼**完全相同**，跳過實際重編譯，**但仍需取得 Exclusive Pin** 以確保無其他 Session 正在執行。
• Shared Pin（執行中）阻塞 Exclusive Pin（DDL），造成競爭，**本質未解**。

對比：GRANT 在 10g 的突破

1. 同樣 Session 1 執行 `calling` 持有 Shared Pin。
2. Session 2 執行 `GRANT EXECUTE ON pining TO sys;` → **即時成功，無等待**。
3. Session 1 結束後查 `LAST_DDL_TIME`：**已更新**——DDL 實際執行且未阻塞執行中 Session。

結論：10g **GRANT 繞過 Library Cache Pin 競爭**，但 `CREATE OR REPLACE` 仍需 Exclusive Pin。

實務啟示

1. **避免業務高峰期重編譯熱門 Package/Procedure**——雖代碼未變，仍會阻塞執行中 Session。
2. 權限變更（GRANT/REVOKE）可放心在線上執行，10g+ 無阻塞。
3. 若必須熱修復代碼：考慮 **Edition-Based Redefinition (11gR2+)** 實現零停機版本切換。
4. 監控腳本：

   SELECT s.sid, s.username, s.program, w.event, w.p1raw
   FROM   v$session s, v$session_wait w
   WHERE  s.sid = w.sid
   AND    w.event = 'library cache pin'
   AND    w.wait_time = 0;

參考

原文：Eygle《深入淺出 Oracle》讀者 dqpylf 發現，多版本驗證後發文。