Oracle Latch 爭用診斷與解決：Shared Pool 與 Library Cache 熱點分析

Latch 基礎概念

• 低層序列化機制，保護 SGA 共享結構 (如 Buffer Cache 區塊頭、Library Cache 雜湊桶)。
• 兩種請求模式：
  • Willing-to-wait：自旋 → 睡眠 → 重試，直至獲得。
  • Immediate：搶不到立即放棄，繼續其他工作。
• 不直接調優 Latch，爭用意味著 SGA 使用模式異常。

爭用偵測查詢

系統級等待事件排序

SELECT * FROM v$system_event ORDER BY time_waited DESC;

若 latch free 排前列，需深入分析。

Latch 詳細統計

SELECT * FROM v$latch;

Willing-to-wait 關鍵欄位：GETS、MISSES、SLEEPS、WAIT_TIME、CWAIT_TIME、SPIN_GETS。
Immediate 關鍵欄位：IMMEDIATE_GETS、IMMEDIATE_MISSES。

常見熱點 Latch 及對策

Shared Pool / Library Cache Latch

SELECT * FROM v$latch
WHERE name LIKE '%shared pool%' OR name LIKE '%library cache%';

爭用通常源自三大原因：

1. 不可共享 SQL：字面值導致大量相似語句。

   SELECT sql_text FROM v$sqlarea WHERE executions=1 ORDER BY upper(sql_text);

   對策：改用繫結變數、CURSOR_SHARING=FORCE/SIMILAR。
2. 共享 SQL 重新編譯：物件變更導致失效。

   SELECT sql_text, parse_calls, executions FROM v$sqlarea WHERE parse_calls > 5;

   對策：檢查 DDL 頻率、增加 Shared Pool、使用 KEEP 池。
3. Library Cache 空間不足：被迫頻繁老化出。 對策：調大 SHARED_POOL_SIZE、啟用 SESSION_CACHED_CURSORS。

一般處理原則

1. 鎖定爭用熱點 Latch (查 V$LATCH / V$LATCH_CHILDREN)。
2. 若集中於 Shared Pool/Library Cache → 優化應用 (繫結變數、減少硬解析)。
3. 若調整應用無效 → 擴充 Shared Pool / Buffer Cache。
4. 避免盲目調整隱藏參數 (如 _SPIN_COUNT)，除非 Oracle 支援建議。