Oracle 10g Library Cache Pin 競爭與 Procedure 重建行為分析

前言

Oracle 10g 增強 Procedure 重建機制，但 Library Cache Pin 競爭在特定場景下仍存在。本文透過實測案例分析重建與授權操作的鎖行為差異。

實測場景：Procedure 重建遭遇 Shared Pin 阻塞

1. Session 1 建立 PINING 與 CALLING，CALLING 呼叫 PINING 並 DBMS_LOCK.SLEEP(60) 持有 Shared Pin。
2. Session 2 執行 CREATE OR REPLACE PROCEDURE PINING → 掛起等待 Library Cache Pin（Exclusive）。
3. Session 3 觀察 V$SESSION：Session 2 顯示 library cache pin 等待事件。
4. Session 1 結束後，Session 2 完成重建，但 LAST_DDL_TIME 未變（因實際未重新編譯，僅等待）。

結論：10g 仍會在 Shared Pin 存在時強制要求 Exclusive Pin，無法依鎖模式跳過，競爭本質未改變。

對比：GRANT 授權操作的增強

同樣環境下，Session 2 執行 GRANT EXECUTE ON PINING TO SYS → 立即成功，無等待。
Session 1 結束後，LAST_DDL_TIME 更新為授權時間。

發現：Oracle 10g 讓 GRANT 繞過 Library Cache Pin 競爭，DDL 時間戳記正確更新，這是 10g 真正的增強點。

實務啟示

• 線上環境避免在高併發時段重建被頻繁呼叫的 Procedure/Package。
• 若需修改邏輯，考慮新建版本並切換同義詞，或使用 Edition-Based Redefinition（11g+）。
• 授權操作可放心在線執行，不會阻塞業務。

小結

10g 對 Procedure 重建的增強有限（主要在無效化處理），Library Cache Pin 競爭核心機制未變；但 GRANT 操作成功繞過 Pin 鎖，值得關注。