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

最新推荐文章于 2024-08-15 01:26:58 发布

congbeili7411

最新推荐文章于 2024-08-15 01:26:58 发布

阅读量201

点赞数

文章标签：数据库

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

连接到:
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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14359/viewspace-689837/

congbeili7411

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
配置文件管理器DBMS_PROFILER创建和使用指南

dbms_profiler用来测试PL/SQL代码非常有用，比如找出哪一段代码比较耗时，也可以用来比较不同算法之间的差异，进行性能调整。1.配置dbms_profiler的运行环境sys用户下...
复制链接

扫一扫