对比PL/SQL profiler剖析结果

1、用于实施剖析的存储过程
[sql] 
--环境  
sys@USBO> select * from v$version where rownum<2;  
  
BANNER  
------------------------------------------------------------------------------------------------------------  
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production  
  
--用于实施剖析的原过程，下面是使用字面量的过程，注意代码中包含了启用与停止profiler  
scott@USBO> create or replace procedure binds  
  2  is  
  3   v_num number;  
  4  begin  
  5  dbms_profiler.start_profiler('binds');  
  6   for i in 1..100000 loop  
  7     v_num := dbms_random.random;  
  8     insert into t1 values (v_num,v_num);  
  9   end loop;  
 10  dbms_profiler.stop_profiler;  
 11  end;  
 12  /  
  
Procedure created.  
  
--用于实施剖析的原过程，下面是使用绑定量的过程，注意代码中包含了启用与停止profiler  
scott@USBO> create or replace procedure literals  
  2  is  
  3   v_num number;  
  4  begin  
  5  dbms_profiler.start_profiler('literals');  
  6   for i in 1..100000 loop  
  7     v_num := dbms_random.random;                 
  8     execute immediate   
  9      'insert into t1 values ('||v_num||','||v_num||')';  
 10   end loop;  
 11  dbms_profiler.stop_profiler;  
 12  end;  
 13  /   
  
Procedure created.  
  
--Author : Leshami  
--Blog   : http://blog.csdn.net/leshami  
  
--清除剖析表中的历史数据(每次剖析对比前执行)  
scott@USBO> delete from plsql_profiler_data;  
  
scott@USBO> delete from plsql_profiler_units;  
  
scott@USBO> delete from plsql_profiler_runs;  
  
--分别执行两个不同的过程  
scott@USBO> exec literals;  
  
scott@USBO> exec binds;  
2、提取剖析对比结果
[sql] 
scott@USBO> @profsum  
  
2 rows updated.  
  
PL/SQL procedure successfully completed.  
  
=  
=  
====================  
total time  
  
GRAND_TOTAL  
-----------       
      58.93  
  
=  
=  
====================  
total time spent on each run  
  
  RUNID RUN_COMMENT      SECS  
------- ----------- ---------  
      7 literals        53.19  
      8 binds            5.75  
  
=  
=  
====================  
percentage of time in each module, for each run separately  
  
  RUNID RUN_COMMENT UNIT_OWNER  UNIT_NAME           SECS PERCEN  
------- ----------- ----------- -------------- --------- ------  
      7 literals    SCOTT       LITERALS           53.19  100.0  
      8 binds       SCOTT       BINDS               5.75  100.0  
  
=  
=  
====================  
percentage of time in each module, summarized across runs  
  
UNIT_OWNER  UNIT_NAME           SECS PERCENTAG  
----------- -------------- --------- ---------  
SCOTT       LITERALS           53.19     90.25  
SCOTT       BINDS               5.75      9.75  
  
=  
=  
====================  
lines taking more than 1% of the total time, each run separate  
  
  RUNID     HSECS     PCT OWNER       UNIT_NAME       LINE# TEXT  
------- --------- ------- ----------- -------------- ------ ---------------------  
      7   5221.18    88.6 SCOTT       LITERALS            8 execute immediate  
      8    502.97     8.5 SCOTT       BINDS               8 insert into t1 values  
                                                            (v_num,v_num);  
  
      7     73.04     1.2 SCOTT       LITERALS            7 v_num :=  
                                                            dbms_random.random;  
  
=  
=  
====================  
most popular lines (more than 1%), summarize across all runs  
  
    HSECS     PCT UNIT_OWNER  UNIT_NAME       LINE# TEXT  
--------- ------- ----------- -------------- ------ ---------------------  
  5221.18    88.6 SCOTT       LITERALS            8 execute immediate  
   502.97     8.5 SCOTT       BINDS               8 insert into t1 values  
                                                    (v_num,v_num);  
  
    73.04     1.2 SCOTT       LITERALS            7 v_num :=  
                                                    dbms_random.random;  
  
PL/SQL procedure successfully completed.  
  
=  
=  
====================  
Number of lines actually executed in different units (by unit_name)  
  
UNIT_OWNER  UNIT_NAME      LINES_EXECUTED LINES_PRESENT     PCT  
----------- -------------- -------------- ------------- -------  
SCOTT       LITERALS                    4             7    57.1  
SCOTT       BINDS                       4             7    57.1  
  
=  
=  
====================  
Number of lines actually executed for all units  
  
LINES_EXECUTED  
--------------  
             8  
  
=  
=  
====================  
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