oracle 执行时长评估,使用profiler 评估一段plsql 代码的执行时间

有时候我们在执行一段比较长的plsql程序的时候，发现执行过程比较慢，但又不知道是哪部分出现了性能问题，这时候应该如何快速找到最耗费时间的部分呢？当然有很多方法，但这里推荐一种方法，可以使用dbms_profiler包来观察每一行代码的执行次数和时间。

一、操作步骤

1. 登录到SYS用户，检查dbms_profiler包有没有安装。没有的话运行$ORACLE_HOME/rdbms/admin/profload.sql来创建该包。

2. 将该包的执行权限赋予待调试的用户。

3. 登录到调试用户，运行$ORACLE_HOME/rdbms/admin/proftab.sql创建相关表。

4. 在待优化的PL/SQL程序前后加上运行dbms_profiler包的代码，如下：

BEGIN

DBMS_PROFILER.START_PROFILER('any comment');

...'your pl/sql code'

DBMS_PROFILER.STOP_PROFILER;

...

END;

5. 运行该PL/SQL程序，然后检查PLSQL_PROFILER_DATA表内容，可以从中看到每一行代码的执行次数，每行代码总共的执行时间。

二、具体的例子：

SQL> conn / as sysdba;

Connected.

1.检查是否安装了dbms_profiler包

SQL> desc dbms_profiler;

ERROR:

ORA-04043: object dbms_profiler does not exist

--如果没有安装的话，执行下面的语句安装

SQL> @/disk_dev/oracle/product/9.2.0/rdbms/admin/profload.sql;

Package created.

Grant succeeded.

Synonym created.

Library created.

Package body created.

Testing for correct installation

SYS.DBMS_PROFILER successfully loaded.

PL/SQL procedure successfully completed.

2.把dbms_profiler的执行权限赋于oauser

SQL> grant execute on dbms_profiler to oauser;

Grant succeeded.

3.使用oauser登录，执行proftab.sql，此文件实际是创建plsql_profiler_data、plsql_profiler_units、plsql_profiler_runs三个表，具体proftab.sql的内容如下：

proftab.sql:

drop table plsql_profiler_data cascade constraints;

drop table plsql_profiler_units cascade constraints;

drop table plsql_profiler_runs cascade constraints;

drop sequence plsql_profiler_runnumber;

create table plsql_profiler_runs

(

runid number primary key, -- unique run identifier,

-- from plsql_profiler_runnumber

related_run number, -- runid of related run (for client/

-- server correlation)

run_owner varchar2(32), -- user who started run

run_date date, -- start time of run

run_comment varchar2(2047), -- user provided comment for this run

run_total_time number, -- elapsed time for this run

run_system_info varchar2(2047), -- currently unused

run_comment1 varchar2(2047), -- additional comment

spare1 varchar2(256) -- unused

);

comment on table plsql_profiler_runs is

'Run-specific information for the PL/SQL profiler';

create table plsql_profiler_units

(

runid number references plsql_profiler_runs,

unit_number number, -- internally generated library unit #

unit_type varchar2(32), -- library unit type

unit_owner varchar2(32), -- library unit owner name

unit_name varchar2(32), -- library unit name

-- timestamp on library unit, can be used to detect changes to

-- unit between runs

unit_timestamp date,

total_time number DEFAULT 0 NOT NULL,

spare1 number, -- unused

spare2 number, -- unused

--

primary key (runid, unit_number)

);

comment on table plsql_profiler_units is

'Information about each library unit in a run';

create table plsql_profiler_data

(

runid number, -- unique (generated) run identifier

unit_number number, -- internally generated library unit #

line# number not null, -- line number in unit

total_occur number, -- number of times line was executed

total_time number, -- total time spent executing line

min_time number, -- minimum execution time for this line

max_time number, -- maximum execution time for this line

spare1 number, -- unused

spare2 number, -- unused

spare3 number, -- unused

spare4 number, -- unused

--

primary key (runid, unit_number, line#),

foreign key (runid, unit_number) references plsql_profiler_units

);

comment on table plsql_profiler_data is

'Accumulated data from all profiler runs';

create sequence plsql_profiler_runnumber start with 1 nocache;

SQL> conn oauser/oauser;

Connected.

SQL> @/disk_dev/oracle/product/9.2.0/rdbms/admin/proftab.sql;

4.创建一个测试的过程，在代码中加入dbms_profiler代码，如下：

create or replace procedure p_dbms_profiler_test

is

i number :=1 ;

v_str varchar2(4000);

begin

dbms_profiler.start_profiler('测试一下');

dbms_output.put_line(i);

for i in 1..100 loop

exit when length(v_str)>100;

v_str := v_str||i;

end loop;

dbms_output.put_line(v_str);

dbms_profiler.stop_profiler;

end p_dbms_profiler_test;

SQL> exec p_dbms_profiler_test;

PL/SQL procedure successfully completed

SQL> exec p_dbms_profiler_test;

PL/SQL procedure successfully completed

SQL> exec p_dbms_profiler_test;

PL/SQL procedure successfully completed

执行一次p_dbms_profiler_test过程，就会分别在plsql_profiler_units和plsql_profiler_runs插入一条信息，在plsql_profiler_data插入具体的过程执行数据。

5.查看结果

通过下面的SQL语句我们可以知道每一行代码的执行次数，时间等等。

SQL> select t1.line#,t3.text,t1.total_occur,t1.total_time,min_time, max_time

2 from plsql_profiler_data t1,plsql_profiler_units t2,user_source t3

3 where t1.runid = t2.runid

4 and t2.unit_name='P_DBMS_PROFILER_TEST'

5 and t2.unit_name = t3.name

6 and t1.line#=t3.line

7 and t1.runid=3

8 order by t1.line#

9 /

LINE# TEXT TOTAL_OCCUR TOTAL_TIME MIN_TIME MAX_TIME

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

3 i number :=1 ; 0 0 0 0

6 dbms_profiler.start_profiler('测试一下'); 0 0 0 0

7 dbms_output.put_line(i); 1 17000 17000 17000

8 for i in 1..100 loop 56 222000 2000 6000

9 exit when length(v_str)>100; 56 232000 3000 7000

10 v_str := v_str||i; 55 254000 3000 16000

12 dbms_output.put_line(v_str); 1 5000 5000 5000

13 dbms_profiler.stop_profiler; 1 5000 5000 5000

14 end p_dbms_profiler_test; 0 0 0 0

9 rows selected