Oracle Shared Pool 調優關鍵指標與實戰腳本

為何優先調優 Shared Pool？

Shared Pool 的 Cache Miss 成本遠高於 Data Buffer。Dictionary Cache 留存時間較長，關鍵在於 **Library Cache** 效能。核心指標三件套：

• Gets (Parse)：Namespace 中查找物件次數
• Pins (Execution)：讀取/執行物件次數
• Reloads (Reparse)：執行期 Library Cache Miss 導致重新解析次數

八大調優要點與檢查腳本

1. SQL Area 命中率 > 90%

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

若未達標，優先檢查應用程式：是否使用綁定變數？是否重複解析相似 SQL？

2. Reloads / Pins < 1%

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

超標兩大原因：Shared Pool 空間不足、SQL 參照物件失效（如 DDL 導致游標失效）。

3. Reserved Pool 大小適中

預設 10% Shared Pool，上限 50%。檢查：

SELECT * FROM v$shared_pool_reserved;

REQUEST_MISSES = 0 或持平、FREE_MEMORY > 50% 表示過大，可壓縮。

4. 大型匿名 PL/SQL 區塊拆解

將大型匿名區塊改為「小匿名區塊呼叫預存程序」，減少解析與記憶體碎片。

5. 執行計畫留存 (9i+)

V$SQL_PLAN 保存執行計畫，利於事後診斷。

6. 釘住大型熱門物件

大物件是記憶體碎片主因。查找未釘住的大型 Package/Procedure/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');

對應腳本：dbmspool.sql

7. 掃描超大匿名區塊

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

處理方案：拆解為預存程序呼叫，或同樣釘住。

8. Dictionary Cache Miss 率控制

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

• 單項 < 2%，總計 < 15%
• 超標唯一手段：增大 shared_pool_size

總結檢查清單

實務建議

定期排程執行上述查詢，建立基準線。發現指標惡化時，優先檢查應用端是否新增未使用綁定變數的 SQL、是否頻繁 DDL 導致失效，再考慮調整記憶體參數。