Oracle PL/SQL 性能调优案例

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

---

1. 概述

PL/SQL 性能调优案例汇总[1]：

详细见：Oracle PL/SQL 性能优化详解、Oracle SQL 性能调优案例。

---

2. 案例 1：循环 DML

2.1 问题

-- 慢（每行 SQL 上下文切换）
CREATE OR REPLACE PROCEDURE slow_raise IS
BEGIN
  FOR rec IN (SELECT id, salary FROM employees) LOOP
    UPDATE employees SET salary = rec.salary * 1.1 WHERE id = rec.id;
  END LOOP;
  COMMIT;
END;
/
-- 10000 行：30 秒

2.2 解决：FORALL

CREATE OR REPLACE PROCEDURE fast_raise IS
  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;
  
  FORALL i IN 1..v_ids.COUNT
    UPDATE employees SET salary = v_sals(i) * 1.1 WHERE id = v_ids(i);
  
  COMMIT;
END;
/
-- 10000 行：0.5 秒

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

---

3. 案例 2：拼接 SQL

3.1 问题

-- 慢（每次 hard parse）
CREATE OR REPLACE PROCEDURE slow_query(p_id NUMBER) IS
  v_name VARCHAR2(100);
BEGIN
  EXECUTE IMMEDIATE 'SELECT name FROM employees WHERE id = ' || p_id
    INTO v_name;
END;
/
-- 10000 次调用：5 秒

3.2 解决：绑定变量

CREATE OR REPLACE PROCEDURE fast_query(p_id NUMBER) IS
  v_name VARCHAR2(100);
BEGIN
  EXECUTE IMMEDIATE 'SELECT name FROM employees WHERE id = :id'
    INTO v_name USING p_id;
END;
/
-- 10000 次调用：0.2 秒

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

---

4. 案例 3：递归查询

4.1 问题

-- 慢（递归计算斐波那契）
CREATE OR REPLACE FUNCTION fib(n NUMBER) RETURN NUMBER IS
BEGIN
  IF n <= 1 THEN RETURN n;
  ELSE RETURN fib(n - 1) + fib(n - 2);
  END IF;
END;
/
-- fib(30)：长时间

4.2 解决：迭代 + 缓存

CREATE OR REPLACE FUNCTION fib_fast(n NUMBER) RETURN NUMBER IS
  TYPE num_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  v_cache num_tab;
  
  FUNCTION calc(n NUMBER) RETURN NUMBER IS
  BEGIN
    IF n <= 1 THEN RETURN n; END IF;
    IF NOT v_cache.EXISTS(n) THEN
      v_cache(n) := calc(n - 1) + calc(n - 2);
    END IF;
    RETURN v_cache(n);
  END;
BEGIN
  RETURN calc(n);
END;
/
-- fib(30)：瞬间

---

5. 案例 4：全表扫描

5.1 问题

-- 慢（全表扫描）
SELECT * FROM employees WHERE UPPER(name) = 'SMITH';
-- 100 万行：5 秒

5.2 解决：函数索引

CREATE INDEX idx_upper_name ON employees(UPPER(name));

SELECT * FROM employees WHERE UPPER(name) = 'SMITH';
-- 100 万行：0.01 秒

详细见：Oracle 索引优化策略详解。

---

6. 案例 5：DISTINCT 慢

6.1 问题

-- 慢（DISTINCT 排序）
SELECT DISTINCT dept_id FROM employees;
-- 排序开销

6.2 解决：GROUP BY / EXISTS

-- GROUP BY
SELECT dept_id FROM employees GROUP BY dept_id;

-- EXISTS
SELECT d.id FROM departments d 
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

---

7. 案例 6：UNION

7.1 问题

-- 慢（UNION 排序去重）
SELECT id, name FROM employees WHERE dept_id = 10
UNION
SELECT id, name FROM employees WHERE dept_id = 20;

7.2 解决：UNION ALL

-- UNION ALL（无重复）
SELECT id, name FROM employees WHERE dept_id = 10
UNION ALL
SELECT id, name FROM employees WHERE dept_id = 20;

-- 或 IN
SELECT id, name FROM employees WHERE dept_id IN (10, 20);

详细见：Oracle SQL 集合操作。

---

8. 案例 7：游标循环

8.1 问题

-- 慢（每行 FETCH）
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;
/

8.2 解决：BULK COLLECT

DECLARE
  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;
    
    FOR i IN 1..v_emp.COUNT LOOP
      -- 处理
    END LOOP;
  END LOOP;
  CLOSE c;
END;
/

详细见：Oracle PL/SQL 游标详解。

---

9. 案例 8：频繁查询

9.1 问题

-- 慢（频繁查询部门名）
CREATE OR REPLACE PROCEDURE process_emp IS
  v_dept_name VARCHAR2(100);
BEGIN
  FOR rec IN (SELECT * FROM employees) LOOP
    SELECT dept_name INTO v_dept_name FROM departments WHERE id = rec.dept_id;
    -- 处理
  END LOOP;
END;
/

9.2 解决：RESULT_CACHE

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;
/

CREATE OR REPLACE PROCEDURE process_emp IS
  v_dept_name VARCHAR2(100);
BEGIN
  FOR rec IN (SELECT * FROM employees) LOOP
    v_dept_name := get_dept_name(rec.dept_id);
    -- 处理
  END LOOP;
END;
/

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

---

10. 案例 9：大参数

10.1 问题

-- 慢（大集合复制）
CREATE OR REPLACE PROCEDURE process(p_data IN OUT big_collection) IS
BEGIN
  -- 处理
  -- 复制开销
END;
/

10.2 解决：NOCOPY

CREATE OR REPLACE PROCEDURE process(p_data IN OUT NOCOPY big_collection) IS
BEGIN
  -- 引用传递
END;
/

---

11. 案例 10：PL/SQL vs SQL

11.1 问题

-- 慢（PL/SQL 循环）
CREATE OR REPLACE PROCEDURE slow_insert IS
BEGIN
  FOR rec IN (SELECT * FROM source) LOOP
    INSERT INTO target VALUES (rec.id, rec.name);
  END LOOP;
END;
/

11.2 解决：SQL

CREATE OR REPLACE PROCEDURE fast_insert IS
BEGIN
  INSERT INTO target SELECT id, name FROM source;
END;
/

-- 或 BULK
CREATE OR REPLACE PROCEDURE bulk_insert IS
  TYPE src_tab IS TABLE OF source%ROWTYPE;
  v_src src_tab;
BEGIN
  SELECT * BULK COLLECT INTO v_src FROM source LIMIT 10000;
  
  FORALL i IN 1..v_src.COUNT
    INSERT INTO target VALUES v_src(i);
END;
/

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

---

12. 案例 11：MERGE

12.1 问题

-- 慢（SELECT + UPDATE + INSERT）
CREATE OR REPLACE PROCEDURE slow_upsert IS
  v_count NUMBER;
BEGIN
  FOR rec IN (SELECT * FROM source) LOOP
    SELECT COUNT(*) INTO v_count FROM target WHERE id = rec.id;
    IF v_count > 0 THEN
      UPDATE target SET name = rec.name WHERE id = rec.id;
    ELSE
      INSERT INTO target VALUES (rec.id, rec.name);
    END IF;
  END LOOP;
END;
/

12.2 解决：MERGE

CREATE OR REPLACE PROCEDURE fast_upsert IS
BEGIN
  MERGE INTO target t
  USING source s ON (t.id = s.id)
  WHEN MATCHED THEN UPDATE SET t.name = s.name
  WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);
END;
/

详细见：Oracle MERGE 语句详解。

---

13. 案例 12：分析函数

13.1 问题

-- 慢（自连接）
SELECT e1.name, e1.salary,
  (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id) AS dept_avg
FROM employees e1;

13.2 解决：分析函数

SELECT name, salary,
  AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employees;

详细见：Oracle 高级分析函数。

---

14. 案例 13：分页

14.1 问题

-- 慢（ROWNUM 全部）
SELECT * FROM (
  SELECT ROWNUM rn, t.* FROM (
    SELECT * FROM employees ORDER BY id
  ) t WHERE ROWNUM <= 100010
) WHERE rn > 100000;

14.2 解决：FETCH / 键集

-- FETCH（12c+）
SELECT * FROM employees ORDER BY id
OFFSET 100000 ROWS FETCH NEXT 10 ROWS ONLY;

-- 键集
SELECT * FROM employees 
WHERE id > :last_id 
ORDER BY id 
FETCH FIRST 10 ROWS ONLY;

详细见：Oracle 12c 新 SQL 特性。

---

15. 性能调优流程

15.1 识别

- AWR TOP SQL
- ASH
- 用户反馈
- 监控

15.2 分析

- 执行计划
- 统计信息
- 等待事件
- SQL 文本

15.3 优化

- 索引
- SQL 重写
- BULK
- 绑定变量
- Hint
- Profile / Baseline

15.4 验证

- 性能对比
- 业务测试
- 监控

详细见：Oracle SQL 调优最佳实践。

---

16. 工具

- AWR
- ASH
- SQL Monitor
- DBMS_PROFILER
- DBMS_HPROF
- TKPROF

---

17. 最佳实践

1. BULK COLLECT / FORALL：批量
2. 绑定变量：减少解析
3. SQL 优先：高效
4. MERGE：UPSERT
5. 分析函数：复杂
6. FETCH：分页
7. NOCOPY：大参数
8. RESULT_CACHE：缓存
9. Profiler：定位
10. 测试：验证

---

18. 参考资料

[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