对比PL/SQL profiler剖析结果(四)
prev_row pls_integer := 0;
procedure report_exception(which IN varchar2, reraised IN boolean) is
begin
dbms_output.new_line();
dbms_output.new_line();
dbms_output.put('======================================');
dbms_output.put_line('======================================');
dbms_output.put('Exception Number: ');
dbms_output.put(sqlcode);
dbms_output.put(' raised in routine ' || which );
if (reraised)
then
dbms_output.put(' (Will be reraised)');
end if;
dbms_output.new_line();
dbms_output.put('======================================');
dbms_output.put_line('======================================');
end;
-- compute the total time spent executing this unit - the sum of the
-- time spent executing lines in this unit (for this run)
--
procedure rollup_unit(run_number IN number, unit IN number) is
begin
dbms_profiler.rollup_unit(run_number, unit);
exception
when others then
report_exception('Rollup_Unit', true);
raise;
end rollup_unit;
-- rollup all units for the given run
--
procedure rollup_run(run_number IN number) is
begin
dbms_profiler.rollup_run(run_number);
exception
when others then
report_exception('Rollup_Run', true);
raise;
end rollup_run;
procedure rollup_all_runs is
cursor crunid is
select runid from plsql_profiler_runs order by runid asc;
begin
for runidrec in crunid loop
dbms_profiler.rollup_run(runidrec.runid);
end loop crunid;
end rollup_all_runs;
--
-- Reporting functions
--
-- Format and print information on a unit
--
procedure print_unit_header(run_number IN number, unit IN nu
mber) is
cursor cuhdr(run_number number, unit number) is
select * from plsql_profiler_units
where runid = run_number and unit_number = unit;
unit_row cuhdr%rowtype;
begin
-- fetch data for the given unit
open cuhdr(run_number, unit);
fetch cuhdr into unit_row;
close cuhdr;
-- format and print the data
dbms_output.put('Unit #');
dbms_output.put(unit_row.unit_number);
dbms_output.put(': ');
dbms_output.put(unit_row.unit_owner || '.' || unit_row.unit_name);
dbms_output.put(' - Total time: ');
dbms_output.put(to_char(unit_row.total_time/1000000000, '99999.99'));
dbms_output.put_line(' seconds');
end print_unit_header;
-- Format and print information on a run
--
procedure print_run_header(run_number IN number) is
cursor crun(run_number number) is
select * from plsql_profiler_runs where runid = run_number;
runidrec crun%rowtype;
begin
open crun(run_number);
fetch crun into runidrec;
close crun;
dbms_output.new_line();
dbms_output.new_line();
dbms_output.put('===========================');
dbms_output.put('Results for run #');
dbms_output.put(runidrec.runid);
dbms_output.put(' made on ');
dbms_output.put(to_char(runidrec.run_date, 'DD-MON-YY HH24:MI:SS'));
dbms_output.put_line(' =========================');
if (runidrec.run_comment is not null) then
dbms_output.put(' (');
dbms_output.put(runidrec.run_comment);
dbms_output.put(') ');
end if;
dbms_output.put('Run total time: ');
dbms_output.put(to_char(runidrec.run_total_time/1000000000, '99999.99'));
dbms_output.put_line(' seconds');
if (runidrec.run_system_info is not null) then
dbms_output.put_line(runidrec.r