您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle SQL 共享池機制深度解析：硬解析 vs 軟解析、精確匹配三條件與 Cursor Sharing 實戰優化

編輯：Oracle 資料庫基礎

**「解析一次，執行多次」** 是 Oracle 高效能的基石。SQL 進入資料庫後，優先在 **Shared Pool (共用池, SGA 組件)** 尋找相同的已解析游標。**硬解析** 消耗 CPU、Latch、Heap 記憶體；**軟解析** 僅做語法/語義檢查與權限驗證，開銷極低。本文深度解析 **精確匹配三鐵律、Bind Variable 關鍵作用、Cursor Sharing 參數策略、解析量監控與優化實戰**。

共用池與 Library Cache 架構

Library Cache：Shared Pool 核心子結構，儲存 **Parsed SQL (Heaps)**、執行計畫、PL/SQL 編譯單元、鎖/釘狀態。
Parent Cursor (SQL Text)：以 **SQL 文本雜湊值** 為 Key，掛載多個 Child Cursor。
Child Cursor：針對特定 **優化器環境、綁定變數長度、NLS 參數、容器 ID** 等差異生成的獨立執行計畫實體。
V$SQL / V$SQLAREA：監視 Parent/Child Cursor 統計 (解析次數、執行次數、CPU、Elapsed、Buffer Gets)。

共享游標的「精確匹配三鐵律」

只有同時滿足三條件，才能複用現有 Child Cursor (軟解析)：

條件 A：字元級逐字比對 (Case/空格/換行/註釋全敏感)


-- 以下四條在 Library Cache 中是**四個不同 Parent Cursor**
SELECT * FROM emp;                           -- Hash 1
SELECT * FROM EMP;                           -- Hash 2 (大小寫不同)
SELECT * FROM emp ;                          -- Hash 3 (結尾空格)
SELECT /* hint */ * FROM emp;                -- Hash 4 (註釋不同)

**對策：** 程式碼中 SQL **常數化、格式統一、使用 Bind Variable**。

條件 B：參考物件解析完全一致 (Schema/同義詞/權限)


-- User JACK: 私有同義詞 sal_limit → JACK.SAL_LIMIT
-- User JILL: 私有同義詞 sal_limit → JILL.SAL_LIMIT (不同物件)
-- 兩者執行相同 SQL: SELECT * FROM sal_limit
-- → 無法共享 Child Cursor，因 OBJECT_ID 不同

**對策：** 統一使用 **Public Synonym** 或 **Schema Qualified Name** (`SCHEMA.TABLE`)，避免私有同義詞導致「同文本異物件」。

條件 C：綁定變數名稱與數據類型完全一致


-- 可共享
SELECT * FROM emp WHERE empno = :b1;         -- :b1
SELECT * FROM emp WHERE empno = :b1;         -- :b1

-- 不可共享 (綁定變數名稱不同)
SELECT * FROM emp WHERE empno = :b1;         -- :b1
SELECT * FROM emp WHERE empno = :b2;         -- :b2 → 新 Child Cursor

**對策：** 程式框架層**統一綁定變數命名規範** (如 `:p1, :p2...` 或 `:empno, :ename`)，禁止動態生成變數名。

硬解析危害與軟解析收益量化

關鍵優化槓桿：Bind Variable (綁定變數)


-- 惡例：字串拼接 (每次不同 Literal → 硬解析洪水)
String sql = "SELECT * FROM orders WHERE order_id = " + orderId; 

-- 佳例：PreparedStatement + Bind Variable (軟解析複用)
String sql = "SELECT * FROM orders WHERE order_id = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setLong(1, orderId);

鐵律：**所有變動值絕對禁止字串拼接入 SQL，100% 使用 Bind Variable。** 這是解決 90% 以上解析風暴、Library Cache Latch 爭用、Shared Pool ORA-04031 的根本之道。

CURSOR_SHARING 參數：緊急止血 vs 根治

指標	硬解析	軟解析
CPU 消耗	高 (語法/語義/優化/行源生成)	極低 (僅查找 Hash、權限檢查)
Latch 爭用	Library Cache Latch / Shared Pool Latch 熱點	幾乎無
Shared Pool 記憶體	每個新 Cursor 佔用 1-5 MB+ (Heap 0-6)	零增長
執行計畫穩定性	每次可能不同 (統計資訊變化、Bind Peeking)	複用既有計畫，穩定可預測

值	行為	適用場景	風險
EXACT (預設)	精確匹配三鐵律	正常開發、已規範 Bind Variable	無
FORCE	強制將 Literal 替換為系統生成 Bind Variable (如 `:SYS_B_0`)	緊急救援：Legacy 系統無法改碼、Literal 導致硬解析風暴	1. 直方圖失效 (CBO 看不見值分布) 2. 函數索引失效 3. 可能產生次優計畫 4. 11g+ Adaptive Cursor Sharing 部分緩解
SIMILAR (11g 棄用)	僅替換「安全」Literal (無直方圖欄位)	過渡方案	同 FORCE，且行為難預測

建議：**開發階段強制 EXACT + 代碼審查 Bind Variable 覆蓋率 100%**；生產突發解析風暴時，**短期開啟 FORCE 止血，同步排程改碼上線，改碼完成後關回 EXACT**。

監控與診斷實戰查詢


-- 1. 硬/軟解析比率 (系統級)
SELECT 
  (1 - (session cursor cache hits) / (parse count (total))) * 100 AS hard_parse_pct
FROM v$sysstat 
WHERE name IN ('parse count (total)', 'session cursor cache hits');

-- 2. 高解析 SQL Top 10
SELECT sql_id, sql_text, parse_calls, executions, 
       round(parse_calls/executions, 2) AS parse_per_exec
FROM v$sqlarea
WHERE executions > 100
ORDER BY parse_calls DESC
FETCH FIRST 10 ROWS ONLY;

-- 3. Child Cursor 爆炸 (同一 SQL_ID 多 Child)
SELECT sql_id, count(*) AS child_count
FROM v$sql
GROUP BY sql_id
HAVING count(*) > 20
ORDER BY child_count DESC;

-- 4. Shared Pool 記憶體壓力
SELECT * FROM v$sgastat WHERE pool = 'shared pool' AND name LIKE '%free%';

Session Cursor Cache：隱形軟解析加速器

參數：SESSION_CACHED_CURSORS (預設 50, 建議 200-500)
機制：Session 關閉 Cursor 時，若未超過限制，**將 Parent Cursor Handle 緩存在 Session 私有記憶體 (PGA)**，下次同 Session 開啟同 SQL **完全跳過 Library Cache 查找**，極速軟解析。
監控：`session cursor cache hits` 統計值。