Oracle存储过程和PL/SQL代码常常包含了多个子过程和SQL，对于单个的SQL，我们很容易分析他的执行效率，但是对于PL/SQL存储过程，我们如何分析它的执行效率、具体每一步的耗时情况呢?Oracle提供了profiler工具可以比较好的解决这个问题。一、使用前的准备工作1、检查DBMS_PROFILER包是否安装desc dbms_profiler;如果没有安装，在SYS用户下执行Oracle主目录里的脚本进行安装@?/rdbms/admin/profload.sql2、第一次使用需要创建用于存放统计数据的表以哪个用户执行PL/SQL，就以哪个用户登陆来创建表。执行以下脚本，会创建三个表plsql_profiler_data、plsql_profiler_runs、plsql_profiler_units@?/rdbms/admin/proftab.sql二、运行测试代码对需要分析的存储过程和PL/SQL代码执行以下包裹测试，填入执行存储过程需要的参数declareerr number;emp_no varchar2(20) := '91001';line_id number := 100;station_id number := 1040;res varchar2(100);beginerr := dbms_profiler.start_profiler(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')); -- 任意的注释参数-- 需要执行的存储过程和PL/SQL代码M090_NS_CHK_EMP(emp_no, line_id, station_id, res);err := dbms_profiler.stop_profiler;end;/三、查询结果找到最近一次执行的run_idcol run_comment for a50select * from (select runid, to_char(run_date, 'yyyy-mm-dd hh24:mi:ss') run_date, run_comment from plsql_profiler_runs order by runid desc) where rownum = 1;RUNID RUN_DATE RUN_COMMENT---------- ------------------- --------------------------------------------------3 2019-09-02 12:54:08 2019-09-02 12:54:08根据run_id查询分析结果select p.unit_name,p.occured,p.total_time,p.line# line,substr(s.text, 1, 100) textfrom (select u.unit_name,d.total_occur occured,d.total_time,d.line#from plsql_profiler_units u, plsql_profiler_data dwhere d.runid = u.runidand d.unit_number = u.unit_numberand d.total_occur > 0and u.runid = &run_id) p,user_source swhere p.unit_name = s.name(+)and p.line# = s.line(+)order by p.unit_name, p.line#;
由此可以看到存储过程及其各个子过程中每一步的消耗时间以及执行次数。四、生成报告为了以更好的形式展示分析结果，可以执行以下脚本生成查询分析报告@C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\profiler.sqlRUNID RUN_DATE RUN_COMMENT---------- ----------------------- --------------------------------------------------------------------------------1 02-9月 -19 10:01:31 Test Script - 2019/9/2 10:01:312 02-9月 -19 10:15:45 Test Script - 2019/9/2 10:15:443 02-9月 -19 12:54:08 2019-09-02 12:54:08已用时间: 00: 00: 00.03Usage:sqlplus apps/<pwd>SQL> START profiler.sql <runid>输入 1 的值: 3生成的报告类似如下形式
附：相关脚本
profiler.zip
