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