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

Profiled Run 19 (plsql_profiler_runs)

Run Date Total Time1 Comment
19 13-NOV-03 22:57:10 55.14 Test #1: 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 45.13
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 30.94 300100 0.00 0.15 1 SYSTEM PROFTEST1 PROCEDURE 37 y := instr(v_str, ',', 1, j);
2 4.30 600200 0.00 0.13 1 SYSTEM PROFTEST1 PROCEDURE 47 dbms_output.put_line(j || ': "' || v_str2 || '"');
3 3.23 300100 0.00 0.09 1 SYSTEM PROFTEST1 PROCEDURE 45 v_str2 := substr(v_str, x, (y -x));
4 2.04 300100 0.00 0.22 1 SYSTEM PROFTEST1 PROCEDURE 49 x := y + 1;
5 1.79 3002 0.00 0.09 1 SYSTEM PROFTEST1 PROCEDURE 19 for x in get_numbers loop
6 1.34 300200 0.00 0.09 1 SYSTEM PROFTEST1 PROCEDURE 34 for j in 1..v_cnt loop
7 1.08 300100 0.00 0.00 1 SYSTEM PROFTEST1 PROCEDURE 36 if in_mode = 'Test #1' then
8 0.32 2999 0.00 0.08 1 SYSTEM PROFTEST1 PROCEDURE 24 v_str := v_str || ',' || x.c1;
9 0.07 3001 0.00 0.04 1 SYSTEM PROFTEST1 PROCEDURE 27 v_cnt := v_cnt + 1;
10 0.02 3000 0.00 0.00 1 SYSTEM PROFTEST1 PROCEDURE 21 if get_numbers%rowcount = 1 then
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.01 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 --
19T5 1.79 3002 for x in get_numbers loop
20 --
21T10 0.02 3000 if get_numbers%rowcount = 1 then
22 0.00 1 v_str := x.c1;
23 else
24T8 0.32 2999 v_str := v_str || ',' || x.c1;
25 end if;
26 --
27T9 0.07 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.34 300200 for j in 1..v_cnt loop
35 --
36T7 1.08 300100 if in_mode = 'Test #1' then
37T1 30.94 300100 y := instr(v_str, ',', 1, j);
38 0.00 0 elsif in_mode = 'Test #2' then
39 0.00 0 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 3.23 300100 v_str2 := substr(v_str, x, (y -x));
46 --
47T2 4.30 600200 dbms_output.put_line(j || ': "' || v_str2 || '"');
48 --
49T4 2.04 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