dbms_profiler incorect timing info?

From: DazzaL <duaij1REMOVEME_at_hotmail.com>
Date: Sat, 22 Jun 2002 11:59:26 +0000 (UTC)
Message-ID: <7vo8huglu1s7esus9s1fpfmcvog4ke9cq1@4ax.com>

Hi,

im testing out dbms_profiler but the timings it coming back with appear wrong:

the package im testing.......

CREATE OR REPLACE package one as

 procedure main;

end;
/

CREATE OR REPLACE package body one as
  procedure main
  as
  begin
    for i in (select * from all_objects)     loop
     two.main;
   end loop;
  end main;
end;
/

CREATE OR REPLACE package two as
 procedure main;
end;
/

CREATE OR REPLACE package body two as
  procedure main
  as
    v_temp varchar2(2000);
  begin
    select 'x' into v_temp from dual;
   end main;
end;
/

the script t collect data:

set serveroutput on size 1000000;
set timing on;
set linesize 200;

declare
  v_run number;
begin
  dbms_profiler.start_profiler('test','',v_run);   one.main;

  dbms_profiler.stop_profiler;
  dbms_profiler.rollup_run(v_run);
  prof_report_utilities.print_run(v_run);

end;
/

the timings it comes back with are

===========================Results for run #8 made on 22-JUN-02 12:51:29 =========================
(test) Run total time:   1811.39 seconds
Unit #1: <anonymous>.<anonymous> - Total time:       .03 seconds
Unit #2: SPG.ONE - Total time:   1038.55 seconds
1                                                package body one as

2
3

4                                                 procedure main
5                                                 as
6
7                                                 v_temp varchar2(2000);
8                                                 begin
9
10      21,242   996.28314  .04690156                for i in (select * from all_objects)
11                                                   loop
12
13      42,483   42.258235  .00099470                  two.main;
14                                                    end loop;
15
16           1   .00634494  .00634494               end main;
17                                                end;
Unit #3: SPG.TWO - Total time:    693.64 seconds
1                                                package body two as

2
3

4                                                 procedure main
5                                                 as
6
7                                                 v_temp varchar2(2000);
8                                                 begin
9
10      21,241   672.91691  .03168009                 select 'x' into v_temp from dual;
11
12      21,241   20.724036  .00097566               end main;
13                                                end;

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.78
SQL> so why does dbms_profiler say that the total run time is : (test) Run total time: 1811.39 seconds when it clearly took only 4.78 seconds in total

Thanks.

-- 
Daz.

Received on Sat Jun 22 2002 - 06:59:26 CDT