Shared Pool Miss (硬解析) 成本遠高於 Buffer Cache Miss (物理讀)。Library Cache (SQL/PLAN) 變動頻繁，Dictionary Cache 相對穩定，**關鍵在 Library Cache**。
| 指標 | 查詢 SQL | 健康閾值 | 異常處理 |
|---|---|---|---|
| SQL Area Gethitratio | SELECT gethitratio FROM v$librarycache WHERE namespace='SQL AREA'; | > 90% (理想 > 95%) | 檢查應用是否使用綁定變數、CURSOR_SHARING |
| Reloads / Pins (Library Cache Miss Rate) | SELECT SUM(pins) execs, SUM(reloads) misses, SUM(reloads)/SUM(pins) ratio FROM v$librarycache; | < 1% | 1) 增加 SHARED_POOL_SIZE 2) 檢查無效物件導致重解析 |
| Dictionary Cache Miss Rate | SELECT SUM(getmisses)/SUM(gets) FROM v$rowcache; | 總計 < 15%，單項 < 2% | 間接調大 SHARED_POOL_SIZE (無獨立參數) |
| Reserved Pool 使用率 | SELECT * FROM v$shared_pool_reserved; | FREE_MEMORY > 50% 或 REQUEST_MISSES=0/不增長 | 過大則壓縮 (Reserved Pool = 10% Shared Pool，上限 50%) |
大型匿名區塊佔用大量 Shared Pool、易造成碎片。拆解後小區塊調用存儲過程，存儲過程可被 KEEP 鎖定。
V$SQL_PLAN 可直接查看計劃，利於診斷。善用 DBMS_XPLAN.DISPLAY_CURSOR。
大物件 (Package/Procedure/Trigger/Sequence) 一旦被淘汰，重載成本高、且 Sequence 可能導致號碼跳號。
SELECT owner, name, type, sharable_mem
FROM v$db_object_cache
WHERE sharable_mem > 10000 -- > 10KB
AND type IN ('PACKAGE','PROCEDURE','FUNCTION','PACKAGE BODY','TRIGGER','SEQUENCE')
AND kept = 'NO'
ORDER BY sharable_mem DESC;
EXEC DBMS_SHARED_POOL.KEEP('SCHEMA.PACKAGE_NAME');
EXEC DBMS_SHARED_POOL.KEEP('SCHEMA.TRIGGER_NAME');
EXEC DBMS_SHARED_POOL.KEEP('SCHEMA.SEQUENCE_NAME');
對應腳本：$ORACLE_HOME/rdbms/admin/dbmspool.sql。
-- 找出超過 500 字元的匿名區塊 (COMMAND_TYPE=47)
SELECT sql_text FROM v$sqlarea
WHERE command_type = 47 AND LENGTH(sql_text) > 500;
處理方案：A) 拆解為存儲過程 B) 若必須保留，執行 DBMS_SHARED_POOL.KEEP 鎖定 (需先轉為 Cursor)。
-- 可用塊大小分布
SELECT KSMCHCLS CLASS, COUNT(*), SUM(KSMCHSIZ) TOTAL_BYTES, MAX(KSMCHSIZ) MAX_CHUNK
FROM X$KSMSP
WHERE KSMCHCLS IN ('free','R-free','perm','R-perm')
GROUP BY KSMCHCLS;
R-free (Reserved Free) 不足易觸發 ORA-04031。調大 SHARED_POOL_RESERVED_SIZE (隱含參數 _SHARED_POOL_RESERVED_PCT，預設 5%)。
ALTER SYSTEM SET CURSOR_SHARING=FORCE 可緩解，但可能導致計劃不穩，需測試。
# 每小時執行
hit_ratio = query("SELECT gethitratio FROM v$librarycache WHERE namespace='SQL AREA'")
if hit_ratio < 0.90: alert("Library Cache Hit Ratio 低於 90%")
miss_rate = query("SELECT SUM(reloads)/SUM(pins) FROM v$librarycache")
if miss_rate > 0.01: alert("Reloads/Pins 超過 1%")
dict_miss = query("SELECT SUM(getmisses)/SUM(gets) FROM v$rowcache")
if dict_miss > 0.15: alert("Dictionary Cache Miss Rate 超過 15%")
reserved = query("SELECT free_space, request_misses FROM v$shared_pool_reserved")
if reserved.free_space < reserved.total_space * 0.5: alert("Reserved Pool 空閒低於 50%")
