对比PL/SQL profiler剖析结果(五)
un_system_info);
end if;
end print_run_header;
--
-- Routines for formatting and printing profiler data
--
-- Format and print one line of data and source
--
procedure print_line(line number, lcount number,
running_total number, source varchar2) is
outline varchar2(200); -- temp buffer to hold output
cline varchar2(40); -- number of times this line was executed
total_time varchar2(40); -- total time executing this line
ave_time varchar2(40); -- average time for this line
ave_nano number;
begin
outline := to_char(line, '99G999');
-- format and store away the count and running total
if (lcount is not null) then
cline := to_char(lcount, '99G999G999');
end if;
if (running_total is not null) then
total_time := substr(to_char(running_total/1000000000), 1, 9);
end if;
-- compute average time executing this line and stash it away
if (lcount > 0) then
ave_nano := running_total/lcount;
ave_time := substr(to_char(ave_nano/1000000000), 1, 9);
end if;
-- now put together all the data, the source line and output it
--
outline := outline || ' ' || cline || ' ' ||
total_time || ' ' || ave_time || ' ';
if source is not null then
outline := rpad(outline, 55) || substr(source, 1, (length(source) - 1));
end if;
dbms_output.put_line(outline);
end print_line;
-- insert a c2 row into the window
--
procedure insert_into_window(c2row c2%rowtype) is
next_row pls_integer;
begin
next_row := mod((prev_row + 1), window);
c2tab(next_row) := c2row;
prev_row := next_row;
end insert_into_window;
-- clear out the window (for reuse later)
--
procedure clear_window is
empty_tab c2tab_t;
begin
-- throw away table
c2tab := empty_tab;
prev_row := 0;
end clear_window;
-- print the window and throw it away
--
procedure print_window(start_separator IN boolean) is
next_row pls_integer;
iter pls_integer;
c2row c2%rowtype;
first_line boolean := true;
ct number := c2tab.count;
begin
if (window <= 0) then
return;
end if;
-- compute first row
next_row := mod((prev_row + 1), window);
-- Detect the case where the window hasn't wrapped around yet
if (not c2tab.exists(next_row)) then
next_row := c2tab.next(next_row);
if (next_row is NULL) then
next_row := c2tab.first;
end if;
end if;
for iter in 1..window loop
exit when (ct <= 0);
if (c2tab.exists(next_row)) then
c2row := c2tab(next_row);
if (first_line and (last_line_printed < c2row.line-1)) then
dbms_output.put_line('.');
dbms_output.put_line('.');
dbms_output.put_line('.');
end if;
first_line := false;
print_line(c2row.line, c2row.total_occur,
c2row.total_time, c2row.text);
last_line_printed := c2row.line;
ct := ct - 1;
end if;
next_row := mod((next_row + 1), window);
end loop;
if (not start_separator) then
last_line_printed := 999999999;
end if;
clear_window;
end print_window;
procedure print_unit(run_number number, unit number) is
cursor cuhdr(run number, unit number) is
select * from plsql_profiler_units
where runid = run and unit_number = unit;
unit_row cuhdr%rowtype;
joined_row c2%rowtype;
lcount number;
-- print a trailing window after the last interesting line
print_trailing_window boolean := false;
trail_count pls_integer := 0;
begin
dbms_profiler.rollup_unit(run_number, unit);
-- fetch unit name and type information
open cuhdr(run_number, unit);
fetch cuhdr into unit_row;
close cuhdr;
-- If there was an error previously, cursor "c2" might be open
-- Close it, and ignore the error if it already was
--
begin
close c2;
exception
when others then
null;
end;
open c2(run_number, unit,
unit_row.unit_owner, unit_row.unit_name, unit_row.unit_type);
loop
fetch c2 into joined_row;
exit when c2%notfound;
lcount := joined_row.total_occur;
-- if there is interesting data at this line, print its prefix window
-- and the data itself; else stash away this line c2tab - it may get
-- printed as part of another line's window
if (lcount is not null and lcount <>
0) then
print_window (start_separator => false);
print_line(joined_row.line, joined_row.total_occur,
joined_row.total_time, joined_row.text);
print_trailing_window := true;
trail_count := 0;
else
insert_into_window(joined_row);
-- if we are now accumulating rows after a row with data, increment
-- count of rows accumulated since last interesting row. if we have
-- accumulated a window full of data, print it out.
if (print_trailing_window) then
trail_count := trail_count + 1;
if (trail_count = window) then
print_window(start_separator => true);
print_trailing_window := false;
end if;
end if;
end if;
end loop;
close c2;
-- if the window isn't empty, print it out.
if (print_trailing_window) then
print_window(start_separator => false);
print_trailing_window := false;
end if;
clear_window;
exception
when others then
report_exception('Print_Unit', false);
end print_unit;
procedure print_run(run_number number) is
cursor cunits(run_number number) is
select unit_number from plsql_profiler_units
where runid = run_number order by unit_number asc;
begin
print_run_header(run_number);
dbms_profiler.rollup_run(run_number);
for unitrec in cunits(run_number) loop
print_unit_header(run_number, unitrec.unit_number);
print_unit(run_number, unitrec.unit_number);
end loop;
exception
when others then
report