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

Profiled Run 13 (plsql_profiler_runs)

Run Date Total Time1 Comment
13 13-NOV-03 17:16:32 44.18 DBMS_ROWID: 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 34.50
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 28.42 100002 0.00 0.36 1 SYSTEM PROFTEST2 PROCEDURE 37 for x in get_rowids loop
2 1.08 200001 0.00 0.00 1 SYSTEM PROFTEST2 PROCEDURE 42 v_fno := dbms_rowid.rowid_relative_fno(x.rowid);
3 0.63 200000 0.00 0.00 1 SYSTEM PROFTEST2 PROCEDURE 43 v_obj := dbms_rowid.rowid_object(x.rowid);
4 0.63 200000 0.00 0.00 1 SYSTEM PROFTEST2 PROCEDURE 44 v_bno := dbms_rowid.rowid_block_number(x.rowid);
5 0.62 200000 0.00 0.00 1 SYSTEM PROFTEST2 PROCEDURE 45 v_rno := dbms_rowid.rowid_row_number(x.rowid);
6 0.50 100000 0.00 0.02 1 SYSTEM PROFTEST2 PROCEDURE 84 v_rows := v_rows + 1;
7 0.49 100000 0.00 0.00 1 SYSTEM PROFTEST2 PROCEDURE 39 v_errcontext := 'extracting ROWID components';
8 0.47 100000 0.00 0.02 1 SYSTEM PROFTEST2 PROCEDURE 47 if v_fno <> v_prev_fno or
9 0.43 100000 0.00 0.00 1 SYSTEM PROFTEST2 PROCEDURE 40 if in_mode = 'DBMS_ROWID' then
10 0.35 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 28.42 100002 for x in get_rowids loop
38 --
39T7 0.49 100000 v_errcontext := 'extracting ROWID components';
40T9 0.43 100000 if in_mode = 'DBMS_ROWID' then
41 --
42T2 1.08 200001 v_fno := dbms_rowid.rowid_relative_fno(x.rowid);
43T3 0.63 200000 v_obj := dbms_rowid.rowid_object(x.rowid);
44T4 0.63 200000 v_bno := dbms_rowid.rowid_block_number(x.rowid);
45T5 0.62 200000 v_rno := dbms_rowid.rowid_row_number(x.rowid);
46 --
47T8 0.47 100000 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 339 v_blocks := v_blocks + 1;
52 end if;
53 --
54 0.28 100000 v_prev_fno := v_fno;
55 0.29 100000 v_prev_obj := v_obj;
56 0.32 100000 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 */
66 0.00 0 v_fno_str := substr(x.rowid, 7, 3);
67 0.00 0 v_obj_str := substr(x.rowid, 10, 6);
68 0.00 0 v_bno_str := substr(x.rowid, 1, 6);
69 0.00 0 v_rno_str := substr(x.rowid, 16, 3);
70 --
71 0.00 0 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 0 v_blocks := v_blocks + 1;
76 end if;
77 --
78 0.00 0 v_prev_fno_str := v_fno_str;
79 0.00 0 v_prev_obj_str := v_obj_str;
80 0.00 0 v_prev_bno_str := v_bno_str;
81 --
82 end if;
83 --
84T6 0.50 100000 v_rows := v_rows + 1;
85 --
86T10 0.35 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