关于使用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
=======================================
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/111631/viewspace-610559/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/111631/viewspace-610559/