Oracle存储过程和PL/SQL代码常常包含了多个子过程和SQL，对于单个的SQL，我们很容易分析他的执行效率，但是对于PL/SQL存储过程，我们如何分析它的执行效率、具体每一步的耗时情况呢?Oracle提供了profiler工具可以比较好的解决这个问题。

一、使用前的准备工作

1、检查DBMS_PROFILER包是否安装

desc dbms_profiler;

如果没有安装，在SYS用户下执行Oracle主目录里的脚本进行安装

@?/rdbms/admin/profload.sql

2、第一次使用需要创建用于存放统计数据的表

以哪个用户执行PL/SQL，就以哪个用户登陆来创建表。执行以下脚本，会创建三个表plsql_profiler_data、plsql_profiler_runs、plsql_profiler_units

@?/rdbms/admin/proftab.sql

二、运行测试代码

对需要分析的存储过程和PL/SQL代码执行以下包裹测试，填入执行存储过程需要的参数

declare

err number;

emp_no varchar2(20) := '91001';

line_id number := 100;

station_id number := 1040;

res varchar2(100);

begin

err := 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_id

col run_comment for a50

select * 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) text

from (select u.unit_name,

d.total_occur occured,

d.total_time,

d.line#

from plsql_profiler_units u, plsql_profiler_data d

where d.runid = u.runid

and d.unit_number = u.unit_number

and d.total_occur > 0

and u.runid = &run_id) p,

user_source s

where 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.sql

RUNID RUN_DATE RUN_COMMENT

---------- ----------------------- --------------------------------------------------------------------------------

1 02-9月 -19 10:01:31 Test Script - 2019/9/2 10:01:31

2 02-9月 -19 10:15:45 Test Script - 2019/9/2 10:15:44

3 02-9月 -19 12:54:08 2019-09-02 12:54:08

已用时间: 00: 00: 00.03

Usage:

sqlplus apps/<pwd>

SQL> START profiler.sql <runid>

输入 1 的值: 3

生成的报告类似如下形式

附：相关脚本

profiler.zip