Oracle 綁定變數深度解析：原理、硬解析與軟解析、實戰範例

核心觀念：為什麼需要綁定變數？

Oracle 綁定變數的本質是替代 SQL 語句中的常值。它是讓 Oracle 重複利用執行計畫、避免硬解析的唯一機制。掌握綁定變數，是編寫高效能 PL/SQL 與應用程式的關鍵。

硬解析 vs. 軟解析：效能分水嶺

硬解析

當 SQL 送入共用池無完全相同語句時觸發。需經歷語法檢查、語意分析、權限驗證、最佳化器制定執行路徑、生成執行計畫等完整流程。極度消耗 CPU，且佔用關鍵 Latch (閂鎖) 資源，嚴重限制系統擴展性，單純增加記憶體或 CPU 無法根治。

軟解析

共用池中存在完全相同的 SQL (文字逐字相符)，直接複用現有執行計畫，僅做輕量級語法/權限檢查。效能差異可達數十到數百倍。

綁定變數如何消除硬解析

使用綁定變數 (如 :1, :2 或 :cust_no) 後，無論傳入何種實際數值，SQL 文本永遠保持完全一致。Oracle 即可在共用池精準匹配，實現軟解析複用。

關鍵特性與限制

• 佔位符本質：同名綁定變數不代表同一值，傳遞時依出現順序對位，而非名稱。
• 不可替代項目：無法用於表名、欄位名、程序名等結構性識別符，僅能替代數值常值。
• 資料歪斜風險：當欄位有索引且資料分布極度不均 (如某值佔 90% 以上) 時，綁定變數可能導致最佳化器誤判執行計畫，反而降低效能。此類場景建議使用字面值或直方圖統計資訊輔助。
• 不適用場景：極低頻執行的 SQL (綁定變數優勢被最佳化器開銷抵消)、資料倉庫型大量複雜查詢。

實戰程式碼範例

建立測試表

CREATE TABLE t (a VARCHAR2(20), b VARCHAR2(20));

使用綁定變數 (高效能模式)

DECLARE
  v_sql VARCHAR2(50);
BEGIN
  FOR i IN 1..100000 LOOP
    v_sql := 'INSERT INTO t VALUES (:1, :2)';
    EXECUTE IMMEDIATE v_sql USING 'a'||i, 'b'||i;
  END LOOP;
  COMMIT;
END;
/

所有 10 萬次插入共用同一個執行計畫，僅軟解析。

不使用綁定變數 (低效能模式)

DECLARE
BEGIN
  FOR i IN 1..100000 LOOP
    INSERT INTO t VALUES ('a'||i, 'b'||i);
  END LOOP;
  COMMIT;
END;
/

每次迴圈產生不同 SQL 文本，觸發 10 萬次硬解析，嚴重拖垮系統。

清除測試資料

TRUNCATE TABLE t;

Oracle 10g/11g/12c/19c 中的演進

自 10g 全面採用 CBO (成本基礎最佳化) 取代 RBO，綁定變數搭配 CURSOR_SHARING、Adaptive Cursor Sharing (11g)、SQL Plan Baselines (11g) 等特性，效能提升空間遠大於 9i 時代。現代版本更能智慧處理資料歪斜問題。