Oracle 庫快取與共享池深度優化：命中率、重載率與參數調校

共享池 = 庫快取 + 字典快取 + SQL 區。優化核心：最大化命中率、最小化重載率。整體大小由 SHARED_POOL_SIZE 控制。

一、Redo Log Buffer 優化（前置知識）

1. 查詢 V$SYSSTAT 取得 redo log space requests、redo entries。
2. 申請失敗率 = requests / entries → 應趨近 0。
3. 失敗率高 → 增加 LOG_BUFFER 並持續監控。
4. Latch 爭用：查詢 V$LATCH + V$LATCHNAME 過濾 'redo allocation'、'redo copy'。
   - 分配 Latch 爭用 → 降低 LOG_SMALL_ENTRY_MAX_SIZE。
   - 複製 Latch 爭用 → 增加 LOG_SIMULTANEOUS_COPIES (建議 2×CPU) 或增加 LOG_ENTRY_PREBUILD_THRESHOLD。

二、排序區優化

• sorts (memory)：記憶體排序（佳）。
• sorts (disk)：磁碟排序（慢 ~14,000 倍）。
• 磁碟排序多 → 增加 SORT_AREA_SIZE (PGA) 或 PGA_AGGREGATE_TARGET (10g+ 自動管理)。

三、庫快取優化三步驟

1. 獲取活動統計

SELECT namespace, pins, reloads FROM V$LIBRARYCACHE;

• NAMESPACE：'SQL AREA'、'TABLE/PROCEDURE'、'BODY'、'TRIGGER' 等。
• PINS：執行呼叫總次數。
• RELOADS：隱式解析/重載錯誤次數。

2. 計算重載率

RELOADS / PINS < 1% 為合格。超標措施：

• 增加 SHARED_POOL_SIZE 或 OPEN_CURSORS。
• 撰寫可重用 SQL（常數改繫結變數）。
• 若零錯誤可設 CURSOR_SPACE_FOR_TIME=TRUE 加速呼叫（需足夠私有 SQL 區）。

3. 字典快取同步優化

SELECT SUM(gets), SUM(getmisses) FROM V$ROWCACHE;

GETMISSES / GETS < 10%~15%；否則增大 SHARED_POOL_SIZE。

四、關鍵參數互動總結