DB / Oracle / SQL 开发

Oracle PL/SQL 包性能优化详解

2026.09.06

Oracle PL/SQL 包性能优化详解

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

PL/SQL 包性能优化策略[1]：

详细见：Oracle PL/SQL 包设计与最佳实践、Oracle PL/SQL 性能优化详解。

2. 包设计

2.1 模块化

-- 相关功能放一起
CREATE OR REPLACE PACKAGE emp_pkg AS
  -- CRUD
  PROCEDURE hire_emp(...);
  PROCEDURE update_emp(...);
  PROCEDURE delete_emp(...);
  FUNCTION get_emp(...) RETURN ...;
  
  -- 查询
  FUNCTION get_by_dept(...) RETURN ...;
  FUNCTION get_by_manager(...) RETURN ...;
END;
/

2.2 公私分明

CREATE OR REPLACE PACKAGE emp_pkg AS
  -- 公共
  PROCEDURE hire_emp(...);
END;
/

CREATE OR REPLACE PACKAGE BODY emp_pkg AS
  v_count NUMBER;  -- 私有
  
  FUNCTION validate(...) RETURN BOOLEAN IS ...  -- 私有
  
  PROCEDURE hire_emp(...) IS
  BEGIN
    IF validate(...) THEN
      ...
    END IF;
  END;
END;
/

详细见：Oracle PL/SQL 包重载与封装详解。

3. RESULT_CACHE

3.1 函数

CREATE OR REPLACE PACKAGE dept_pkg AS
  FUNCTION get_name(p_id NUMBER) RETURN VARCHAR2
    RESULT_CACHE RELIES_ON (departments);
END;
/

CREATE OR REPLACE PACKAGE BODY dept_pkg AS
  FUNCTION get_name(p_id NUMBER) RETURN VARCHAR2
    RESULT_CACHE RELIES_ON (departments)
  IS
    v_name VARCHAR2(100);
  BEGIN
    SELECT name INTO v_name FROM departments WHERE id = p_id;
    RETURN v_name;
  END;
END;
/

3.2 优势

- 跨会话共享
- 自动失效
- 大幅减少 SQL

详细见：Oracle PL/SQL 性能优化详解。

4. 包级缓存

4.1 会话级

CREATE OR REPLACE PACKAGE emp_cache AS
  TYPE emp_tab IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
  v_cache emp_tab;
  
  FUNCTION get_emp(p_id NUMBER) RETURN employees%ROWTYPE;
END;
/

CREATE OR REPLACE PACKAGE BODY emp_cache AS
  FUNCTION get_emp(p_id NUMBER) RETURN employees%ROWTYPE IS
  BEGIN
    IF NOT v_cache.EXISTS(p_id) THEN
      SELECT * INTO v_cache(p_id) FROM employees WHERE id = p_id;
    END IF;
    RETURN v_cache(p_id);
  END;
END;
/

4.2 何时失效

- 数据变更
- DBMS_SESSION.MODIFY_PACKAGE_STATE
- 重编译

5. BULK

5.1 批量查询

CREATE OR REPLACE PACKAGE bulk_pkg AS
  PROCEDURE process_all;
END;
/

CREATE OR REPLACE PACKAGE BODY bulk_pkg AS
  PROCEDURE process_all IS
    CURSOR c IS SELECT * FROM employees;
    TYPE emp_tab IS TABLE OF c%ROWTYPE;
    v_emp emp_tab;
  BEGIN
    OPEN c;
    LOOP
      FETCH c BULK COLLECT INTO v_emp LIMIT 1000;
      EXIT WHEN v_emp.COUNT = 0;
      
      -- 处理
      FORALL i IN 1..v_emp.COUNT
        INSERT INTO emp_copy VALUES v_emp(i);
    END LOOP;
    CLOSE c;
  END;
END;
/

详细见：Oracle BULK COLLECT 与 FORALL 详解。

5.2 批量 DML

FORALL i IN 1..v_ids.COUNT SAVE EXCEPTIONS
  UPDATE employees SET salary = v_sals(i) WHERE id = v_ids(i);

6. Native

6.1 启用

ALTER SYSTEM SET PLSQL_CODE_TYPE = NATIVE;

6.2 重编译

ALTER PACKAGE emp_pkg COMPILE;

6.3 适合

- 计算密集
- 循环
- 数学

7. INLINE

7.1 显式

CREATE OR REPLACE PACKAGE BODY emp_pkg AS
  FUNCTION calc(p_val NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN p_val * 1.1;
  END;
  
  PROCEDURE process IS
    PRAGMA INLINE(calc, 'YES');
    v_result NUMBER;
  BEGIN
    v_result := calc(100);
  END;
END;
/

7.2 自动

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3;

8. 优化级别

9.1 级别

0：无优化
1：基本
2：默认（推荐）
3：激进（INLINE）
ALTER SYSTEM SET PLSQL_OPTIMIZE_LEVEL = 2;

9. 共享池

9.1 KEEP

EXEC DBMS_SHARED_POOL.KEEP('EMP_PKG', 'P');
-- 钉在共享池

9.2 监控

SELECT namespace, gets, gethits, pins, pinhits
FROM v$librarycache
WHERE namespace = 'PL/SQL AREA';

详细见：Oracle SGA 调优。

10. 重载

10.1 简化 API

CREATE OR REPLACE PACKAGE calc_pkg AS
  FUNCTION add(a NUMBER, b NUMBER) RETURN NUMBER;
  FUNCTION add(a VARCHAR2, b VARCHAR2) RETURN VARCHAR2;
  FUNCTION add(a DATE, b NUMBER) RETURN DATE;
END;
/

10.2 性能

- API 简洁
- 编译时绑定
- 性能不受影响

详细见：Oracle PL/SQL 包重载与封装详解。

11. 启动优化

11.1 初始化块

CREATE OR REPLACE PACKAGE BODY emp_pkg AS
BEGIN
  -- 首次引用时执行
  -- 简洁
  -- 避免复杂操作
  NULL;
END;
/

11.2 延迟加载

- 首次引用
- 一次/会话
- 简洁

12. 状态管理

12.1 SERIALLY_REUSABLE

CREATE OR REPLACE PACKAGE temp_pkg AS
  PRAGMA SERIALLY_REUSABLE;
  -- 临时状态
END;
/

12.2 重置

EXEC DBMS_SESSION.MODIFY_PACKAGE_STATE(DBMS_SESSION.REINITIALIZE);

详细见：Oracle PL/SQL 包初始化与状态管理详解。

13. 监控

13.1 Profiler

EXEC DBMS_PROFILER.START_PROFILER('test');
-- 执行包
EXEC DBMS_PROFILER.STOP_PROFILER;

SELECT u.unit_name, d.line, d.total_occur, d.total_time
FROM plsql_profiler_data d
JOIN plsql_profiler_units u ON d.runid = u.runid
WHERE d.runid = 1
ORDER BY d.total_time DESC;

13.2 DBMS_HPROF

EXEC DBMS_HPROF.START_PROFILING('DIR', 'file.txt');
-- 执行
EXEC DBMS_HPROF.STOP_PROFILING;

13.3 SQL Monitor

SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => '&sql_id') FROM dual;

详细见：Oracle PL/SQL 性能监控详解。

14. 应用场景

14.1 高频查询

- RESULT_CACHE
- 包级缓存
- 减少 SQL

14.2 批量处理

- BULK COLLECT
- FORALL
- LIMIT

14.3 计算密集

- Native
- INLINE
- 优化级别

14.4 临时处理

- SERIALLY_REUSABLE
- 内存优化

15. 常见坑与排错

15.1 包失效

- 依赖变更
- 重编译
- utlrp

15.2 内存

- 包状态累积
- SERIALLY_REUSABLE
- 重置

15.3 性能

- 循环 DML
- BULK
- 缓存

16. 最佳实践

  1. 模块化：相关
  2. 公私分明：封装
  3. RESULT_CACHE：函数
  4. 包级缓存：会话
  5. BULK：批量
  6. Native：计算
  7. INLINE：小函数
  8. 优化级别：2+
  9. KEEP：共享池
  10. 监控：Profiler

17. 参考资料

[1] Oracle Database PL/SQL Language Reference 19c, “PL/SQL Performance” https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-optimization-and-tuning.html