Oracle 自定義函數性能實測：上下文切換開銷與優化策略

性能現象：自定義函數比內建函數慢 3-7 倍

測試環境：Windows 2003，Xeon 5140 2.33GHz 4核，4GB 內存。表 tacustomer 約 200 萬行。對比三種寫法提取生日年份：

根因分析：SQL/PLSQL 引擎上下文切換

• Oracle 執行 SQL 時在 SQL 引擎；調用 PL/SQL 函數需切換到 PL/SQL 引擎。
• 每行調用一次函數 = 200 萬次上下文切換，CPU 開銷巨大。
• v2 使用 CAST(... AS NUMBER) + 異常捕獲，異常處理機制額外昂貴，導致更慢。

函數代碼對比

版本 1：逐字符檢查

CREATE OR REPLACE FUNCTION f_extract_birthday_v1 (
  p_id      VARCHAR2,
  p_birthday VARCHAR2
) RETURN VARCHAR2 IS
  v_len   PLS_INTEGER := LENGTH(p_id);
  v_yyyy  VARCHAR2(4);
BEGIN
  IF p_birthday IS NOT NULL THEN
    RETURN SUBSTR(p_birthday, 1, 4);
  END IF;

  IF v_len = 15 AND p_id BETWEEN '000000000000000' AND '999999999999999' THEN
    RETURN '19' || SUBSTR(p_id, 7, 2);  -- 一代身份證
  ELSIF v_len = 18 AND SUBSTR(p_id, 1, 17) BETWEEN '00000000000000000' AND '99999999999999999' THEN
    RETURN SUBSTR(p_id, 7, 4);          -- 二代身份證
  END IF;
  RETURN NULL;
END;

版本 2：CAST 異常捕獲（反面教材）

CREATE OR REPLACE FUNCTION f_extract_birthday_v2 (...) RETURN VARCHAR2 IS
  v_num NUMBER;
BEGIN
  BEGIN
    v_num := CAST(p_id AS NUMBER);  -- 非全數字拋異常
    -- 此處省略後續邏輯
  EXCEPTION WHEN VALUE_ERROR THEN
    RETURN NULL;
  END;
END;

優化策略：消除或減少上下文切換

方案 1：純 SQL 重寫（首選）

SELECT COUNT(
  CASE
    WHEN birthday IS NOT NULL THEN SUBSTR(birthday,1,4)
    WHEN LENGTH(certificateno)=15 AND TRANSLATE(certificateno,'0123456789','##########') IS NULL
      THEN '19'||SUBSTR(certificateno,7,2)
    WHEN LENGTH(certificateno)=18 AND TRANSLATE(SUBSTR(certificateno,1,17),'0123456789','##########') IS NULL
      THEN SUBSTR(certificateno,7,4)
  END
) FROM tacustomer WHERE ROWNUM < 2000000;

預期耗時接近基準 8-9 秒，零上下文切換。

方案 2：PRAGMA UDF (12c+)

CREATE OR REPLACE FUNCTION f_extract_birthday_udf (...) RETURN VARCHAR2
  PRAGMA UDF;  -- 告知編譯器優化 SQL 調用路徑
IS ...

實測可降低 30%-50% 開銷，但仍慢於純 SQL。

方案 3：DETERMINISTIC + RESULT_CACHE (適用重複輸入)

CREATE OR REPLACE FUNCTION f_extract_birthday_det (...)
  RETURN VARCHAR2 DETERMINISTIC RESULT_CACHE IS ...

同一證件號重複出現時極快，但首次調用仍有切換開銷。

最佳實踐總結

1. 能用純 SQL 解決的，堅決不用 PL/SQL 函數。
2. 必須用函數時：加 PRAGMA UDF、DETERMINISTIC、RESULT_CACHE 三大利器。
3. 避免在函數內使用異常捕獲做邏輯判斷（如 v2 的 CAST）。
4. 大數據量 ETL 建議用 INSERT /*+ APPEND */ ... SELECT 純SQL邏輯 直載目標表。