Line |
Total Time1 |
Times Executed |
Text |
1 |
0.00 |
0 |
procedure proftest2(in_mode in varchar2, in_table in varchar2)
|
2 |
|
|
is
|
3 |
|
|
c integer;
|
4 |
|
|
rtn integer;
|
5 |
|
|
v_rowcnt number;
|
6 |
0.00 |
0 |
v_total_rowcnt number := 0;
|
7 |
0.00 |
0 |
v_fetches number := 0;
|
8 |
|
|
v_sql_stmt varchar2(200);
|
9 |
|
|
--
|
10 |
|
|
v_errcontext varchar2(250);
|
11 |
|
|
v_errmsg varchar2(500);
|
12 |
|
|
--
|
13 |
|
|
begin
|
14 |
|
|
--
|
15 |
0.00 |
0 |
dbms_application_info.set_module('proftest2', in_table);
|
16 |
|
|
--
|
17 |
0.00 |
0 |
v_errcontext := 'dbms_profiler.start_profiler';
|
18 |
0.00 |
0 |
dbms_profiler.start_profiler(in_mode || ': proftest2(' || in_table || ')');
|
19 |
|
|
--
|
20 |
0.00 |
1 |
v_errcontext := 'construct SQL statement';
|
21 |
0.00 |
1 |
if in_mode = 'SUBSTR' then
|
22 |
|
|
--
|
23 |
0.00 |
1 |
v_sql_stmt := 'select count(substr(ROWID,16,3)) from ' || in_table ||
|
24 |
|
|
' group by substr(ROWID,7,3), substr(ROWID,10,6), substr(ROWID,1,6)';
|
25 |
|
|
--
|
26 |
0.00 |
0 |
elsif in_mode = 'DBMS_ROWID' then
|
27 |
|
|
--
|
28 |
0.00 |
0 |
v_sql_stmt := 'select count(dbms_rowid.rowid_row_number(ROWID)) from ' || in_table ||
|
29 |
|
|
' group by dbms_rowid.rowid_relative_fno(ROWID), dbms_rowid.rowid_block_number(ROWID), dbms_rowid.rowid_object(ROWID)';
|
30 |
|
|
--
|
31 |
|
|
else
|
32 |
|
|
--
|
33 |
0.00 |
0 |
raise_application_error(-20000, 'IN_MODE must be either "SUBSTR" or "DBMS_ROWID"');
|
34 |
|
|
--
|
35 |
|
|
end if;
|
36 |
|
|
--
|
37 |
0.00 |
1 |
v_errcontext := 'dbms_sql.open_cursor';
|
38T7 |
0.01 |
1 |
c := dbms_sql.open_cursor;
|
39 |
|
|
--
|
40 |
0.00 |
1 |
v_errcontext := substr('dbms_sql.parse("' || v_sql_stmt || '")', 1, 250);
|
41 |
0.00 |
1 |
dbms_sql.parse(c, v_sql_stmt, dbms_sql.native);
|
42 |
|
|
--
|
43 |
0.00 |
1 |
v_errcontext := 'dbms_sql.define_column';
|
44 |
0.00 |
1 |
dbms_sql.define_column(c, 1, v_rowcnt);
|
45 |
|
|
--
|
46 |
0.00 |
1 |
v_errcontext := substr('dbms_sql.execute("' || v_sql_stmt || '")', 1, 250);
|
47 |
0.00 |
1 |
dbms_application_info.set_action(v_errcontext);
|
48 |
0.00 |
1 |
rtn := dbms_sql.execute(c);
|
49 |
|
|
--
|
50 |
0.00 |
1 |
v_errcontext := substr('dbms_sql.fetch_rows("' || v_sql_stmt || '")', 1, 250);
|
51 |
0.00 |
1 |
dbms_application_info.set_action(v_errcontext);
|
52T2 |
0.11 |
340 |
while dbms_sql.fetch_rows(c) = 1 loop
|
53 |
|
|
--
|
54T3 |
0.07 |
339 |
v_errcontext := 'dbms_sql.column_value';
|
55T1 |
0.15 |
339 |
dbms_sql.column_value(c, 1, v_rowcnt);
|
56 |
|
|
--
|
57T4 |
0.06 |
339 |
v_fetches := v_fetches + 1;
|
58T5 |
0.05 |
339 |
v_total_rowcnt := v_total_rowcnt + v_rowcnt;
|
59 |
|
|
--
|
60 |
|
|
end loop;
|
61 |
|
|
--
|
62 |
0.00 |
1 |
v_errcontext := 'dbms_sql.close_cursor';
|
63 |
0.00 |
1 |
dbms_sql.close_cursor(c);
|
64 |
|
|
--
|
65 |
|
|
/*
|
66 |
|
|
* ...drop the rowcount from the very last database block,
|
67 |
|
|
* so that a partially filled database block does not skew
|
68 |
|
|
* the average...
|
69 |
|
|
*/
|
70 |
0.00 |
1 |
if v_fetches > 1 then
|
71 |
|
|
--
|
72 |
0.00 |
1 |
v_total_rowcnt := (v_total_rowcnt - v_rowcnt) / (v_fetches - 1);
|
73 |
|
|
--
|
74 |
|
|
end if;
|
75 |
|
|
--
|
76 |
0.00 |
1 |
v_errcontext := 'displaying final report';
|
77T6 |
0.02 |
1 |
dbms_output.put_line(in_table || ' avgs ' ||
|
78 |
|
|
ltrim(to_char(v_total_rowcnt, '99,990.0000')) ||
|
79 |
|
|
' rows/db-block for ' ||
|
80 |
|
|
ltrim(to_char(v_fetches, '999,999,999,990')) || ' db-blocks');
|
81 |
|
|
--
|
82 |
0.00 |
1 |
v_errcontext := 'dbms_profiler.stop_profiler';
|
83 |
0.00 |
1 |
dbms_profiler.stop_profiler;
|
84 |
|
|
--
|
85 |
|
|
exception
|
86 |
|
|
when others then
|
87 |
0.00 |
0 |
v_errmsg := sqlerrm;
|
88 |
0.00 |
0 |
raise_application_error(-20001, v_errcontext || ': ' || v_errmsg);
|
89 |
0.00 |
0 |
end proftest2; |
Note 1: Total Time is in seconds