最近遇到开发反馈一个执行将近2天的存储过程，需要分析慢的原因。查询和咨询了大家一些定位方法，汇总记录一下。
一、 存储过程中加入计时代码
原始的分析方法，适用于较简单、性能要求不太高的存储过程场景。在怀疑段设置计时点，不断缩小范围。
缺点：
- 需要改业务代码
- 复杂存储过程需要多次或者大量设置，可行性不高
- 需要再次执行存储过程
- 计时点设置过多可能影响存储过程本身性能
二、 dbms_profiler
用于分析Oracle存储过程中的各段代码的时间开销情况，从而快速找到性能瓶颈的步骤。 参考
Oracle 使用dbms_profiler统计存储过程每步执行时间_Hehuyi_In的博客-CSDN博客_oracle查看存储过程执行时间
缺点：
- 需要创建提前额外profiler用户和对象
- 存储过程执行前要运行dbms_profiler相关代码，只适合手动执行存储过程时分析
- 需要再次执行存储过程
三、 plsqldev工具
原理其实就是调用dbms_profiler工具，用法参考
PLSQL Developer 分析存储过程_iluckly的博客-CSDN博客
缺点基本同上，优点是展示更清晰明了
四、 ash视图
如果v$ash视图中还有数据，尽量用这个，没有的话用dba_hist_ash
原理就是ash视图有top_level_sql_id（存储过程对应的sql_id），每个sql又有sql_exec_start，可以根据执行时间定位sql_id执行时间和次数。
create table proc_run_time as
SELECT T.SQL_ID,
T.SQL_EXEC_ID,
CAST(MAX(T.SAMPLE_TIME) AS DATE) EXEC_END_TIME,
T.SQL_EXEC_START EXEC_START_TIME,
ROUND((CAST(MAX(T.SAMPLE_TIME) AS DATE) - T.SQL_EXEC_START) * 1440 * 60,
2) RUN_S,
T.SQL_PLAN_HASH_VALUE,
T.MODULE
FROM v$active_session_history T
WHERE T.top_level_sql_id = 'fvtrazmp924f7' --------- 注意这里
AND T.SAMPLE_TIME > SYSDATE - 2
GROUP BY T.SQL_ID,
T.SQL_EXEC_ID,
T.SQL_EXEC_START,
T.SQL_PLAN_HASH_VALUE,
T.MODULE
ORDER BY EXEC_END_TIME DESC再根据临时表 proc_run_time 做分析统计
select sql_id,count(*) execution,sum(run_s) elapsed_time_s
from proc_run_time
where run_s is not null and exec_start_time >= to_date('2021-11-30 20:00','yyyy-mm-dd hh24:mi')
group by sql_id
order by 3 desc
缺点：
- 当然这个统计结果没有dbms_profiler准确，但大致能看出来，并且不需要再次运行存储过程。
- 如果是嵌套多层的存储过程，可能需要循环多次执行上面步骤
- 需要自己写SQL分析，不够方便
五、 AWR
如果在存储过程执行期间别的SQL运行不多，很可能存储过程和其中最慢的具体sql都会在top 榜单上。根据sql module字段可以方便定位和优化。
缺点：
- 如果系统负载较高，不一定能看到
- 就算能看到，通常只有存储过程里最耗时的top sql，不会有完整的每个SQL执行耗时记录
六、 10046 trace
SQL>execute sys.dbms_system.set_ev(7,36,10046,12,'');
exec sql
SQL>execute sys.dbms_system.set_ev(7,36,10046,0,'');
tkprof ora_2229_10046.trc 888.trc 可以通过disk 排序之类的方式，定位TOP SQL
缺点：
- 需要再次执行一次存储过程；
- 不高效，需要对disk 操作系统文件进行一定的grep 过滤 order by 人为查询
参考：