Oracle SGA 效能調校核心指標：Shared Pool 與 Library Cache 深度解析

調校優先序：Shared Pool 優先於 Data Buffer

Shared Pool 中的 Cache Miss 代價遠高於 Data Buffer，且 Data Dictionary 物件駐留時間較長，因此關鍵在於 Library Cache 優化。

核心指標與閾值

1. SQL Area Get Hit Ratio

SELECT gethitratio FROM v$librarycache WHERE namespace = 'SQL AREA';

• 目標 > 90%
• 未達標：檢查應用程式碼，提升可重用性（使用繫結變數）

2. Reloads / Pins 比率

SELECT SUM(pins) "executions", SUM(reloads) "cache misses",
       SUM(reloads)/SUM(pins) "ratio" FROM v$librarycache;

• 目標 < 1%
• 超標原因：Shared Pool 空間不足、SQL 參照無效物件
• 對策：增加 shared_pool_size、修正無效物件引用

3. Shared Pool Reserved Area 設定

• 建議值：shared_pool_size 的 10%~50%
• 監控 V$SHARED_POOL_RESERVED：
  REQUEST_MISSES = 0 或持平、FREE_MEMORY > 50% → 保留區過大，可縮減

進階優化技巧

4. 匿名 PL/SQL 區塊拆解

將大型匿名區塊轉為小區塊呼叫預存程序，減少 Library Cache 碎片。

5. 執行計畫快取（9i+）

9i 起可將 Execution Plan 與 SQL 文本同存 Library Cache，便於診斷。查詢 V$SQL_PLAN 即可檢視。

6. 釘住大型熱門物件

大型物件（Package、Trigger、Sequence）移出會造成嚴重碎片與號碼遺失。查詢未釘住大物件：

SELECT * FROM v$db_object_cache 
WHERE sharable_mem > 10000 
  AND type IN ('PACKAGE','PROCEDURE','FUNCTION','PACKAGE BODY')
  AND kept = 'NO';

釘住指令：EXEC dbms_shared_pool.keep('package_name');

7. 偵測過大匿名區塊

SELECT sql_text FROM v$sqlarea 
WHERE command_type = 47 AND LENGTH(sql_text) > 500;

解法：拆解為預存程序或釘住。

8. Dictionary Cache 優化

SELECT SUM(getmisses)/SUM(gets) FROM v$rowcache;

• 單一類別 Miss Rate < 2%，總計 < 15%
• 超標唯有增加 shared_pool_size 間接擴大

腳本參考

Oracle 官方提供 dbmspool.sql 協助 Shared Pool 管理。