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

Profiled Run 14 (plsql_profiler_runs)

Run Date Total Time1 Comment
14 13-NOV-03 17:17:18 37.05 SUBSTR: proftest2
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 PROFTEST2 PROCEDURE 13-NOV-03 17:16:27 33.32
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 27.39 100002 0.00 0.23 1 SYSTEM PROFTEST2 PROCEDURE 37 for x in get_rowids loop
2 1.16 100000 0.00 0.02 1 SYSTEM PROFTEST2 PROCEDURE 66 v_fno_str := substr(x.rowid, 7, 3);
3 0.54 100000 0.00 0.00 1 SYSTEM PROFTEST2 PROCEDURE 69 v_rno_str := substr(x.rowid, 16, 3);
4 0.54 100000 0.00 0.00 1 SYSTEM PROFTEST2 PROCEDURE 67 v_obj_str := substr(x.rowid, 10, 6);
5 0.54 100000 0.00 0.00 1 SYSTEM PROFTEST2 PROCEDURE 68 v_bno_str := substr(x.rowid, 1, 6);
6 0.51 100000 0.00 0.00 1 SYSTEM PROFTEST2 PROCEDURE 84 v_rows := v_rows + 1;
7 0.50 100000 0.00 0.00 1 SYSTEM PROFTEST2 PROCEDURE 71 if v_fno_str <> v_prev_fno_str or
8 0.47 100000 0.00 0.02 1 SYSTEM PROFTEST2 PROCEDURE 40 if in_mode = 'DBMS_ROWID' then
9 0.46 100000 0.00 0.00 1 SYSTEM PROFTEST2 PROCEDURE 39 v_errcontext := 'extracting ROWID components';
10 0.33 100001 0.00 0.00 1 SYSTEM PROFTEST2 PROCEDURE 86 v_errcontext := 'fetch/close get_rowids';
Note 1: Total Time is in seconds
Note 2: Min and Max Time for one execution of this line (in seconds)

Unit:1 SYSTEM.PROFTEST2 (all_source)

Line Total Time1 Times Executed Text
1 procedure proftest2(in_mode in varchar2)
2 is
3 --
4 cursor get_rowids is
5 0.00 1 select rowid
6 from proftest_table
7 order by rowid;
8 --
9 0.00 0 v_blocks number := 0;
10 0.00 0 v_rows number := 0;
11 v_fno number;
12 v_obj number;
13 v_bno number;
14 v_rno number;
15 0.00 0 v_prev_fno number := -1;
16 0.00 0 v_prev_obj number := -1;
17 0.00 0 v_prev_bno number := -1;
18 v_fno_str varchar2(6);
19 v_obj_str varchar2(6);
20 v_bno_str varchar2(6);
21 v_rno_str varchar2(6);
22 0.00 0 v_prev_fno_str varchar2(6) := '~';
23 0.00 0 v_prev_obj_str varchar2(6) := '~';
24 0.00 0 v_prev_bno_str varchar2(6) := '~';
25 --
26 v_errcontext varchar2(250);
27 v_errmsg varchar2(500);
28 --
29 begin
30 --
31 0.00 0 dbms_application_info.set_module('proftest2', in_mode);
32 --
33 0.00 0 v_errcontext := 'dbms_profiler.start_profiler';
34 0.00 1 dbms_profiler.start_profiler(in_mode || ': proftest2');
35 --
36 0.00 1 v_errcontext := 'open/fetch get_rowids';
37T1 27.39 100002 for x in get_rowids loop
38 --
39T9 0.46 100000 v_errcontext := 'extracting ROWID components';
40T8 0.47 100000 if in_mode = 'DBMS_ROWID' then
41 --
42 0.00 0 v_fno := dbms_rowid.rowid_relative_fno(x.rowid);
43 0.00 0 v_obj := dbms_rowid.rowid_object(x.rowid);
44 0.00 0 v_bno := dbms_rowid.rowid_block_number(x.rowid);
45 0.00 0 v_rno := dbms_rowid.rowid_row_number(x.rowid);
46 --
47 0.00 0 if v_fno <> v_prev_fno or
48 v_obj <> v_prev_obj or
49 v_bno <> v_prev_bno
50 then
51 0.00 0 v_blocks := v_blocks + 1;
52 end if;
53 --
54 0.00 0 v_prev_fno := v_fno;
55 0.00 0 v_prev_obj := v_obj;
56 0.00 0 v_prev_bno := v_bno;
57 --
58 else /* in_mode = 'SUBSTR' */
59 --
60 /*
61 v_fno_str := substr(rowidtochar(x.rowid), 7, 3);
62 v_obj_str := substr(rowidtochar(x.rowid), 10, 6);
63 v_bno_str := substr(rowidtochar(x.rowid), 1, 6);
64 v_rno_str := substr(rowidtochar(x.rowid), 16, 3);
65 */
66T2 1.16 100000 v_fno_str := substr(x.rowid, 7, 3);
67T4 0.54 100000 v_obj_str := substr(x.rowid, 10, 6);
68T5 0.54 100000 v_bno_str := substr(x.rowid, 1, 6);
69T3 0.54 100000 v_rno_str := substr(x.rowid, 16, 3);
70 --
71T7 0.50 100000 if v_fno_str <> v_prev_fno_str or
72 v_obj_str <> v_prev_obj_str or
73 v_bno_str <> v_prev_bno_str
74 then
75 0.00 339 v_blocks := v_blocks + 1;
76 end if;
77 --
78 0.29 100000 v_prev_fno_str := v_fno_str;
79 0.29 100000 v_prev_obj_str := v_obj_str;
80 0.30 100000 v_prev_bno_str := v_bno_str;
81 --
82 end if;
83 --
84T6 0.51 100000 v_rows := v_rows + 1;
85 --
86T10 0.33 100001 v_errcontext := 'fetch/close get_rowids';
87 --
88 end loop; /* end of "get_rowids" cursor loop */
89 --
90 0.00 1 v_errcontext := 'displaying final report';
91 0.00 2 dbms_output.put_line(v_blocks || ' blocks, ' || v_rows || ' rows for an average of ' ||
92 trim(to_char(v_rows/v_blocks, '9,990.000')) || ' rows per block');
93 --
94 0.00 1 v_errcontext := 'dbms_profiler.stop_profiler';
95 0.00 1 dbms_profiler.stop_profiler;
96 --
97 exception
98 when others then
99 0.00 0 v_errmsg := sqlerrm;
100 0.00 1 raise_application_error(-20001, v_errcontext || ': ' || v_errmsg);
101 end proftest2;
Note 1: Total Time is in seconds
Note Tn: Top "n" Line in terms of Total Time