ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 配置文件管理器DBMS_PROFILER创建和使用指南

配置文件管理器DBMS_PROFILER创建和使用指南

原创 Linux操作系统 作者:landf 时间:2011-03-18 11:02:24 0 删除 编辑
dbms_profiler用来测试PL/SQL代码非常有用,比如找出哪一段代码比较耗时,也可以用来比较不同算法之间的差异,进行性能调整。
1.配置dbms_profiler的运行环境
sys用户下
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 过程已成功完成。
在需要运行测试procedure的用户下创建profiler相关表和序列
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>
在需要运行测试procedure的用户下,创建profiler相关视图及包
SQL> @?\plsql\demo\profrep.sql
视图已创建。

视图已创建。

视图已创建。

视图已创建。

程序包已创建。
没有错误。
程序包体已创建。
没有错误。

SQL>
2.使用dbms_profiler的例子
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 过程已成功完成。
3.生成Report
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.2、进入调试窗口,在1处单击,打开profiler开关,在2处单击或按F8执行
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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14359/viewspace-689837/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 使用Statspack
下一篇: TKPROF
请登录后发表评论 登录
全部评论

注册时间:2008-02-14

  • 博文量
    68
  • 访问量
    494675