如何定位存储过程的性能热点
一个跑了几分钟的存储过程，内部有几十个子程序调用，瓶颈在哪个函数、哪一行？常规手段要么靠 dbms_output 在各处埋点（改代码、反复重跑），要么上 10046 跟踪（看到的多是 SQL 层的信息，PL/SQL 自身的函数调用层级看不清）。Oracle 在 11g 给出的专用工具是 PL/SQL 层次化剖析器：DBMS_HPROF 包。它记录每一次函数调用的层级关系与耗时，结果落库，一条 SQL 就能排出热点榜单，不需要修改一行业务代码。
与老的 DBMS_PROFILER 相比，DBMS_HPROF 的优势在于层次化：不只是每个子程序各自耗了多少时间，还包含谁调用了谁、每个调用边（caller→callee）的次数与子树耗时，方便顺着调用链把时间归属到分支上。
一、使用前提
以 11.2 到 12.x 的经典用法为例（18c 起 API 有调整，见文末注意事项），使用前需要准备三件事：
1. 目录对象。剖析的原始数据写到一个操作系统文件，location 参数传的是目录对象名：
SQL > create directory hprof_dir as '/home/oracle/hprof' ;
Directory created.
SQL > grant read , write on directory hprof_dir to test ;
Grant succeeded.
2. 结果表。以执行分析的用户身份运行安装脚本，创建 DBMSHP_RUNS、DBMSHP_FUNCTION_INFO、DBMSHP_PARENT_CHILD_INFO 三张表：
SQL > connect test/oracle
Connected.
SQL > @?/rdbms/admin/dbmshptab .sql
注意该脚本重复执行时会先删除已有的三张表，历史剖析结果会丢失。
3. 包权限。若提示无执行权限，由 DBA 授权 grant execute on dbms_hprof to test;。
三个核心子程序的签名：
DBMS_HPROF .START_PROFILING (
location IN VARCHAR2 DEFAULT NULL , -- 目录对象
filename IN VARCHAR2 DEFAULT NULL , -- 原始数据文件名
max_depth IN PLS_INTEGER DEFAULT NULL ) ; -- 最大调用深度
DBMS_HPROF .STOP_PROFILING ;
DBMS_HPROF .ANALYZE (
location IN VARCHAR2 ,
filename IN VARCHAR2 ,
summary_mode IN BOOLEAN DEFAULT FALSE ,
run_comment IN VARCHAR2 DEFAULT NULL )
RETURN NUMBER ; -- 返回 runid
ANALYZE 读取原始文件、把结果装载进三张表，返回的 runid 是后续查询的钥匙。summary_mode 传 TRUE 时把同一调用边的多次信息聚合，通常 FALSE 即可 。
二、实测：找出三个过程中谁最耗时
1. 制造一个有明显热点的被测程序（TEST 用户）
SQL > create or replace procedure p_fast as
2 n number : = 0 ;
3 begin
4 for i in 1 .. 1000000 loop
5 n : = n + 1 ;
6 end loop ;
7 end ;
8 /
Procedure created.
SQL > create or replace procedure p_slow as
2 n number : = 0 ;
3 begin
4 for i in 1 .. 4000000 loop
5 n : = n + 1 ;
6 end loop ;
7 end ;
8 /
Procedure created.
SQL > create or replace procedure p_main as
2 begin
3 p_fast ;
4 p_slow ;
5 end ;
6 /
Procedure created.
2. 包裹剖析启动与停止
SQL > begin
2 dbms_hprof .start_profiling ( 'HPROF_DIR' , 'p_main.trc' ) ;
3 p_main ;
4 dbms_hprof .stop_profiling ;
5 end ;
6 /
PL/SQL procedure successfully completed.
START 与 STOP 之间的所有 PL/SQL 调用（含其间执行的 SQL）都被记录，所以剖析窗口要恰好罩住目标程序，不要把无关操作圈进来。
3. 装载分析结果
SQL > declare
2 l_runid number ;
3 begin
4 l_runid : = dbms_hprof .analyze (location => 'HPROF_DIR' ,
5 filename => 'p_main.trc' ,
6 run_comment => 'p_main 剖析' ) ;
7 dbms_output .put_line ( 'runid = ' || l_runid ) ;
8 end ;
9 /
runid = 1
PL/SQL procedure successfully completed.
4. 查询热点榜单
SQL > select pc .function_elapsed_time / 1000000 secs ,
2 pc .calls ,
3 fi .function ,
4 fi .line#
5 from dbmshp_parent_child_info pc
6 join dbmshp_function_info fi
7 on fi .runid = pc .runid
8 and fi .symbolid = pc .childsymid
9 where pc .runid = 1
10 order by pc .function_elapsed_time desc ;
SECS CALLS FUNCTION LINE #
---------- ---------- ------------------------------ ----------
.0864 1 P_SLOW 4
.0211 1 P_FAST 4
.0000 1 __static_sql_exec_line5 0
...
P_SLOW 的自耗时间约为 P_FAST 的四倍，与两者循环次数 400 万对 100 万的比例吻合，热点一目了然。时间列单位为微秒，除以 1000000 得到秒。两表按 runid 加 symbolid 关联，SUBTREE_ELAPSED_TIME 则给出含所有下级调用的子树耗时；用 PARENTSYMID/CHILDSYMID 还原层级后，配合 CONNECT BY 可以打印整棵调用树，日常排查中按 FUNCTION_ELAPSED_TIME 排序的数据已经够用 。
真实排查时，LINE# 直接指向子程序内的调用行，再对照 USER_SOURCE 的行号即可落到最后修改的那几行代码上——这就是"不改代码定位热点行"的完整路径 。
三、注意事项
-
版本差异：以上是 11.2–12.x 的经典 API。18c 起 API 改版：START_PROFILING 不再需要目录和文件名，原始数据写入库内的 DBMSHP_TRACE_DATA 表并返回 trace_id，ANALYZE 改为接收 trace_id，安装脚本 dbmshptab.sql 被 DBMS_HPROF.CREATE_TABLES 过程取代（脚本标记为废弃）。新版环境上先确认版本再选对应流程；
-
dbmshptab.sql 重复执行会重建三张结果表，多次剖析的结果集中在同一用户 schema 下时，注意保留 runid 清单；
-
剖析有少量开销，但量级不大；生产环境上建议在低峰对目标模块做短窗口剖析，不要长期开启；
-
max_depth 可以限制采集深度，超深的递归调用只记到该层，时间归属到祖先函数，递归型程序可用来控制结果规模；
-
原始 .trc 文件本身是可读的文本格式，直接翻看也能获得调用顺序的直观印象，分析表负责精确统计。
PL/SQL 性能问题的排查路径里，DBMS_HPROF 补上了"函数级定位"这一环：SQL 慢交给 SQL 跟踪和执行计划，PL/SQL 自身慢，就用它把时间归属到调用树的每一个分支上。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/70013625/viewspace-3186997/ ，如需转载，请注明出处，否则将追究法律责任。
