Oracle PL/SQL 性能诊断

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

1. 概述

PL/SQL 性能诊断工具[1]：

DBMS_PROFILER
DBMS_HPROF
DBMS_TRACE
v$ 视图

2. DBMS_PROFILER

2.1 安装

@?/rdbms/admin/proftab.sql
@?/rdbms/admin/dbmsppr.sql

2.2 使用

BEGIN
  DBMS_PROFILER.START_PROFILER('my_run');
  
  -- 执行 PL/SQL
  my_proc;
  
  DBMS_PROFILER.STOP_PROFILER;
END;
/

2.3 查看结果

-- 运行
SELECT runid, run_owner, run_date FROM plsql_profiler_runs;

-- 单元
SELECT unit_number, unit_type, unit_name FROM plsql_profiler_units WHERE runid = 1;

-- 行
SELECT 
  u.unit_name,
  d.line#,
  d.total_occur,
  d.total_time,
  s.text
FROM plsql_profiler_data d
JOIN plsql_profiler_units u ON d.runid = u.runid AND d.unit_number = u.unit_number
LEFT JOIN user_source s ON u.unit_name = s.name AND d.line# = s.line
WHERE d.runid = 1
ORDER BY d.total_time DESC;

3. DBMS_HPROF（11g+）

3.1 创建目录

CREATE DIRECTORY prof_dir AS '/u01/prof';

3.2 启用

BEGIN
  DBMS_HPROF.START_PROFILING('PROF_DIR', 'prof.txt');
  
  -- 执行 PL/SQL
  my_proc;
  
  DBMS_HPROF.STOP_PROFILING;
END;
/

3.3 分析

-- 创建分析表
@?/rdbms/admin/dbmshptab.sql

-- 分析
BEGIN
  DBMS_HPROF.ANALYZE('PROF_DIR', 'prof.txt');
END;
/

-- 查看
SELECT * FROM dbmshp_function_info ORDER BY function_elapsed_time DESC;

SELECT * FROM dbmshp_parent_child_info;

3.4 优势

层次化
函数调用树
详细时间

4. DBMS_TRACE

4.1 启用

ALTER SESSION SET PLSQL_DEBUG = TRUE;

BEGIN
  DBMS_TRACE.SET_PLSQL_TRACE(DBMS_TRACE.TRACE_ALL_CALLS);
  -- DBMS_TRACE.TRACE_ALL_SQL
  -- DBMS_TRACE.TRACE_ALL_EXCEPTIONS
  
  -- 执行
  my_proc;
  
  DBMS_TRACE.CLEAR_PLSQL_TRACE;
END;
/

4.2 查看 trace

# trace 文件
ls $ORACLE_BASE/diag/rdbms/$DB_UNIQUE_NAME/$ORACLE_SID/trace/*.trc

5. PL/SQL 性能问题

5.1 慢查询

-- 1. 在 PL/SQL 中找慢 SQL
SELECT sql_id, elapsed_time, executions
FROM v$sql
WHERE parsing_schema_name = 'SCOTT'
ORDER BY elapsed_time DESC;

5.2 循环开销

-- 慢：循环 SQL
FOR rec IN cur LOOP
  UPDATE ... WHERE id = rec.id;
END LOOP;

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

5.3 上下文切换

-- 减少 SQL/PL/SQL 切换
-- BULK COLLECT + FORALL

5.4 内存

-- 集合过大
-- LIMIT 限制
FETCH c BULK COLLECT INTO v LIMIT 1000;

6. 诊断脚本

6.1 测量时间

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;

6.2 测量 CPU

DECLARE
  v_cpu_start NUMBER;
  v_cpu_end NUMBER;
BEGIN
  v_cpu_start := DBMS_UTILITY.GET_CPU_TIME;
  
  -- 代码
  
  v_cpu_end := DBMS_UTILITY.GET_CPU_TIME;
  DBMS_OUTPUT.PUT_LINE('CPU: ' || (v_cpu_end - v_cpu_start) || ' cs');
END;

7. 优化点

7.1 BULK COLLECT + FORALL

-- 批量
SELECT ... BULK COLLECT INTO v LIMIT 1000;
FORALL i IN 1..v.COUNT
  INSERT INTO ... VALUES v(i);

7.2 NOCOPY

PROCEDURE process(p_data IN OUT NOCOPY BIG_TYPE) IS ...

7.3 绑定变量

EXECUTE IMMEDIATE 'SELECT ... WHERE id = :1' USING v_id;

7.4 PLS_INTEGER

v_count PLS_INTEGER := 0;

7.5 NATIVE 编译

ALTER SYSTEM SET plsql_code_type = NATIVE SCOPE=SPFILE;

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

8. 内存分析

8.1 PL/SQL 内存

SELECT 
  name, 
  value / 1024 / 1024 AS mb
FROM v$mystat s, v$statname n
WHERE s.statistic# = n.statistic#
  AND n.name LIKE '%PL/SQL%';

8.2 集合内存

-- 监控集合大小
-- 避免过大

9. AWR 中的 PL/SQL

-- Top PL/SQL
SELECT 
  plsql_entry_object_id,
  plsql_entry_subprogram_id,
  COUNT(*) AS executions
FROM dba_hist_active_sess_history
WHERE snap_id BETWEEN 100 AND 110
  AND plsql_entry_object_id IS NOT NULL
GROUP BY plsql_entry_object_id, plsql_entry_subprogram_id
ORDER BY executions DESC;

10. 常见坑与排错

10.1 慢代码定位

-- 1. DBMS_HPROF
-- 2. 找热点函数
-- 3. 优化

10.2 内存泄漏

-- 1. 集合未释放
-- 2. 临时 LOB 未释放
-- 3. 监控

10.3 上下文切换多

-- 1. BULK
-- 2. 减少 SQL
-- 3. 集合操作

11. 最佳实践

DBMS_HPROF 定位：精准
BULK COLLECT + FORALL：性能
绑定变量：减少解析
NOCOPY 大集合：避免复制
PLS_INTEGER：整数运算
NATIVE 编译：高性能
循环外 SQL：减少切换
LIMIT 内存控制：避免过大
定期测量：基线
优化热点：效益

12. 参考资料

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