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

Profiled Run 17 (plsql_profiler_runs)

Run Date Total Time1 Comment
17 13-NOV-03 18:47:59 45.97 Test #1: 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 37.09
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 24.65 300100 0.00 0.08 1 SYSTEM PROFTEST3 PROCEDURE 48 y := instr(v_str, ',', 1, j);
2 3.46 600200 0.00 0.03 1 SYSTEM PROFTEST3 PROCEDURE 57 dbms_output.put_line(j || ': "' || v_str2 || '"');
3 2.69 300100 0.00 0.02 1 SYSTEM PROFTEST3 PROCEDURE 55 v_str2 := substr(v_str, x, (y -x));
4 1.51 3002 0.00 0.08 1 SYSTEM PROFTEST3 PROCEDURE 27 for x in get_numbers loop
5 1.47 300100 0.00 0.03 1 SYSTEM PROFTEST3 PROCEDURE 59 x := y+1;
6 1.05 300200 0.00 0.03 1 SYSTEM PROFTEST3 PROCEDURE 45 for j in 1..v_cnt loop
7 1.01 300100 0.00 0.00 1 SYSTEM PROFTEST3 PROCEDURE 47 if in_mode = 'Test #1' then
8 0.96 300100 0.00 0.00 1 SYSTEM PROFTEST3 PROCEDURE 61 v_errcontext := 'increment counter "i"';
9 0.22 2999 0.00 0.00 1 SYSTEM PROFTEST3 PROCEDURE 32 v_str := v_str || ',' || x.c1;
10 0.03 3000 0.00 0.00 1 SYSTEM PROFTEST3 PROCEDURE 35 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.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.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 --
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.51 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
32T9 0.22 2999 v_str := v_str || ',' || x.c1;
33 end if;
34 --
35T10 0.03 3000 v_cnt := v_cnt + 1;
36 --
37 0.02 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 --
45T6 1.05 300200 for j in 1..v_cnt loop
46 --
47T7 1.01 300100 if in_mode = 'Test #1' then
48T1 24.65 300100 y := instr(v_str, ',', 1, j);
49 0.00 0 elsif in_mode = 'Test #2' then
50 0.00 0 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.69 300100 v_str2 := substr(v_str, x, (y -x));
56 --
57T2 3.46 600200 dbms_output.put_line(j || ': "' || v_str2 || '"');
58 --
59T5 1.47 300100 x := y+1;
60 --
61T8 0.96 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