对比PL/SQL profiler剖析结果(二)
=
=
====================
total number of lines in all units
LINES_PRESENT
-------------
14
3、生成剖析结果的脚本
[sql]
a、脚本profrep.sql
--在执行剖析前需要先执行该脚本以准备环境(仅首次使用)
Rem Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved.
Rem
Rem NAME
Rem profrep.sql
Rem
Rem DESCRIPTION
Rem PL/SQL Profiler reporting utilities
Rem
Rem NOTES
Rem The reporting procedures expect server output to be set on
Rem Some of the rollup functions commit the transaction.
Rem
-- First create the views used in the reporting package
--
create or replace view plsql_profiler_grand_total as
select sum(total_time) as grand_total from plsql_profiler_units;
create or replace view plsql_profiler_units_cross_run as
select unit_owner, unit_name, unit_type, sum(total_time) as total_time
from plsql_profiler_units group by unit_owner, unit_name, unit_type;
create or replace view plsql_profiler_lines_cross_run as
select p1.unit_owner as unit_owner, p1.unit_name as unit_name,
p1.unit_type as unit_type,
p2.line# as line#,
sum(p2.total_occur) as total_occur,
sum(p2.total_time) as total_time,
min(p2.min_time) as min_time,
max(p2.max_time) as max_time
from plsql_profiler_units p1, plsql_profiler_data p2
where p1.runid=p2.runid and p1.unit_number = p2.unit_number
group by p1.unit_owner, p1.unit_name, p1.unit_type, p2.line#;
create or replace view plsql_profiler_notexec_lines as
select owner, name, type, line, text, total_occur
from all_source t1, plsql_profiler_lines_cross_run t2
where t2.total_occur = 0 and t2.unit_owner = owner
and t2.unit_name = name and t2.unit_type = type and t2.line# = line
order by line asc;
create or replace package prof_report_utilities
authid current_user is
-- Routines to roll up profile information from line level to unit level
--
procedure rollup_unit(run_number IN number, unit IN number);
procedure rollup_run(run_number IN number);
procedure rollup_all_runs;
-- Routines to print a report, treating each run separately
--
procedure print_unit(run_number IN number, unit IN number);
procedure print_run(run_number IN number);
procedure print_detailed_report;
-- Routine to print a single report including information from each run
--
procedure print_summarized_report;
-- Set size of window for reports
procedure set_window_size(window_size IN pls_integer);
end prof_report_utilities;
/
show errors;
create or replace
package body prof_report_utilities is
-- the reports print 'window' lines of source around lines with profiler
-- data, otherwise skipping lines with no data. This is useful when
-- viewing data for units with sparse profiler data.
--
window pls_integer := 10;
last_line_printed number := 999999999;
cursor c2(run number, unit number,
owner_name varchar2, unit_name varchar2, unit_type varchar2) is
select line, text, total_occur, total_time, min_time, max_time
from all_source, plsql_profiler_data
where runid (+) = run and unit_number (+) = unit
and owner = owner_name and name = unit_name and type = unit_type
and plsql_profiler_data.line# (+) = line
order by line asc;
-- c2tab contains the window of lines around any line with interesting
-- data.
type c2tab_t is table of c2%rowtype index by binary_integer;
c2tab c2tab_t;
-- index into the window where previous row was inserted