PL/SQL Profiler - DBMS_PROFILER

之前写过EBS下的PL/SQL Profiling 调试的文章，PL/SQL Profiling用于调试EBS下PL/SQL（如:Procedure,Function）的性能情况。今天抛开EBS这个特定的产品,写一下如何使用DBMS_PROFILER这个Package，如何获取PL/SQL中各段代码的执行时间。

DBMS_PROFILER的官方介绍：http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_profil.htm

DBMS_PROFILER Installation

一般情况下DBMS_PROFILER的Package和Table都已经安装，如果你的数据库里没有DBMS_PROFILER相关Package和Table，那么需要手动的运行以下两个prof*.sql
1.Run profload.sql which will install the DBMS_PROFILER package.
$ORACLE_HOME/rdbms/admin/profload.sql

2.Once connected run proftab.sql which will create special tables where profiler puts its results.
$ORACLE_HOME/rdbms/admin/proftab.sql
Tables:
plsql_profiler_runs  - information on profiler runs
plsql_profiler_units - information on each lu profiled
plsql_profiler_data  - profiler data for each lu profiled

Demo

1.创建一个测试表

create table t1 (col1 varchar2(30), col2 varchar2(30));

2.创建一个测试Procedure

create or replace procedure literals
is
 vNumber number;
begin
 for i in 1..100000 loop
   vNumber := dbms_random.random;               
   execute immediate
    'insert into t1 values ('||vNumber||','||vNumber||')';
 end loop;
 end;

3.Start Profiler-> Your Procedure -> Stop Profiler

SQL> execute dbms_profiler.start_profiler('PTIAN123'); --PTIAN123 is comment for your profiler

SQL> exec literals;

SQL> execute dbms_profiler.stop_profiler;

4.查看测试结果

4.1) plsql_profiler_runs has summary of all dbms_profiler runs.

set lines 10000
column run_owner format a30
column run_comment format a10
select runid,
       run_owner, 
       run_date,
       run_total_time/1000000000 run_total_time,
       run_comment
from plsql_profiler_runs
WHERE run_comment = 'PTIAN123';

Result Like:

RUNID	RUN_OWNER	RUN_DATE	RUN_TOTAL_TIME	RUN_COMMENT
7	APPS	02.12.2012 20:24:55	49.759322	PTIAN123

4.2)

column text format a55
 column total_time format 99.9
 column min_time format 99.9
 column max_time format 99.9
 select s.text ,
        p.total_occur ,
        p.total_time/1000000000 total_time,
        p.min_time/1000000000 min_time,
        p.max_time/1000000000 max_time
 from plsql_profiler_data p, user_source s, plsql_profiler_runs r
 where p.line# = s.line
 and   p.runid = r.runid
 and   r.run_comment = 'PTIAN123'  --Your Profiler Comment
 and   s.name ='LITERALS';  --Procedure Name

Result Like:

TEXT	TOTAL_OCCUR	TOTAL_TIME	MIN_TIME	MAX_TIME
procedure literals	0	0	0	0
is	0	0	0	0
vNumber number;	1	.000001	.000001	.000001
procedure literals	2	.000035	.000003	.000019
procedure literals	2	.000033	.000002	.000027
procedure literals	0	.000004	.000004	.000004
is	2	.000013	.000001	.000012
vNumber number;	1	.000003	.000003	.000003
procedure literals	0	.000004	.000004	.000004
for i in 1..100000 loop	100001	.10566	.000001	.000808
vNumber := dbms_random.random;	100000	.319572	.000001	.000959
execute immediate	100000	40.923568	.000287	.112456
end;	1	.000055	.000055	.000055
procedure literals	2	.000044	.000005	.000025
procedure literals	2	.000032	.000002	.000026
procedure literals	0	.000007	.000007	.000007
is	2	.002595	.000002	.002593
vNumber number;	0	0	0	0

从上面的结果就可以清楚的看到，execute immediate的执行时间最长（实际是insert的消耗）

DBMS Profiler HTML report

另外Oracle还提供了一个DBMS Profiler的HTML页面化工具，可以参见下边这个NOTE，HTML的输出更加清楚些.

Script to produce HTML report with top consumers out of PL/SQL Profiler DBMS_PROFILER data (Doc ID 243755.1)
输出的样式：