配置檔案管理器DBMS_PROFILER建立和使用指南

dbms_profiler用來測試PL/SQL程式碼非常有用，比如找出哪一段程式碼比較耗時，也可以用來比較不同演算法之間的差異，進行效能調整。

C:\Users\Administrator.WIN-20100719IOX>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 3月 18 10:39:06 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> @?\rdbms\admin\profload.sql

程式包已建立。

授權成功。

同義詞已建立。

庫已建立。

程式包體已建立。

Testing for correct installation
SYS.DBMS_PROFILER successfully loaded.

PL/SQL 過程已成功完成。

SQL> conn hr/hr
已連線。

SQL> @?\rdbms\admin\proftab.sql
drop table plsql_profiler_data cascade constraints
           *
第 1 行出現錯誤:
ORA-00942: 表或檢視不存在
drop table plsql_profiler_units cascade constraints
           *
第 1 行出現錯誤:
ORA-00942: 表或檢視不存在

drop table plsql_profiler_runs cascade constraints
           *
第 1 行出現錯誤:
ORA-00942: 表或檢視不存在

drop sequence plsql_profiler_runnumber
              *
第 1 行出現錯誤:
ORA-02289: 序列不存在

 

表已建立。

註釋已建立。

表已建立。

註釋已建立。

表已建立。

註釋已建立。

序列已建立。

SQL>

檢視已建立。

檢視已建立。

檢視已建立。

檢視已建立。

程式包已建立。

沒有錯誤。

程式包體已建立。

沒有錯誤。

SQL>

e.g.:

SQL> conn hr/hr
已連線。
SQL> create or replace procedure do_mod
  2  as
  3      cnt number  :=  0;
  4  begin
  5      dbms_profiler.start_profiler( 'mod' );
  6      for i in 1 .. 500000
  7      loop
  8          cnt := cnt + 1;
  9          if ( mod(cnt,1000) = 0 )
 10          then
 11              commit;
 12          end if;
 13      end loop;
 14      dbms_profiler.stop_profiler;
 15  end;
 16  /

過程已建立。

SQL> create or replace procedure no_mod
  2  as
  3      cnt number  :=  0;
  4  begin
  5      dbms_profiler.start_profiler( 'no mod' );
  6      for i in 1 .. 500000
  7      loop
  8          cnt := cnt + 1;
  9          if ( cnt = 1000 )
 10          then
 11              commit;
 12              cnt := 0;
 13          end if;
 14      end loop;
 15      dbms_profiler.stop_profiler;
 16  end;
 17  /

過程已建立。

然後執行：

SQL> exec no_mod

PL/SQL 過程已成功完成。

SQL> exec do_mod

PL/SQL 過程已成功完成。

Oracle自帶的profsum.sql比較耗時，可以使用tom修改過的profsum.sql指令碼來生成Report。

Oracle自帶的profsum.sql指令碼產生的Report比Tom的profsum.sql指令碼所產生的Report詳細，具體的結果請檢視示例。

 

每次執行profsum.sql指令碼之前，需要把舊的資料刪除:

truncate table plsql_profiler_data;
delete from plsql_profiler_units cascade;
delete from plsql_profiler_runs cascade;

SQL> @?\rdbms\admin\profsum.sql

4.在PLSQL Developer裡運用Profiler
以上介紹的是通過手工方法應用Profiler，使用相對比較煩雜，下面將詳細介紹在PLSQL DEVELOPER 應用Profiler。
4.1、開啟test window
方法一、新建一個test window，在test window中輸入你要執行的PLSQL指令碼。
方法二、選擇要跟蹤執行的儲存過程，右鍵快捷選單選擇Test，如果是Package那麼先右鍵快捷選單裡選擇View，在開啟的Package的儲存過程列表裡右鍵快捷選單選擇Test，

4.3、執行完成後，切換到profiler選項卡
每列的詳細意義如下：
unit        --單元名稱，即執行的儲存過程，包括其呼叫的過程
line        --程式碼行號
total time    --此行執行時間(顏色長度表示本行程式碼的執行時間與最長程式碼執行時間的百分比圖)
occurrences    --此行執行次數
text        --對應程式碼行，對於加密的程式碼，將不能顯示
Average time    —平均執行時間
aximum time    --最大執行時間
minimum time    --最小執行時間（以上三個時間預設不顯示，可以通過配置對話方塊選擇顯示，參加4.4）

列表中顯示的原始碼只顯示一行，如果要定位則可以在對應的行中開啟右鍵，選擇[Go to unit line] ，這樣就會直接跳到對應的原始碼位置。
Profiler皮膚的工具欄說明：
a、顯示配置對話方塊
b、重新整理
c、刪除當前執行號的資料
d、Run 顯示當前的系統的所有Profiler列表，預設為當前的跟蹤
e、Unit 顯示本次跟蹤的單元列表資訊（執行時間），預設為所有單元的執行時間
4.4、Profiler配置對話方塊
Available Columns        --可用列
Selected Columns        --選擇要檢視的列
Time units            --時間單位(秒、毫秒、微秒)
occurrences        --是否顯示執行0次的處理語句
Graphical time display    --用圖形顯示處理時間的顏色深度百分比

 
A.附加profsum.sql程式碼：
set echo off
set linesize 5000
set trimspool on
set serveroutput on
set termout off

column owner format a11
column unit_name format a14
column text format a21 word_wrapped
column runid format 9999
column secs  format 999.99
column hsecs format 999.99
column grand_total  format 9999.99
column run_comment format a11 word_wrapped
column line# format 99999
column pct format 999.9
column unit_owner format a11

spool profsum.out

/* Clean out rollup results, and recreate */
update plsql_profiler_units set total_time = 0;

execute prof_report_utilities.rollup_all_runs;

prompt =
prompt =
prompt ====================
prompt Total time
select grand_total/1000000000 as grand_total
  from plsql_profiler_grand_total;

prompt =
prompt =
prompt ====================
prompt Total time spent on each run
select runid,
       substr(run_comment,1, 30) as run_comment,
       run_total_time/1000000000 as secs
  from (select a.runid, sum(a.total_time)  run_total_time, b.run_comment
          from plsql_profiler_units a, plsql_profiler_runs b
         where a.runid = b.runid group by a.runid, b.run_comment )
 where run_total_time > 0
 order by runid asc;


prompt =
prompt =
prompt ====================
prompt Percentage of time in each module, for each run separately

select p1.runid,
       substr(p2.run_comment, 1, 20) as run_comment,
       p1.unit_owner,
       decode(p1.unit_name, '', '',
                    substr(p1.unit_name,1, 20)) as unit_name,
       p1.total_time/1000000000 as secs,
       TO_CHAR(100*p1.total_time/p2.run_total_time, '999.9') as percentage
  from plsql_profiler_units p1,
       (select a.runid, sum(a.total_time)  run_total_time, b.run_comment
          from plsql_profiler_units a, plsql_profiler_runs b
         where a.runid = b.runid group by a.runid, b.run_comment ) p2
 where p1.runid=p2.runid
   and p1.total_time > 0
   and p2.run_total_time > 0
   and  (p1.total_time/p2.run_total_time)  >= .01
 order by p1.runid asc, p1.total_time desc;

column secs form. 9.99
prompt =
prompt =
prompt ====================
prompt Percentage of time in each module, summarized across runs
select p1.unit_owner,
       decode(p1.unit_name, '', '', substr(p1.unit_name,1, 25)) as unit_name,
       p1.total_time/1000000000 as secs,
       TO_CHAR(100*p1.total_time/p2.grand_total, '99999.99') as percentage
  from plsql_profiler_units_cross_run p1,
       plsql_profiler_grand_total p2
 order by p1.total_time DESC;


prompt =
prompt =
prompt ====================
prompt Lines taking more than 1% of the total time, each run separate
select p1.runid as runid,
       p1.total_time/10000000 as Hsecs,
        p1.total_time/p4.grand_total*100 as pct,
       substr(p2.unit_owner, 1, 20) as owner,
       decode(p2.unit_name, '', '', substr(p2.unit_name,1, 20)) as unit_name,
       p1.line#,
       ( select p3.text
           from all_source p3
          where p3.owner = p2.unit_owner and
                p3.line = p1.line# and
                p3.name=p2.unit_name and
                p3.type not in ( 'PACKAGE', 'TYPE' )) text
  from plsql_profiler_data p1,
       plsql_profiler_units p2,
       plsql_profiler_grand_total p4
 where (p1.total_time >= p4.grand_total/100)
   AND p1.runID = p2.runid
   and p2.unit_number=p1.unit_number
 order by p1.total_time desc;

prompt =
prompt =
prompt ====================
prompt Most popular lines (more than 1%), summarize across all runs
select p1.total_time/10000000 as hsecs,
        p1.total_time/p4.grand_total*100 as pct,
       substr(p1.unit_owner, 1, 20) as unit_owner,
       decode(p1.unit_name, '', '',
                 substr(p1.unit_name,1, 20)) as unit_name,
       p1.line#,
       ( select p3.text from all_source p3
          where (p3.line = p1.line#) and
                (p3.owner = p1.unit_owner) AND
                (p3.name = p1.unit_name) and
                (p3.type not in ( 'PACKAGE', 'TYPE' ) ) ) text
  from  plsql_profiler_lines_cross_run p1,
        plsql_profiler_grand_total p4
 where (p1.total_time >= p4.grand_total/100)
 order by p1.total_time desc;

execute prof_report_utilities.rollup_all_runs;

prompt =
prompt =
prompt ====================
prompt  Number of lines actually executed in different units (by unit_name)

select p1.unit_owner,
       p1.unit_name,
       count( decode( p1.total_occur, 0, null, 0))  as lines_executed ,
       count(p1.line#) as lines_present,
       count( decode( p1.total_occur, 0, null, 0))/count(p1.line#) *100
                                       as pct
  from plsql_profiler_lines_cross_run p1
 where (p1.unit_type in ( 'PACKAGE BODY', 'TYPE BODY',
                          'PROCEDURE', 'FUNCTION' )  )
 group by p1.unit_owner, p1.unit_name;


prompt =
prompt =
prompt ====================
prompt  Number of lines actually executed for all units
select count(p1.line#) as lines_executed
  from plsql_profiler_lines_cross_run p1
 where (p1.unit_type in ( 'PACKAGE BODY', 'TYPE BODY',
                          'PROCEDURE', 'FUNCTION' )  )
    AND p1.total_occur > 0;


prompt =
prompt =
prompt ====================
prompt  Total number of lines in all units
select count(p1.line#) as lines_present
  from plsql_profiler_lines_cross_run p1
 where (p1.unit_type in ( 'PACKAGE BODY', 'TYPE BODY',
                          'PROCEDURE', 'FUNCTION' )  );

spool off
set termout on
edit profsum.out
set linesize 131


B.另外，profrep.sql指令碼在$ORACLE_HOME\plsql\demo目錄下，事先需要安裝companion cd。the other scripts are in the demo directories (to be loaded from the companion cd)

C.另一個例子，

set echo on
clear screen
create or replace procedure do_something
as
    l_x    number := 0;
begin
    for i in 1 .. 100
    loop
        l_x := l_x + 1;
    end loop;
end;
/
pause

clear screen
create or replace
function fact_recursive( n int ) return number
as
begin
        if ( n = 1 )
        then
            return 1;
        else
            if ( mod(n,3) = 0 )
            then
                do_something;
            end if;
            return n * fact_recursive(n-1);
        end if;
end;
/
pause

clear screen
create or replace
function fact_iterative( n int ) return number
as
        l_result number default 1;
begin
        for i in 2 .. n
        loop
            if ( mod(i,3) = 0 )
            then
                do_something;
            end if;
            l_result := l_result * i;
        end loop;
        return l_result;
end;
/
pause

clear screen
set serveroutput off
exec dbms_profiler.start_profiler( 'factorial recursive' )
begin
    for i in 1 .. 100 loop
        dbms_output.put_line( fact_recursive(50) );
    end loop;
end;
/
exec dbms_profiler.stop_profiler
exec dbms_profiler.start_profiler( 'factorial iterative' )
begin
    for i in 1 .. 100 loop
        dbms_output.put_line( fact_iterative(50) );
    end loop;
end;
exec dbms_profiler.stop_profiler
set serveroutput on size 1000000
pause

@?/rdbms/admin/profsum