Oracle PL/SQL 性能优化详解

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

---

1. 概述

PL/SQL 性能优化方法[1]：

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

---

2. BULK COLLECT

2.1 批量查询

DECLARE
  TYPE emp_tab IS TABLE OF employees%ROWTYPE;
  v_emp emp_tab;
BEGIN
  -- 差（每行一次）
  -- FOR rec IN (SELECT * FROM employees) LOOP ... END LOOP;
  
  -- 好（批量）
  SELECT * BULK COLLECT INTO v_emp FROM employees;
  
  FOR i IN 1..v_emp.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_emp(i).name);
  END LOOP;
END;
/

2.2 LIMIT

DECLARE
  CURSOR c_emp IS SELECT * FROM employees;
  TYPE emp_tab IS TABLE OF employees%ROWTYPE;
  v_emp emp_tab;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp BULK COLLECT INTO v_emp LIMIT 1000;
    EXIT WHEN v_emp.COUNT = 0;
    
    -- 处理
    FOR i IN 1..v_emp.COUNT LOOP
      ...
    END LOOP;
  END LOOP;
  CLOSE c_emp;
END;
/

详细见：Oracle BULK COLLECT 与 FORALL。

---

3. FORALL

3.1 批量 DML

DECLARE
  TYPE id_tab IS TABLE OF NUMBER;
  TYPE sal_tab IS TABLE OF NUMBER;
  v_ids id_tab;
  v_sals sal_tab;
BEGIN
  SELECT id, salary BULK COLLECT INTO v_ids, v_sals FROM employees;
  
  -- 差（每行一次）
  -- FOR i IN 1..v_ids.COUNT LOOP
  --   UPDATE employees SET salary = v_sals(i) * 1.1 WHERE id = v_ids(i);
  -- END LOOP;
  
  -- 好（批量）
  FORALL i IN 1..v_ids.COUNT
    UPDATE employees SET salary = v_sals(i) * 1.1 WHERE id = v_ids(i);
END;
/

3.2 SAVE EXCEPTIONS

DECLARE
  TYPE id_tab IS TABLE OF NUMBER;
  v_ids id_tab;
BEGIN
  v_ids := id_tab(1, 2, 3, 4, 5);
  
  FORALL i IN 1..v_ids.COUNT SAVE EXCEPTIONS
    INSERT INTO t VALUES (v_ids(i));
EXCEPTION
  WHEN OTHERS THEN
    FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(
        'Index ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
        ' Code ' || SQL%BULK_EXCEPTIONS(i).ERROR_CODE
      );
    END LOOP;
END;
/

详细见：Oracle BULK COLLECT 与 FORALL。

---

4. 绑定变量

4.1 静态 SQL

-- PL/SQL 自动绑定
FOR rec IN (SELECT * FROM employees WHERE dept_id = p_dept) LOOP ...

4.2 动态 SQL

-- 差
EXECUTE IMMEDIATE 'SELECT * FROM t WHERE id = ' || v_id;

-- 好
EXECUTE IMMEDIATE 'SELECT * FROM t WHERE id = :id' USING v_id;

详细见：Oracle PL/SQL 动态 SQL 详解。

---

5. 游标

5.1 显式

DECLARE
  CURSOR c IS SELECT * FROM employees;
  v_emp c%ROWTYPE;
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO v_emp;
    EXIT WHEN c%NOTFOUND;
    ...
  END LOOP;
  CLOSE c;
END;
/

5.2 FOR

-- 更简洁
FOR rec IN (SELECT * FROM employees) LOOP
  ...
END LOOP;

详细见：Oracle PL/SQL 游标。

---

6. 集合

6.1 选择

- Associative Array：内存
- Nested Table：持久
- VARRAY：固定大小

详细见：Oracle PL/SQL 集合详解。

6.2 性能

- BULK COLLECT 到集合
- 批量处理
- 减少上下文切换

---

7. 缓存

7.1 包变量

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

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

7.2 RESULT CACHE（11g+）

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

---

8. NOCOPY

8.1 大参数

DECLARE
  TYPE big_tab IS TABLE OF VARCHAR2(1000);
  v_data big_tab;
  
  PROCEDURE process(p_data IN OUT NOCOPY big_tab) IS
  BEGIN
    ...
  END;
BEGIN
  v_data := big_tab(...);  -- 大量数据
  process(v_data);
  -- NOCOPY 避免复制
END;
/

8.2 限制

- 异常时可能不一致
- 谨慎

---

9. 函数内联

10.1 INLINE（11g+）

DECLARE
  PROCEDURE small_proc IS
  BEGIN
    ...
  END;
  
  PROCEDURE caller IS
    PRAGMA INLINE(small_proc, 'YES');
  BEGIN
    small_proc;
    small_proc;
  END;
BEGIN
  caller;
END;
/

---

10. SQL 与 PL/SQL

10.1 SQL 优先

-- 差（PL/SQL 循环）
FOR rec IN (SELECT * FROM t) LOOP
  INSERT INTO t2 VALUES (rec.id);
END LOOP;

-- 好（SQL）
INSERT INTO t2 SELECT id FROM t;

10.2 MERGE

-- 差
FOR rec IN (SELECT * FROM src) LOOP
  UPDATE dst SET ... WHERE id = rec.id;
  IF SQL%NOTFOUND THEN
    INSERT INTO dst VALUES (...);
  END IF;
END LOOP;

-- 好
MERGE INTO dst d
USING src s ON (d.id = s.id)
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;

详细见：Oracle MERGE 语句详解。

---

11. 避免递归

- 深度限制
- 改迭代
- 性能

---

12. Native Compilation

ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE;

CREATE OR REPLACE PROCEDURE heavy_compute IS
BEGIN
  ...
END;
/

---

13. Profiling

13.1 DBMS_PROFILER

EXEC DBMS_PROFILER.START_PROFILER('test');

-- 执行

EXEC DBMS_PROFILER.STOP_PROFILER;

SELECT unit_name, line, total_occur, total_time
FROM plsql_profiler_data d, plsql_profiler_units u
WHERE d.runid = ... AND d.unit_number = u.unit_number
ORDER BY total_time DESC;

13.2 DBMS_HPROF

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

-- 执行

EXEC DBMS_HPROF.STOP_PROFILING;

SELECT * FROM dbmshp_function_info;

---

14. 监控

14.1 V$DB_OBJECT_CACHE

SELECT type, name, namespace, locks, pins, executions
FROM v$db_object_cache
WHERE type LIKE '%PACKAGE%'
ORDER BY executions DESC;

14.2 V$SQL

SELECT sql_text, executions, elapsed_time, cpu_time
FROM v$sql
WHERE parsing_schema_name = 'SCOTT'
ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

---

15. AWR

@?/rdbms/admin/awrrpt.sql
-- PL/SQL TOP

详细见：Oracle AWR 详解。

---

16. 常见性能问题

16.1 上下文切换

- SQL in PL/SQL loop
- BULK COLLECT / FORALL

16.2 Hard Parse

- 拼接 SQL
- 绑定变量

16.3 大集合内存

- LIMIT
- 分批

16.4 递归

- 深度
- 改循环

---

17. 性能优化清单

问题	优化
循环 SQL	BULK
拼接 SQL	绑定
大参数	NOCOPY
频繁查询	缓存
简单函数	INLINE
重复计算	RESULT_CACHE
SQL 可替代	SQL
递归	循环
解析	Native
监控	Profiler

---

18. 最佳实践

1. BULK COLLECT/FORALL：批量
2. 绑定变量：减少解析
3. SQL 优先：高效
4. NOCOPY：大参数
5. RESULT_CACHE：缓存
6. LIMIT：内存
7. Profiler：定位
8. Native：计算密集
9. 避免递归：循环
10. 监控：优化

---

19. 参考资料

[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