測試環境與場景
- 建立測試表：
CREATE TABLE test (a NUMBER(10));
- 寫入 100,001 筆資料 (0 ~ 100,000)，分別測試**動態 SQL 拼接** vs **綁定變量**。
方案一：字串拼接 (無綁定變量)
CREATE OR REPLACE PROCEDURE p_test AS
i NUMBER(10) := 0;
BEGIN
WHILE i <= 100000 LOOP
EXECUTE IMMEDIATE 'INSERT INTO test VALUES (' || TO_CHAR(i) || ')';
i := i + 1;
END LOOP;
COMMIT;
END p_test;
/
-- 執行時間: 91.111 秒
方案二：使用綁定變量 (USING 子句)
CREATE OR REPLACE PROCEDURE p_test AS
i NUMBER(10) := 0;
BEGIN
WHILE i <= 100000 LOOP
EXECUTE IMMEDIATE 'INSERT INTO test VALUES (:a)' USING i;
i := i + 1;
END LOOP;
COMMIT;
END p_test;
/
-- 執行時間: 55.099 秒
結果分析
| 指標 | 字串拼接 | 綁定變量 | 提升幅度 |
|---|
| 執行時間 | 91.111 秒 | 55.099 秒 | 39.5% 更快 |
| Hard Parse 次數 | 100,001 次 | 1 次 (共享游標) | - |
| Shared Pool 壓力 | 極大 (產生 10 萬個游標) | 極小 (單一游標複用) | - |
| Latch 爭用 | Library Cache / Shared Pool Latch 高 | 幾乎無 | - |
核心原理
- Hard Parse：語法檢查、語義分析、權限驗證、優化器生成計劃、存入 Library Cache — 極度消耗 CPU 與 Latch。
- Soft Parse / Cursor Sharing：綁定變量使 SQL 文本完全相同，直接複用現有游標，僅做 Bind Match 與執行。
- Oracle SGA 設計初衷即為**鼓勵綁定變量**，減少 Hard Parse。
最佳實踐建議
- **所有動態 SQL 必須使用 USING 綁定變量**，除非極少數 DDL 或無法綁定場景。
- 應用端 (Java/.NET/Node.js) 使用
PreparedStatement / Parameterized Query，原理相同。
- 若歷史系統大量拼接 SQL，可開啟
CURSOR_SHARING = FORCE 過渡 (會強制將字面值轉為綁定變量)，但可能產生次優計劃，長期仍需改寫代碼。
- 監控指標：
V$SYSSTAT 的 parse count (hard) / parse count (total) 比率，建議 < 10%。
