Oracle 軟硬解析深度解析：原理、危害、Cursor Sharing 與綁定變數優化實戰

Oracle 軟硬解析深度解析：原理、危害、Cursor Sharing 與綁定變數優化實戰

SQL 執行流程與解析分類

1. 語法檢查
2. 語義檢查 (物件存在、權限)
3. 解析：生成解析樹與執行計畫 —— 硬解析/軟解析分水嶺
4. 執行並返回

Oracle 對 SQL 文本哈希後在 Library Cache 查找：

• Hard Parse：無匹配哈希值，或文本不完全相同 (大小寫、空格、註釋、字面值不同) → 完整優化、生成計畫、佔用 Latch/CPU。
• Soft Parse：哈希匹配且文本逐字元相同 → 直接重用現有計畫，跳過優化器。

硬解析的三大危害

1. 資源消耗大：佔用 Shared Pool Latch、CPU、記憶體，單條 SQL 70%+ 時間花在解析。
2. 並發瓶頸：Latch 競爭導致系統吞吐率下降，即使資源閒置也慢。
3. 連鎖效應：大量硬解析的簡單應用會拖垮整個資料庫效能。

判斷硬解析計數

SELECT name, value FROM v$sysstat WHERE statistic# = 331; -- parse count (hard)

優化策略一：CURSOR_SHARING 參數

實測：SIMILAR 模式下字面值自動轉綁定

ALTER SYSTEM SET CURSOR_SHARING = SIMILAR;

-- Session 執行
SELECT * FROM dept WHERE deptno = 10;
SELECT * FROM dept WHERE deptno = 20;

-- v$sql 顯示
SELECT sql_text, child_number FROM v$sql
WHERE sql_text LIKE 'select * from dept where deptno%';
-- 結果：select * from dept where deptno = :"SYS_B_0"  (僅一條，child=0)

優化策略二：綁定變數 — 根治硬解析

正確用法

-- 應用層 (JDBC/ODP.NET/Hibernate) 使用參數標記
SELECT * FROM emp WHERE empno = ?;  -- JDBC
SELECT * FROM emp WHERE empno = :eno; -- PL/SQL

-- PL/SQL 動態 SQL 範例
EXECUTE IMMEDIATE 'INSERT INTO tb_test VALUES (:n)' USING i;

綁定變數效能實測 (Tom Kyte runstats)

綁定變數副作用與對策

• 直方圖失效：優化器無法窺探字面值分布，可能選擇次優計畫。→ 對傾斜欄位保留字面值或用 DBMS_STATS.SET_COLUMN_STATS 模擬。
• SQL Plan Baseline / Adaptive Cursor Sharing (11g+) 可緩解。

總結建議

1. 應用層全面使用綁定變數，源頭消除硬解析。
2. 無法修改代碼時，謹慎用 CURSOR_SHARING=SIMILAR 過渡，避免 FORCE。
3. 定期監控 parse count (hard)、v$sqlarea 版本數 (child cursor 過多暗示綁定不當)。