Oracle PL/SQL 性能优化
Oracle PL/SQL 性能优化
适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07
1. 概述
PL/SQL 性能优化主要方向[1]：
- 减少 SQL/PL/SQL 上下文切换
- 减少硬解析
- 批量操作
- 优化循环
2. BULK COLLECT 与 FORALL
2.1 减少 SQL/PL/SQL 切换
-- 慢：循环 DML
FOR i IN 1..v_ids.COUNT LOOP
UPDATE employees SET salary = v_sal(i) WHERE id = v_ids(i);
END LOOP;
-- 快：FORALL
FORALL i IN 1..v_ids.COUNT
UPDATE employees SET salary = v_sal(i) WHERE id = v_ids(i);2.2 LIMIT 控制
DECLARE
CURSOR c IS SELECT * FROM big_table;
TYPE t IS TABLE OF big_table%ROWTYPE;
v t;
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO v LIMIT 1000;
EXIT WHEN v.COUNT = 0;
-- 处理
END LOOP;
CLOSE c;
END;详细见：Oracle BULK COLLECT 与 FORALL。
3. 绑定变量
3.1 减少硬解析
-- 慢：硬解析
FOR i IN 1..100 LOOP
EXECUTE IMMEDIATE 'SELECT * FROM emp WHERE id = ' || i;
END LOOP;
-- 快：绑定变量
FOR i IN 1..100 LOOP
EXECUTE IMMEDIATE 'SELECT * FROM emp WHERE id = :1' USING i;
END LOOP;3.2 PL/SQL 自动绑定
-- PL/SQL 变量自动绑定
SELECT * INTO v_emp FROM employees WHERE id = v_id;
-- v_id 自动绑定4. NOCOPY 参数
4.1 减少参数复制
-- 默认：值传递（复制）
PROCEDURE process(p_data IN OUT BIG_TABLE_TYPE) IS ...
-- NOCOPY：引用传递（不复制）
PROCEDURE process(p_data IN OUT NOCOPY BIG_TABLE_TYPE) IS ...4.2 优势
- 大集合性能提升
- 减少 CPU 和内存
4.3 限制
- 异常可能修改原数据
- 不能与 ROLLBACK 保证一致
5. PLS_INTEGER vs NUMBER
-- PLS_INTEGER 更快
DECLARE
v_count PLS_INTEGER := 0;
BEGIN
FOR i IN 1..1000000 LOOP
v_count := v_count + 1;
END LOOP;
END;
-- NUMBER 慢
DECLARE
v_count NUMBER := 0;
BEGIN
FOR i IN 1..1000000 LOOP
v_count := v_count + 1;
END LOOP;
END;6. SIMPLE_INTEGER（11g+）
-- 最快，不允许 NULL
DECLARE
v_count SIMPLE_INTEGER := 0;
BEGIN
FOR i IN 1..1000000 LOOP
v_count := v_count + 1;
END LOOP;
END;7. 循环优化
7.1 FOR 比 WHILE 快
-- FOR（快）
FOR i IN 1..v_count LOOP
...
END LOOP;
-- WHILE（慢）
WHILE v_idx <= v_count LOOP
...
v_idx := v_idx + 1;
END LOOP;7.2 减少循环内 SQL
-- 慢：循环内查询
FOR rec IN cur LOOP
SELECT name INTO v_name FROM dept WHERE id = rec.dept_id;
END LOOP;
-- 快：JOIN
FOR rec IN (SELECT e.*, d.name FROM emp e JOIN dept d ON ...) LOOP
...
END LOOP;8. 集合优化
8.1 选择集合类型
| 类型 | 性能 | 适合 |
|---|---|---|
| 索引表 | 最快 | 内存临时 |
| 嵌套表 | 中 | 持久 |
| VARRAY | 慢 | 固定大小 |
8.2 EXTEND 批量
-- 慢：逐个 EXTEND
FOR i IN 1..1000 LOOP
v_list.EXTEND;
v_list(i) := i;
END LOOP;
-- 快：批量 EXTEND
v_list.EXTEND(1000);
FOR i IN 1..1000 LOOP
v_list(i) := i;
END LOOP;9. PRAGMA INLINE
9.1 内联函数
-- 12c+ 自动内联
PROCEDURE process IS
PRAGMA INLINE(my_func, 'YES');
BEGIN
v := my_func(x);
END;
-- 或全局
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3;10. PRAGMA UDF
10.1 函数优化为 UDF
CREATE OR REPLACE FUNCTION calc(p_val NUMBER) RETURN NUMBER IS
PRAGMA UDF;
BEGIN
RETURN p_val * 1.1;
END;
/
-- SQL 中调用更快
SELECT calc(salary) FROM employees;11. 编译选项
11.1 NATIVE 编译
-- 修改参数
ALTER SYSTEM SET plsql_code_type = NATIVE SCOPE=SPFILE;
-- 重编译
ALTER PROCEDURE my_proc COMPILE PLSQL_CODE_TYPE=NATIVE;11.2 优化级别
-- Level 0：无优化
-- Level 1（默认）：基本
-- Level 2：中等
-- Level 3：最高
ALTER SYSTEM SET plsql_optimize_level = 3;12. 性能测量
12.1 DBMS_UTILITY.GET_TIME
DECLARE
v_start NUMBER;
v_end NUMBER;
BEGIN
v_start := DBMS_UTILITY.GET_TIME;
-- 代码
v_end := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE('Elapsed: ' || (v_end - v_start) || ' hsecs');
END;12.2 DBMS_HPROF
-- 层次化 profiler
EXEC DBMS_HPROF.START_PROFILING('PROF_DIR', 'prof.txt');
-- 代码
EXEC DBMS_HPROF.STOP_PROFILING;
-- 分析
SELECT * FROM dbmshp_runs;12.3 DBMS_PROFILER
-- 传统 profiler
EXEC DBMS_PROFILER.START_PROFILER('test');
-- 代码
EXEC DBMS_PROFILER.STOP_PROFILER;
-- 查看结果
SELECT * FROM plsql_profiler_data;13. 常见坑与排错
13.1 性能差
-- 1. 检查是否有循环 SQL
-- 2. 使用 BULK COLLECT + FORALL
-- 3. 使用绑定变量
-- 4. 使用 NOCOPY13.2 内存高
-- BULK COLLECT 无 LIMIT
-- 修复
FETCH c BULK COLLECT INTO v LIMIT 1000;13.3 编译错误
-- 查看
SHOW ERRORS PROCEDURE my_proc;
SELECT * FROM user_errors WHERE name = 'MY_PROC';14. 最佳实践
- BULK COLLECT + FORALL：批量操作
- 绑定变量：减少硬解析
- NOCOPY 大集合：避免复制
- PLS_INTEGER：整数运算
- FOR 循环：比 WHILE 快
- 循环外查询：减少 SQL
- PRAGMA UDF：SQL 调用
- NATIVE 编译：高性能
- 优化级别 3：默认
- profiler 定位瓶颈：精准
15. 参考资料
[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