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

Profiled Run 18 (plsql_profiler_runs)

Run Date Total Time1 Comment
18 13-NOV-03 18:48:46 24.34 Test #2: proftest3
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 PROFTEST3 PROCEDURE 13-NOV-03 18:47:43 15.16
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.20 600200 0.00 0.01 1 SYSTEM PROFTEST3 PROCEDURE 57 dbms_output.put_line(j || ': "' || v_str2 || '"');
2 2.46 300100 0.00 0.09 1 SYSTEM PROFTEST3 PROCEDURE 50 y := instr(v_str, ',', x, 1);
3 2.33 300100 0.00 0.00 1 SYSTEM PROFTEST3 PROCEDURE 55 v_str2 := substr(v_str, x, (y -x));
4 1.53 3002 0.00 0.15 1 SYSTEM PROFTEST3 PROCEDURE 27 for x in get_numbers loop
5 1.41 300100 0.00 0.01 1 SYSTEM PROFTEST3 PROCEDURE 59 x := y+1;
6 1.06 300100 0.00 0.03 1 SYSTEM PROFTEST3 PROCEDURE 47 if in_mode = 'Test #1' then
7 1.02 300200 0.00 0.01 1 SYSTEM PROFTEST3 PROCEDURE 45 for j in 1..v_cnt loop
8 0.99 300100 0.00 0.03 1 SYSTEM PROFTEST3 PROCEDURE 61 v_errcontext := 'increment counter "i"';
9 0.93 300100 0.00 0.01 1 SYSTEM PROFTEST3 PROCEDURE 49 elsif in_mode = 'Test #2' then
10 0.16 2999 0.00 0.00 1 SYSTEM PROFTEST3 PROCEDURE 32 v_str := v_str || ',' || x.c1;
Note 1: Total Time is in seconds
Note 2: Min and Max Time for one execution of this line (in seconds)

Unit:1 SYSTEM.PROFTEST3 (all_source)

Line Total Time1 Times Executed Text
1 procedure proftest3(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 --
16 v_errcontext varchar2(250);
17 v_errmsg varchar2(500);
18 --
19 begin
20 --
21 0.00 0 dbms_application_info.set_module('proftest3', in_mode);
22 --
23 0.00 0 v_errcontext := 'dbms_profiler.start_profiler';
24 0.00 1 dbms_profiler.start_profiler(in_mode || ': proftest3');
25 --
26 0.00 1 v_errcontext := 'open/fetch get_numbers';
27T4 1.53 3002 for x in get_numbers loop
28 --
29 0.02 3000 if get_numbers%rowcount = 1 then
30 0.00 1 v_str := x.c1;
31 else
32T10 0.16 2999 v_str := v_str || ',' || x.c1;
33 end if;
34 --
35 0.03 3000 v_cnt := v_cnt + 1;
36 --
37 0.03 3001 v_errcontext := 'fetch/close get_numbers';
38 --
39 end loop; /* end of "get_numbers" cursor loop */
40 --
41 0.00 1 v_errcontext := 'increment counter "i"';
42 0.00 1 x := 1;
43 0.00 101 for i in 1..100 loop
44 --
45T7 1.02 300200 for j in 1..v_cnt loop
46 --
47T6 1.06 300100 if in_mode = 'Test #1' then
48 0.00 0 y := instr(v_str, ',', 1, j);
49T9 0.93 300100 elsif in_mode = 'Test #2' then
50T2 2.46 300100 y := instr(v_str, ',', x, 1);
51 else
52 0.00 0 raise_application_error(-20000, 'Invalid IN_MODE value');
53 end if;
54 --
55T3 2.33 300100 v_str2 := substr(v_str, x, (y -x));
56 --
57T1 3.20 600200 dbms_output.put_line(j || ': "' || v_str2 || '"');
58 --
59T5 1.41 300100 x := y+1;
60 --
61T8 0.99 300100 v_errcontext := 'increment counter "i"';
62 --
63 end loop;
64 --
65 end loop;
66 --
67 0.00 1 v_errcontext := 'dbms_profiler.stop_profiler';
68 0.00 1 dbms_profiler.stop_profiler;
69 --
70 exception
71 when others then
72 0.00 0 v_errmsg := sqlerrm;
73 0.00 1 raise_application_error(-20001, v_errcontext || ': ' || v_errmsg);
74 end proftest3;
Note 1: Total Time is in seconds
Note Tn: Top "n" Line in terms of Total Time