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

Profiled Run 2 (plsql_profiler_runs)

Run Date Total Time1 Comment
2 22-MAY-09 09:48:10 143.48 SUBSTR: proftest2(proftest_table)
Note 1: Total Time is in seconds

Profiled PL/SQL Libraries (plsql_profiler_units)

Unit Owner Name Type Timestamp Total Time1 Text Header
1 PROFTEST PROFTEST2 PROCEDURE 22-MAY-09 09:33:32 0.47
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.15 339 0.00 0.00 1 PROFTEST PROFTEST2 PROCEDURE 55 dbms_sql.column_value(c, 1, v_rowcnt);
2 0.11 340 0.00 0.00 1 PROFTEST PROFTEST2 PROCEDURE 52 while dbms_sql.fetch_rows(c) = 1 loop
3 0.07 339 0.00 0.00 1 PROFTEST PROFTEST2 PROCEDURE 54 v_errcontext := 'dbms_sql.column_value';
4 0.06 339 0.00 0.00 1 PROFTEST PROFTEST2 PROCEDURE 57 v_fetches := v_fetches + 1;
5 0.05 339 0.00 0.00 1 PROFTEST PROFTEST2 PROCEDURE 58 v_total_rowcnt := v_total_rowcnt + v_rowcnt;
6 0.02 1 0.02 0.02 1 PROFTEST PROFTEST2 PROCEDURE 77 dbms_output.put_line(in_table || ' avgs ' ||
7 0.01 1 0.00 0.00 1 PROFTEST PROFTEST2 PROCEDURE 38 c := dbms_sql.open_cursor;
Note 1: Total Time is in seconds
Note 2: Min and Max Time for one execution of this line (in seconds)

Unit:1 PROFTEST.PROFTEST2 (all_source)

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
Note Tn: Top "n" Line in terms of Total Time