使用dbms_profiler包测试存储过程性能

dbms_profiler用来测试PL/SQL代码非常有用，比如找出哪一段代码比较耗时，也可以用来比较不同算法之间的差异。也得到了一些第三方工具的支持，如PLSQL DEVELOPER。

1、安装

1.1、以sys用户创建dbms_profiler包

SQL> conn /as sysdba
SQL> desc dbms_profiler    --先确信dbms_profiler包是否存在，如果不存在则通过下面的方式创建
SQL> @?/rdbms/admin/profload.sql
包主要使用的函数是：
start_profiler        --启动 profiler
stop_profiler            --停止 profiler

1.2、创建一个用于存放跟踪信息的用户，及其prof表和序列的同义词

SQL> CREATE USER profiler IDENTIFIED BY iamwangnc;
SQL> grant connect,resource to profiler;
SQL> CREATE PUBLIC SYNONYM plsql_profiler_runs FOR profiler.plsql_profiler_runs;
SQL> CREATE PUBLIC SYNONYM plsql_profiler_units FOR profiler.plsql_profiler_units;
SQL> CREATE PUBLIC SYNONYM plsql_profiler_data FOR profiler.plsql_profiler_data;
SQL> CREATE PUBLIC SYNONYM plsql_profiler_runnumber FOR profiler.plsql_profiler_runnumber;

1.3、用profiler用户创建prof表和序列，并赋权

SQL> conn profiler/iamwangnc
SQL> @?/rdbms/admin/proftab.sql
SQL> GRANT SELECT ON plsql_profiler_runnumber TO PUBLIC;
SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON plsql_profiler_data TO PUBLIC;
SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON plsql_profiler_units TO PUBLIC;
SQL> GRANT SELECT,INSERT,UPDATE,DELETE ON plsql_profiler_runs TO PUBLIC;
备注：
plsql_profiler_runs        --prof运行信息
plsql_profiler_units        --prof每个单元信息
plsql_profiler_data        --prof每个单元的详细数据
plsql_profiler_runnumber    --用来生成prof唯一运行编号的序列
               
2、应用实例

SQL> conn u_test/iamwangnc
SQL> create table tab_test (a int);
SQL> CREATE OR REPLACE PROCEDURE sp_test
AS
BEGIN
   FOR I IN 1 .. 100
   LOOP
      INSERT INTO tab_test
        VALUES   (I);
   END LOOP;
   COMMIT;
END;
/
SQL> set serverout on
SQL> DECLARE
   v_run_number   integer;
   v_temp1        integer;
BEGIN
   --启动profiler
   sys.DBMS_PROFILER.start_profiler (run_number => v_run_number);
   --显示当前跟踪的运行序号(后面查询要用)
   DBMS_OUTPUT.put_line ('run_number:' || v_run_number);
   --运行要跟踪的PLSQL
   sp_test;
   --停止profiler
   sys.DBMS_PROFILER.stop_profiler;
END;
/
run_number:1
记住输出的运行号。

3、查询结果

SQL> set lines 256 pages 100

获得本次prof的基本运行信息：
SQL> select runid,run_owner,run_date,run_total_time from plsql_profiler_runs;

     RUNID RUN_OWNER                        RUN_DATE            RUN_TOTAL_TIME
---------- -------------------------------- ------------------- --------------
         1 U_TEST                           2008-11-26.17:18:39       54215000

根据运行号和单元名（即测试的存储过程名）获得本次prof的单元信息：
SQL> select unit_number,unit_type,unit_owner,unit_name,unit_timestamp,total_time from plsql_profiler_units where runid = 1 and unit_name = 'SP_TEST';

UNIT_NUMBER UNIT_TYPE                        UNIT_OWNER                       UNIT_NAME                        UNIT_TIMESTAMP      TOTAL_TIME
----------- -------------------------------- -------------------------------- -------------------------------- ------------------- ----------
          2 PROCEDURE                        U_TEST                           SP_TEST                          2008-11-26.17:17:56          0

根据运行号和单元号获得该存储过程每行运行的统计信息：
SQL> select runid,unit_number,line#,total_occur,total_time,min_time,max_time from plsql_profiler_data where runid = 1 and unit_number = 2;

     RUNID UNIT_NUMBER      LINE# TOTAL_OCCUR TOTAL_TIME   MIN_TIME   MAX_TIME
---------- ----------- ---------- ----------- ---------- ---------- ----------
         1           2          1           0       3000       3000       3000
         1           2          4         101     179000       1000       3000
         1           2          6         100    7084000      17000    1141000
         1           2          9           1     336000     336000     336000
         1           2         10           1       2000       2000       2000

以上信息都不怎么直观，通过下面的sql更加直观展示：
SQL> col text format a60
SQL> SELECT   d.line#,                 --代码行号
    s.text,                         --源代码
       d.total_time,                   --总共运行时间(单位10000亿分之一秒)
       d.total_occur,                  --总共运行次数
       d.min_time,                     --最小运行时间
       d.max_time                      --最大运行时间          
    FROM   plsql_profiler_data d, sys.all_source s, plsql_profiler_units u
   WHERE       d.runid = 1            --运行号
    and u.unit_name = 'SP_TEST'        --单元名，即被测试的存储过程名
       AND u.runid = d.runid
    AND d.unit_number = u.unit_number
    AND d.total_occur <> 0
    AND s.TYPE(+) = u.unit_type
    AND s.owner(+) = u.unit_owner
    AND s.name(+) = u.unit_name
    AND d.line# = NVL (s.line, d.line#)
   ORDER BY   u.unit_number, d.line#;

     LINE# TEXT                                                         TOTAL_TIME TOTAL_OCCUR   MIN_TIME   MAX_TIME
---------- ------------------------------------------------------------ ---------- ----------- ---------- ----------
         4    FOR I IN 1 .. 100                                             179000         101       1000       3000
         6       INSERT INTO tab_test                                      7084000         100      17000    1141000
         9    COMMIT;                                                       336000           1     336000     336000
        10 END;                                                               2000           1       2000       2000