对比PL/SQL profiler剖析结果(六)
_exception('Print_Run', false);
end print_run;
procedure print_detailed_report is
cursor crunid is
select runid from plsql_profiler_runs order by runid asc;
begin
dbms_output.enable(999999);
dbms_output.put('=================================');
dbms_output.put('trace info');
dbms_output.put_line('=================================');
rollup_all_runs();
for runidrec in crunid loop
print_run(runidrec.runid);
end loop crunid;
dbms_output.new_line;
dbms_output.put('======================================');
dbms_output.put_line('======================================');
exception
when others then
report_exception('Print_Detailed_Report', false);
end print_detailed_report;
procedure print_summarized_unit(owner_name varchar2,
unit_name varchar2, unit_type varchar2) is
cursor c3(uowner varchar2, uname varchar2, utype varchar2) is
select line, text, total_occur, total_time, min_time, max_time
from all_source t1, plsql_profiler_lines_cross_run t2
where owner = uowner and name = uname and type = utype
and t2.unit_owner (+) = uowner and t2.unit_name (+) = uname
and t2.unit_type (+) = utype and t2.line# (+) = line
order by line asc;
datarec c3%rowtype;
lcount number;
-- print a trailing window after the last interesting line
print_trailing_window boolean := false;
trail_count pls_integer := 0;
begin
open c3(owner_name, unit_name, unit_type);
loop
fetch c3 into datarec;
exit when c3%notfound;
lcount := datarec.total_occur;
if (lcount is not null and lcount <> 0) then
print_window (start_separator => false);
print_line(datarec.line,
datarec.total_occur,
datarec.total_time,
datarec.text);
print_trailing_window := true;
trail_count := 0;
else
insert_into_window(datarec);
-- 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 c3;
-- 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_Summarized_Unit', false);
end print_summarized_unit;
procedure print_summarized_report is
cursor cunits is
select unit_owner, unit_name, unit_type
from plsql_profiler_units_cross_run
order by unit_owner, unit_name asc;
begin
rollup_all_runs();
dbms_output.enable(9999999);
dbms_output.put('================== Profiler report - all runs rolled up');
dbms_output.put_line(' ===================');
for unitrec in cunits loop
dbms_output.put('Unit ');
dbms_output.put(unitrec.unit_owner);
dbms_output.put('.');
dbms_output.put(unitrec.unit_name);
dbms_output.put_line(':');
print_summarized_unit(unitrec.unit_owner,
unitrec.unit_name, unitrec.unit_type);
end loop;
dbms_output.new_line;
dbms_output.put('======================================');
dbms_output.put_line('===================================