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

Profiled Run 16 (plsql_profiler_runs)

Run Date Total Time1 Comment
16 13-NOV-03 18:38:40 0.77 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:38:29 0.59
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 0.49 1002 0.00 0.07 1 SYSTEM PROFTEST3 PROCEDURE 27 for x in get_numbers loop
2 0.02 999 0.00 0.00 1 SYSTEM PROFTEST3 PROCEDURE 32 v_str := v_str || ',' || x.c1;
3 0.02 2000 0.00 0.00 1 SYSTEM PROFTEST3 PROCEDURE 55 dbms_output.put_line(i || ': "' || v_str2 || '"');
4 0.01 1000 0.00 0.00 1 SYSTEM PROFTEST3 PROCEDURE 53 v_str2 := substr(v_str, x, (y -x));
5 0.01 1000 0.00 0.00 1 SYSTEM PROFTEST3 PROCEDURE 48 y := instr(v_str, ',', x, 1);
6 0.01 1000 0.00 0.00 1 SYSTEM PROFTEST3 PROCEDURE 35 v_cnt := v_cnt + 1;
7 0.01 1000 0.00 0.00 1 SYSTEM PROFTEST3 PROCEDURE 57 x := y+1;
8 0.01 1001 0.00 0.00 1 SYSTEM PROFTEST3 PROCEDURE 37 v_errcontext := 'fetch/close get_numbers';
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 10999;
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 := 0;
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';
27T1 0.49 1002 for x in get_numbers loop
28 --
29 0.00 1000 if get_numbers%rowcount = 1 then
30 0.00 1 v_str := x.c1;
31 else
32T2 0.02 999 v_str := v_str || ',' || x.c1;
33 end if;
34 --
35T6 0.01 1000 v_cnt := v_cnt + 1;
36 --
37T8 0.01 1001 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 1001 for i in 1..v_cnt loop
44 --
45 0.00 1000 if in_mode = 'Test #1' then
46 0.00 0 y := instr(v_str, ',', 1, i);
47 0.00 1000 elsif in_mode = 'Test #2' then
48T5 0.01 1000 y := instr(v_str, ',', x, 1);
49 else
50 0.00 0 raise_application_error(-20000, 'Invalid IN_MODE value');
51 end if;
52 --
53T4 0.01 1000 v_str2 := substr(v_str, x, (y -x));
54 --
55T3 0.02 2000 dbms_output.put_line(i || ': "' || v_str2 || '"');
56 --
57T7 0.01 1000 x := y+1;
58 --
59 0.00 1000 v_errcontext := 'increment counter "i"';
60 --
61 end loop;
62 --
63 0.00 1 v_errcontext := 'dbms_profiler.stop_profiler';
64 0.00 1 dbms_profiler.stop_profiler;
65 --
66 exception
67 when others then
68 0.00 0 v_errmsg := sqlerrm;
69 0.00 1 raise_application_error(-20001, v_errcontext || ': ' || v_errmsg);
70 end proftest3;
Note 1: Total Time is in seconds
Note Tn: Top "n" Line in terms of Total Time