关于使用dbms_profiler package的例子
sql>@?/rdbms/admin/profload
创建相关packages
sql>@?/rdbms/admin/proftab
创建相关tables
sql>@?/plsql/demo/profrep
创建相关views及package

create or replace procedure test1
as
numLoop number := 0;
begin
for i in 1 .. 100000 loop
numLoop := numLoop + 1;
if mod(numLoop,1000) = 0 then
null;
end if;
end loop;
end;
/
create or replace procedure test2
as
numLoop number := 0;
begin
for i in 1 .. 100000 loop
numLoop := numLoop + 1;
if numLoop = 1000 then
null;
numLoop := 0;
end if;
end loop;
end;
/
set line 5000 serveroutput on size 1000000
declare
v_run number;
begin
dbms_profiler.start_profiler(run_number=>v_run);
test1;
dbms_profiler.stop_profiler;
dbms_profiler.start_profiler(run_number=>v_run);
test2;
dbms_profiler.stop_profiler;
end;
/
@?/plsql/demo/profsum

set linesize 131
col text format a24
col run_comment format a12
col run_system_info format a12
col run_comment1 format a12
col run_owner format a12
col spare1 format a12
select * from plsql_profiler_runs order by 1;

col unit_type format a18
col unit_owner format a12
col unit_name format a18
select * from plsql_profiler_units order by 1;

declare
v_run number;
begin
dbms_profiler.start_profiler(run_number=>v_run);
&procedure_name;
dbms_profiler.stop_profiler;
dbms_profiler.rollup_run(v_run);
prof_report_utilities.print_run(v_run);
end;

如果输出信息混乱,清在plsql/demo/profsum.sql中添加如下内容
column owner format a11
column unit_name format a14
column text format a40
column runid format 9999
column secs format 99999.99
column hsecs format 999999.99
column grand_total format 9999.99
column run_comment format a40
column line# format 99999
column pct format 999.9
column unit_owner format a11

////////////////////////////////////////////////////////////////////////////////////

SQL> set line 5000 serveroutput on size 1000000
SQL> declare
2 v_run number;
3 begin
4 dbms_profiler.start_profiler(run_number=>v_run);
5 test1;
6 dbms_profiler.stop_profiler;
7 dbms_profiler.start_profiler(run_number=>v_run);
8 test2;
9 dbms_profiler.stop_profiler;
10 end;
11 /

PL/SQL procedure successfully completed.

////////////////////////////////////////////////////////////////////////////////////////////

执行profsum时最好把profsum.sql内的@profrep去掉

////////////////////////////////////////////////////////////////////////////////////////////

SQL> @?/plsql/demo/profsum

GRAND_TOTA
----------
.60

Elapsed: 00:00:00.01

RUNID RUN_COMMENT SECONDS
----- ----------- ----------
30 29-JAN-05 .640777
31 29-JAN-05 .501734

Elapsed: 00:00:00.00

RUNID RUN_COMMENT UNIT_OWNER UNIT_NAME SECONDS PERCEN
----- ----------- ----------- -------------- --------- ------
30 29-JAN-05 SCOTT TEST1 .36 56.7
31 29-JAN-05 SCOTT TEST2 .24 47.5

Elapsed: 00:00:00.00

UNIT_OWNER UNIT_NAME SECONDS PERCENTAG
----------- -------------- --------- ---------
SCOTT TEST1 .36 60.35
SCOTT TEST2 .24 39.63
.00 .02

Elapsed: 00:00:00.01
to_char(p1.max_time/p1.min_time,'999999.99') as "Max/min",
*
ERROR at line 9:
ORA-01476: divisor is equal to zero

Elapsed: 00:00:00.06

no rows selected

Elapsed: 00:00:00.07

SECONDS UNIT_OWNER UNIT_NAME LINE# TEXT
-------- ----------- -------------- ------ ------------------------------------
.2 SCOTT TEST1 7 if mod(numLoop,1000) = 0 then
.1 SCOTT TEST1 5 for i in 1 .. 100000 loop
.1 SCOTT TEST1 6 numLoop := numLoop + 1;
.1 SCOTT TEST2 6 numLoop := numLoop + 1;
.1 SCOTT TEST2 7 if numLoop = 1000 then
.1 SCOTT TEST2 5 for i in 1 .. 100000 loop

6 rows selected.

Elapsed: 00:00:00.08

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

no rows selected

Elapsed: 00:00:00.01

no rows selected

Elapsed: 00:00:00.00

LINES_EXECUTED
--------------
0

Elapsed: 00:00:00.01

LINES_PRESENT
-------------
0

Elapsed: 00:00:00.00
==================trace info=================
========Results for run #30 made on 29-JAN-05 19:05:54 =======
(29-JAN-05) Run total time: .64 seconds
Unit #1: . - Total time: .00 seconds
Unit #2: SCOTT.TEST1 - Total time: .36 seconds
1 0 .000007 procedure test1
2 as
3 1 .000001 .000001 numLoop number := 0;
4 begin
5 100,001 .095427 .00000095 for i in 1 .. 100000 loop
6 100,000 .089174 .00000089 numLoop := numLoop + 1;
7 100,000 .178602 .00000178 if mod(numLoop,1000) = 0 then
8 null;
9 end if;
10 end loop;
11 1 .000001 .000001 end
========Results for run #31 made on 29-JAN-05 19:05:55 ======
(29-JAN-05) Run total time: .50 seconds
Unit #1: . - Total time: .00 seconds
Unit #2: SCOTT.TEST2 - Total time: .24 seconds
1 0 .000007 procedure test2
2 as
3 1 .000001 .000001 numLoop number := 0;
4 begin
5 100,001 .07694 .00000076 for i in 1 .. 100000 loop
6 100,000 .083937 .00000083 numLoop := numLoop + 1;
7 100,000 .077514 .00000077 if numLoop = 1000 then
8 null;
9 100 .000081 .00000081 numLoop := 0;
10 end if;
11 end loop;
12 1 .000001 .000001 end
================================================

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
========= Profiler report - all runs rolled up ========
Unit .:
Unit SCOTT.TEST1:
1 0 .000007 procedure test1
2 as
3 1 .000001 .000001 numLoop number := 0;
4 begin
5 100,001 .095427 .00000095 for i in 1 .. 100000 loop
6 100,000 .089174 .00000089 numLoop := numLoop + 1;
7 100,000 .178602 .00000178 if mod(numLoop,1000) = 0 then
8 null;
9 end if;
10 end loop;
11 1 .000001 .000001 end
Unit SCOTT.TEST2:
1 0 .000007 procedure test2
2 as
3 1 .000001 .000001 numLoop number := 0;
4 begin
5 100,001 .07694 .00000076 for i in 1 .. 100000 loop
6 100,000 .083937 .00000083 numLoop := numLoop + 1;
7 100,000 .077514 .00000077 if numLoop = 1000 then
8 null;
9 100 .000081 .00000081 numLoop := 0;
10 end if;
11 end loop;
12 1 .000001 .000001 end