Oracle 預存程序開發規範與效能優化實戰指南

預存程序開發的核心價值與挑戰

預存程序（Stored Procedure，SP）封裝複雜業務邏輯與資料庫操作，減少網路往返、提升安全性、便於維護。但 SP 數量龐大、缺乏規範、或忽視效能優化時，將導致系統難以維護、效能低落。經實測，優化後的 SP 效能可達未優化版本的數百倍。本文針對大數據量、高併發 OLTP 系統，提供開發規範與優化實戰指南。

一、跨資料庫存取規範

原則：跨資料庫存取必須在當前資料庫建立 View，嚴禁直接使用 database.dbo.table_name 寫法。

• sp_depends 無法追蹤跨庫相依關係，影響影響分析與重構
• View 可隔離底層結構變更，提供穩定介面
• 命名建議：V_EXT_<來源DB>_<表名>

二、提交前自我檢核清單

1. 執行計劃分析：SET SHOWPLAN ON 檢視每條 SQL 計劃，確無全表掃描、索引缺失、Cardinality 嚴重偏差
2. 查詢優化自檢：WHERE 條件順序、索引覆蓋、避免函數運算導致索引失效
3. 程式碼規範：縮排、註解、錯誤處理、交易控制

三、SQL 撰寫效能規範

3.1 交易與鎖控制

• 避免大交易：拆分批次、提交頻率平衡
• 慎用 HOLDLOCK（等同 SERIALIZABLE），嚴重降低並發
• 鎖升級門檻：超過 200 資料頁（~400KB）頁級鎖升級為表級鎖

3.2 表存取策略

• 避免重複存取同一大表：先依條件篩選至暫存表，再進行聯結
• 大表聯結小表優於大表聯結大表
• 善用 EXISTS 替代 IN / COUNT(*) 判斷存在性

3.3 遊標使用鐵律

1. 能不用就不用：集合操作（SET-based）永遠優於逐列處理（RBAR）
2. 若必須用遊標：資料量 > 1 萬列 → 必須改寫為集合操作
3. 遊標迴圈中嚴禁再做表聯結，將聯結邏輯前置到遊標查詢中
4. 優先用 FAST_FORWARD（唯讀、順向）遊標，效能最佳

3.4 WHERE 條件撰寫黃金法則

• 條件順序：依索引欄位順序、選擇性高→低
• 索引欄位不可做函數/運算：WHERE TRUNC(dt) = ... ❌ → WHERE dt >= ... AND dt < ... ✅
• 避免隱含轉換：字串比數值、日期比字串 → 導致索引失效
• >= 優於 >（邊界包含語意更清晰，優化器估算更準）
• OR 條件評估改寫為 UNION ALL（互斥集合）或 IN 列表

3.5 統計函數選擇

• COUNT(1) 優於 COUNT(*)（舊版本有差異，新版本相同，習慣用 1）
• 判斷是否存在：EXISTS (SELECT 1 ...)，嚴禁 SELECT COUNT(*) > 0

四、索引設計與維護規範

• 大型 OLTP 表索引數 ≤ 6 個（寫入效能與讀取效能平衡）
• 優先建立高選擇性欄位索引，複合索引遵循「等值在前、範圍在後」
• 聯合索引必須用到第一欄位才會被使用
• 避免大表 Table Scan：必要時建立索引、分區、Materialized View
• 定期重建索引（ALTER INDEX ... REBUILD）與重新編譯 SP（DBMS_UTILITY.COMPILE_SCHEMA）
• 監控 V$OBJECT_USAGE 識別未使用索引並移除

五、TEMPDB / 暫存表使用規範

• 減少 DISTINCT、ORDER BY、GROUP BY、HAVING、JOIN、COMPUTE 等加重 TempDB 負擔的操作
• 避免頻繁建立/刪除暫存表 → 系統表資源消耗大
• 大量資料一次性插入暫存表：SELECT INTO 避免 Log、提速；小量資料：CREATE TABLE + INSERT 緩和系統表壓力
• 暫存表需建索引時：建表與建索引放在獨立子程序，確保編譯時統計資訊正確，主程序呼叫子程序
• SP 結束時顯式清理暫存表：TRUNCATE TABLE → DROP TABLE，避免系統表長時間鎖定
• 慎用大暫存表與大表聯結/修改：單語句多次存取系統表，極易造成 TempDB 爭用

六、演算法與調優工具

• 同一業務邏輯嘗試多種演算法（集合操作 vs 遊標、JOIN vs EXISTS、分步驟 vs 单语句）
• 使用 ASE 調優指令驗證：

  SET STATISTICS IO ON;      -- 邏輯/物理讀取
  SET STATISTICS TIME ON;    -- CPU/經過時間
  SET SHOWPLAN ON;           -- 執行計劃（不執行）

• 對比 Logical Reads、Physical Reads、CPU Time、Elapsed Time 選擇最優方案

七、參數與資料型別一致性

• SP 參數名不可與資料表欄位名相同，否則 Oracle 視為欄位參考，即便用表別名也無法區分
• 參數資料型別必須與對應欄位完全一致（長度、精度、標度），避免隱含轉換導致索引失效
• 輸出參數明確宣告 OUT / IN OUT

八、錯誤處理與稽核

CREATE OR REPLACE PROCEDURE PROC_EXAMPLE (
    P_ID IN NUMBER,
    P_RESULT OUT VARCHAR2
) AS
    V_ERR_CODE NUMBER;
    V_ERR_MSG VARCHAR2(4000);
BEGIN
    -- 業務邏輯
    UPDATE TABLE_A SET COL1 = 'X' WHERE ID = P_ID;
    
    IF SQL%NOTFOUND THEN
        P_RESULT := 'NOT_FOUND';
        RETURN;
    END IF;
    
    COMMIT;
    P_RESULT := 'SUCCESS';
    
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        V_ERR_CODE := SQLCODE;
        V_ERR_MSG := SUBSTR(SQLERRM, 1, 4000);
        -- 寫入稽核表
        INSERT INTO PROC_AUDIT_LOG (PROC_NAME, ERR_CODE, ERR_MSG, CALL_TIME)
        VALUES ('PROC_EXAMPLE', V_ERR_CODE, V_ERR_MSG, SYSDATE);
        COMMIT;
        P_RESULT := 'ERROR:' || V_ERR_CODE;
END;

總結：預存程序優化檢核表

將此檢核表納入 Code Review 流程，能攔截 90% 以上的 SP 效能與維護隱患。