萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle PL/SQL 自訂函數效能陷阱:引擎切換開銷與內建函數優勢實測

PL/SQL 程式碼執行涉及兩大引擎:SQL 引擎處理 SQL 陳述式,PL/SQL 引擎處理程序邏輯。兩者切換(Context Switch)會產生 CPU 額外開銷。本文以 200 萬筆客戶資料實測,量化自訂函數與內建函數的效能差距。

實測環境

  • 資料表 ta_customer:200 萬列,含 birthday、certificate_type、certificate_no。
  • 伺服器:Windows 2003,Intel Xeon 5140 2.33GHz ×4,4GB RAM。
  • 測試 SQL:SELECT COUNT(f_extract_birthday(...)) FROM ta_customer WHERE ROWNUM < 2000000

測試結果

關鍵發現

  1. 自訂函數平均比內建運算慢 2-7 倍,主因是每行觸發 PL/SQL→SQL 引擎切換。
  2. 版本 2 使用 CAST(... AS NUMBER) 捕捉例外判斷數字,例外處理機制極其昂貴,比逐字元檢核更慢 3 倍。
  3. 同樣邏輯若改寫為純 SQL(REGEXP_LIKE、SUBSTR、CASE),可完全避免切換開銷。
  4. 優化策略

    1. 能用單一 SQL 解決,絕不寫函數:將邏輯內聯至查詢或 View。
    2. 必須用函數時:
      • 加上 DETERMINISTIC 關鍵字(相同輸入必得相同輸出),啟用結果快取。
      • 使用 PRAGMA UDF (12c+) 減少呼叫開銷。
      • 避免函數內執行 SQL、迴圈、例外處理。
    3. 批次處理改用 FORALL + BULK COLLECT,最小化引擎切換次數。
    4. 大量資料轉換/驗證:考慮 INSERT /*+ APPEND */ + CTAS 純 SQL 完成,再交換表名。

    函數版本 1 核心邏輯(參考)

    FUNCTION f_extract_birthday_v1(p_type VARCHAR2, p_no VARCHAR2, p_bday DATE) RETURN NUMBER IS
      v_year NUMBER;
    BEGIN
      IF p_bday IS NOT NULL THEN RETURN EXTRACT(YEAR FROM p_bday); END IF;
      IF p_type <> '0' AND p_type <> '1' THEN RETURN NULL; END IF; -- 非身分證
      -- 身分證號解析邏輯...
      RETURN v_year;
    END;

    結論:PL/SQL 函數是邏輯封裝利器,但高頻逐行呼叫是效能殺手。架構設計時應優先考慮純 SQL 方案,將函數保留給低頻、複雜、不可 SQL 化的業務邏輯。

    copyright © 萬盛學電腦網 all rights reserved
測試項目耗時 (秒)倍數
純字串串接 cert_type || cert_no || birthday8.561.0x (基準)
自訂函數版本 1(逐字元檢核)19.842.3x
自訂函數版本 2(CAST + EXCEPTION 捕捉)57.956.8x