使用dbms_profiler包的示例

关于使用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/