profiler.sql - PL/SQL Profiler (MetaLink Note:243755.1)

Profiled Run 20 (plsql_profiler_runs)

Run Date Total Time1 Comment
20 13-NOV-03 22:58:06 28.08 Test #2: proftest1
Note 1: Total Time is in seconds

Profiled PL/SQL Libraries (plsql_profiler_units)

Unit Owner Name Type Timestamp Total Time1 Text Header
1 SYSTEM PROFTEST1 PROCEDURE 13-NOV-03 22:56:10 17.46
Note 1: Total Time is in seconds

Top 10 profiled source lines in terms of Total Time (plsql_profiler_data)

Top Total Time1 Times Executed Min Time2 Max Time2 Unit Owner Name Type Line Text
1 3.96 600200 0.00 0.12 1 SYSTEM PROFTEST1 PROCEDURE 47 dbms_output.put_line(j || ': "' || v_str2 || '"');
2 3.03 300100 0.00 0.12 1 SYSTEM PROFTEST1 PROCEDURE 39 y := instr(v_str, ',', x, 1);
3 2.80 300100 0.00 0.13 1 SYSTEM PROFTEST1 PROCEDURE 45 v_str2 := substr(v_str, x, (y -x));
4 1.79 3002 0.00 0.09 1 SYSTEM PROFTEST1 PROCEDURE 19 for x in get_numbers loop
5 1.72 300100 0.00 0.12 1 SYSTEM PROFTEST1 PROCEDURE 49 x := y + 1;
6 1.57 300200 0.00 0.12 1 SYSTEM PROFTEST1 PROCEDURE 34 for j in 1..v_cnt loop
7 1.32 300100 0.00 0.12 1 SYSTEM PROFTEST1 PROCEDURE 36 if in_mode = 'Test #1' then
8 1.05 300100 0.00 0.09 1 SYSTEM PROFTEST1 PROCEDURE 38 elsif in_mode = 'Test #2' then
9 0.18 2999 0.00 0.01 1 SYSTEM PROFTEST1 PROCEDURE 24 v_str := v_str || ',' || x.c1;
10 0.03 3001 0.00 0.00 1 SYSTEM PROFTEST1 PROCEDURE 27 v_cnt := v_cnt + 1;
Note 1: Total Time is in seconds
Note 2: Min and Max Time for one execution of this line (in seconds)

Unit:1 SYSTEM.PROFTEST1 (all_source)

Line Total Time1 Times Executed Text
1 procedure proftest1(in_mode in varchar2)
2 is
3 --
4 cursor get_numbers is
5 0.00 1 select trim(to_char(c1)) c1
6 from proftest_table
7 where c1 between 10000 and 12999;
8 --
9 0.00 0 v_str varchar2(32760) := '';
10 0.00 0 v_str2 varchar2(100) := '';
11 x integer;
12 y integer;
13 0.00 0 v_cnt integer := 1;
14 --
15 begin
16 --
17 0.00 1 dbms_profiler.start_profiler(in_mode || ': proftest1');
18 --
19T4 1.79 3002 for x in get_numbers loop
20 --
21 0.02 3000 if get_numbers%rowcount = 1 then
22 0.00 1 v_str := x.c1;
23 else
24T9 0.18 2999 v_str := v_str || ',' || x.c1;
25 end if;
26 --
27T10 0.03 3001 v_cnt := v_cnt + 1;
28 --
29 end loop; /* end of "get_numbers" cursor loop */
30 --
31 0.00 1 x := 1;
32 0.00 101 for i in 1..100 loop
33 --
34T6 1.57 300200 for j in 1..v_cnt loop
35 --
36T7 1.32 300100 if in_mode = 'Test #1' then
37 0.00 0 y := instr(v_str, ',', 1, j);
38T8 1.05 300100 elsif in_mode = 'Test #2' then
39T2 3.03 300100 y := instr(v_str, ',', x, 1);
40 else
41 0.00 0 raise_application_error(-20000,
42 'Invalid IN_MODE value');
43 end if;
44 --
45T3 2.80 300100 v_str2 := substr(v_str, x, (y -x));
46 --
47T1 3.96 600200 dbms_output.put_line(j || ': "' || v_str2 || '"');
48 --
49T5 1.72 300100 x := y + 1;
50 --
51 end loop;
52 --
53 end loop;
54 --
55 0.00 2 dbms_profiler.stop_profiler;
56 --
57 end proftest1;
Note 1: Total Time is in seconds
Note Tn: Top "n" Line in terms of Total Time