Oracle PL/SQL 性能优化

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

---

1. 概述

PL/SQL 性能优化技术[1]：

技术：

• BULK COLLECT / FORALL
• Native Compilation
• Function Result Cache
• Inline
• DETERMINISTIC

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

---

2. BULK COLLECT

2.1 减少 SQL/PLSQL 切换

-- 慢：单行
FOR rec IN (SELECT * FROM employees) LOOP
  ...
END LOOP;

-- 快：BULK
DECLARE
  TYPE emp_array IS TABLE OF employees%ROWTYPE;
  v_emps emp_array;
BEGIN
  SELECT * BULK COLLECT INTO v_emps FROM employees;
  
  FOR i IN 1..v_emps.COUNT LOOP
    ...
  END LOOP;
END;
/

2.2 LIMIT

DECLARE
  CURSOR c IS SELECT * FROM employees;
  TYPE emp_array IS TABLE OF employees%ROWTYPE;
  v_emps emp_array;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v_emps LIMIT 1000;
    EXIT WHEN v_emps.COUNT = 0;
    -- 处理
  END LOOP;
  CLOSE c;
END;
/

详细见：Oracle BULK COLLECT 与 FORALL。

---

3. FORALL

-- 慢：FOR LOOP
FOR i IN 1..v_ids.COUNT LOOP
  UPDATE employees SET salary = ... WHERE id = v_ids(i);
END LOOP;

-- 快：FORALL
FORALL i IN 1..v_ids.COUNT
  UPDATE employees SET salary = v_sals(i) WHERE id = v_ids(i);

---

4. Native Compilation

4.1 启用

-- 参数
ALTER SYSTEM SET plsql_code_type = NATIVE;

4.2 过程

CREATE OR REPLACE PROCEDURE fast_proc COMPILE NATIVE AS
BEGIN
  ...
END;
/

-- 或
ALTER PROCEDURE fast_proc COMPILE NATIVE;

4.3 适合

• 密集计算
• PL/SQL 重逻辑

---

5. Function Result Cache

5.1 创建

CREATE OR REPLACE FUNCTION get_dept_name(p_id NUMBER) 
RETURN VARCHAR2 RESULT_CACHE IS
  v_name VARCHAR2(100);
BEGIN
  SELECT dept_name INTO v_name FROM departments WHERE id = p_id;
  RETURN v_name;
END;
/

5.2 RELIES_ON

CREATE OR REPLACE FUNCTION get_dept_name(p_id NUMBER) 
RETURN VARCHAR2 RESULT_CACHE RELIES_ON (departments) IS
  ...

5.3 管理

EXEC DBMS_RESULT_CACHE.FLUSH;
EXEC DBMS_RESULT_CACHE.MEMORY_REPORT;

详细见：Oracle Result Cache 结果缓存。

---

6. DETERMINISTIC

CREATE OR REPLACE FUNCTION add_tax(p_amount NUMBER) 
RETURN NUMBER DETERMINISTIC IS
BEGIN
  RETURN p_amount * 1.1;
END;
/

-- 可用于 function-based index
CREATE INDEX idx_amount ON orders(add_tax(amount));

---

7. Inline

7.1 PRAGMA INLINE

CREATE OR REPLACE PROCEDURE outer_proc IS
  PROCEDURE inner_proc IS
  BEGIN
    ...
  END;
BEGIN
  PRAGMA INLINE(inner_proc, 'YES');
  inner_proc;
END;
/

7.2 自动

ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL';
-- 编译器自动 inline

---

8. SQL 调用减少

8.1 单条 SQL

-- 慢：多次 SQL
FOR rec IN (SELECT * FROM t) LOOP
  SELECT ... INTO ... FROM ...;
END LOOP;

-- 快：JOIN
SELECT ... FROM t1, t2 WHERE t1.id = t2.t1_id;

8.2 批量

- BULK COLLECT
- FORALL
- 减少 SQL/PLSQL 切换

---

9. 数据类型

9.1 PLS_INTEGER

-- 比 NUMBER 快
v_count PLS_INTEGER := 0;

9.2 SIMPLE_INTEGER

-- 11g+，最快
v_count SIMPLE_INTEGER := 0;

9.3 避免

- 隐式转换
- 字符与数字

---

10. NOCOPY

10.1 OUT 参数

CREATE OR REPLACE PROCEDURE process(
  p_in IN NUMBER,
  p_out OUT NOCOPY VARCHAR2
) IS
BEGIN
  ...
END;
/

10.2 优势

- 引用传递
- 避免复制
- 大集合

---

11. 避免递归

-- 慢：递归
FUNCTION fib(n NUMBER) RETURN NUMBER IS
BEGIN
  IF n < 2 THEN RETURN n; END IF;
  RETURN fib(n-1) + fib(n-2);
END;

-- 快：迭代
FUNCTION fib(n NUMBER) RETURN NUMBER IS
  a NUMBER := 0;
  b NUMBER := 1;
BEGIN
  FOR i IN 1..n LOOP
    a := a + b;
    b := a - b;
  END LOOP;
  RETURN a;
END;

---

12. 异常处理

12.1 异常开销

- 异常处理开销大
- 避免用异常做控制流

12.2 检查

-- 推荐
IF v_count > 0 THEN
  SELECT ... INTO ...;
END IF;

-- 避免
BEGIN
  SELECT ... INTO ...;
EXCEPTION
  WHEN NO_DATA_FOUND THEN NULL;
END;

---

13. Profiling

13.1 DBMS_PROFILER

EXEC DBMS_PROFILER.START_PROFILER('test');

-- 执行代码

EXEC DBMS_PROFILER.STOP_PROFILER;

-- 查看
SELECT * FROM plsql_profiler_data;

13.2 DBMS_HPROF

EXEC DBMS_HPROF.START_PROFILING('PROF_DIR', 'test.trc');

-- 执行

EXEC DBMS_HPROF.STOP_PROFILING;

-- 分析
SELECT * FROM TABLE(DBMS_HPROF.ANALYZE('PROF_DIR', 'test.trc'));

---

14. 监控

14.1 执行时间

SET TIMING ON;
EXEC my_proc;

14.2 PL/SQL 时间

SELECT 
  sql_id, 
  executions, 
  elapsed_time, 
  plsql_exec_time
FROM v$sql
WHERE ...;

---

15. 性能对比

技术	加速	适用
BULK COLLECT	10-100x	大数据
FORALL	10x+	DML 批量
Native	1.5-3x	计算
Result Cache	100x+	只读
Inline	1.2-2x	小过程
NOCOPY	大对象	OUT 参数

---

16. 常见坑与排错

16.1 内存

- BULK COLLECT LIMIT
- Result Cache 大小
- 监控 PGA

16.2 Result Cache 一致性

- RELIES_ON
- 数据修改失效
- 监控

16.3 Native 部署

- 需要编译
- 共享库
- 部署复杂

---

17. 最佳实践

1. BULK COLLECT + FORALL：核心
2. LIMIT 控制：内存
3. Result Cache：只读
4. DETERMINISTIC：纯函数
5. Native：计算密集
6. NOCOPY：大对象
7. PLS_INTEGER：数字
8. 避免 SQL 循环：单 SQL
9. Profiling：定位
10. 测试：基准

---

18. 参考资料

[1] Oracle Database PL/SQL Language Reference 19c, “Tuning PL/SQL” https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/tuning-pl-sql-applications.html