Oracle Shared Pool 物件無法釋放導致 ORA-04031 完整排查與優化方案

ORA-04031: unable to allocate xx bytes of shared memory 是 Shared Pool 記憶體耗盡的經典錯誤。根因多為「大型 SQL 佔用 Shared Pool 且無法老化淘汰」，導致新 SQL 無法載入。本文提供監控查詢、Metalink 根因分析、SQL 優化策略、參數調整與應急重啟方案。

即時監控：找出佔用前幱 SQL

-- 佔用 > 100KB 的 SQL (可調整門檻)
SELECT sql_text, sharable_mem, executions, loads, version_count
FROM v$sql
WHERE sharable_mem > 100000
ORDER BY sharable_mem DESC;

-- 搭配 V$SQLAREA 彙總相同 SQL_TEXT
SELECT sql_text, SUM(sharable_mem) AS total_mem, COUNT(*) AS child_count
FROM v$sql
GROUP BY sql_text
HAVING SUM(sharable_mem) > 100000
ORDER BY total_mem DESC;

Shared Pool 結構與狀態檢視

-- 詳細記憶體分佈
SELECT pool, name, bytes/1024/1024 AS "MB"
FROM v$sgastat
WHERE pool = 'shared pool'
ORDER BY bytes DESC;

-- Library Cache 命中率
SELECT namespace, gets, gethits, (gethits/gets)*100 AS hit_ratio
FROM v$librarycache;

Metalink 核心根因 (Doc ID 352617.1 等)

若 SHARED_POOL_SIZE 已足夠，大多數 ORA-04031 由 動態 SQL 碎片化 導致，主因三點：

1. 不共享 SQL：字面值硬編碼、未使用綁定變數。
2. 不必要的軟解析：重複開啟/關閉 Cursor、應用端未啟用 Statement Cache。
3. 未使用綁定變數：導致每次硬解析、產生大量 Child Cursor (高 VERSION_COUNT)。

系統性優化策略

應急處理：必須重啟才能釋放

當 ORA-04031 發生，無法透過指令釋放已載入 Shared Pool 的物件，唯有重啟執行個體。因此：

1. 建立監控腳本，每 5 分鐘檢查 V$SGASTAT Free Memory < 10% 即告警。
2. 定期 (每週/月) 執行上述「佔用前幱 SQL」查詢，通知開發優化。
3. 維護窗口排程重啟 (如月一次) 預防性釋放碎片。

避坑提醒

• CURSOR_SHARING=FORCE 可能導致部分查詢計畫劣化，必須在測試環境全面回歸 後上線。
• SHARED_POOL_RESERVED_SIZE 過大反而減少可用空間，建議維持預設 5% 或依 V$SHARED_POOL_RESERVED 監控調整。
• 大型 PL/SQL Package/匿名區塊編譯會產生大量 Library Cache 物件，建議上線前預先編譯並 KEEP。

Shared Pool 管理核心在於「減少硬解析、控制 Child Cursor 版本、預留緩衝空間」。建立 監控→優化→預留 的閉環機制，才能徹底杜絕 ORA-04031。