Oracle 綁定變數效能實測：EXECUTE IMMEDIATE 綁定 vs 字串拼接

實驗設計

向表 T(X INT) 插入 1,000 筆資料，比較兩種動態 SQL 寫法：

寫法 1：綁定變數（使用 USING 子句）

CREATE OR REPLACE PROCEDURE PROC_BIND AS
BEGIN
  FOR I IN 1 .. 1000 LOOP
    EXECUTE IMMEDIATE 'INSERT INTO T VALUES(:X)' USING I;
  END LOOP;
END;
/

寫法 2：字串拼接（字面值內嵌）

CREATE OR REPLACE PROCEDURE PROC_CONCAT AS
BEGIN
  FOR I IN 1 .. 1000 LOOP
    EXECUTE IMMEDIATE 'INSERT INTO T VALUES(' || I || ')';
  END LOOP;
END;
/

實測結果

原理深度解析

綁定變數優勢

1. 游標共享：同一 SQL 文本（含 :X 佔位符）只解析一次，後續複用執行計劃
2. 減少硬解析：避免 1,000 次解析、優化、生成計劃的 CPU 開銷
3. 共享池效率：Library Cache 僅存 1 份父游標，V$SQLAREA 顯示 EXECUTIONS=1000
4. 安全性：天然防 SQL 注入

字串拼接缺陷

• 產生 1,000 條不同 SQL 文本（VALUES(1), VALUES(2)...）
• 每條皆觸發硬解析，佔用 Shared Pool、增加 Latch 競爭
• 大量拼接易導致 ORA-04031 (Shared Pool 記憶體不足)
• SQL 注入風險

驗證查詢

-- 查看共享池 SQL 統計
SELECT SQL_TEXT, EXECUTIONS, PARSE_CALLS, LOADS
FROM V$SQLAREA
WHERE SQL_TEXT LIKE 'INSERT INTO T%';

-- 綁定變數版：EXECUTIONS=1000, PARSE_CALLS=1, LOADS=1
-- 拼接版：1000 筆記錄，各 EXECUTIONS=1, PARSE_CALLS=1, LOADS=1

最佳實踐建議

• 所有動態 SQL 必須使用綁定變數，無例外
• 靜態 SQL（PL/SQL 直接寫 INSERT INTO T VALUES(I)）隱含綁定，效能同寫法 1
• Java/JDBC 端同樣適用：PreparedStatement 對應綁定變數，Statement 對應拼接
• 批次操作優先用 FORALL + 陣列綁定，效能更強

綁定變數不是「優化技巧」，而是「正確寫法」。拼接字串屬於反模式，僅適合極少量、一次性維護腳本。