Implementing and Using the PL/SQL Profiler

在公司内部有时候会给其它的同事进行一些内部的培训,这里大部分的同事都在用PL/SQL写程序,因此关于PL/SQL程序的性能调整是很多人关心的话题。

对于一大段PL/SQL程序,如何快速找到这段程序里面最耗费时间的部分在哪里?如何快速地定位改程序的bottleneck?这时候需要dbms_profiler包。

1. 首先需要检查dbms_profiler包有没有安装。如果没有则需要通过sys用户运行$ORACLE_HOME/admin/profload.sql来创建该包。

2. 将该包的执行权限赋予待优化的PL/SQL程序属主。

3. 使用PL/SQL程序属主登录数据库,运行$ORACLE_HOME/rdbms/admin/proftab.sql来创建repository tables,包含PLSQL_PROFILER_RUNS,PLSQL_PROFILER_UNITS,PLSQL_PROFILER_DATA这三张表。

4. 在待优化的PL/SQL程序前后加上运行dbms_profiler包的代码,如下:

BEGIN
  DBMS_PROFILER.START_PROFILER('any comment');
  ...'your pl/sql code'
  DBMS_PROFILER.STOP_PROFILER;
  ...
END;
/

5. 运行该PL/SQL程序,之后检查PLSQL_PROFILER_DATA表内容,从中就可以看到每一行代码的执行次数,每行代码总共的执行时间。

另外从Metalink上Note:243755.1中可以下载PROF.zip,包含的程序可以用来生成直观的profiler结果报告,一个html样式的报告。

From Drop Box

该报告可以列出Top 10最消耗时间的代码,强烈推荐使用。

5 Comments Add yours

  1. cityvigil says:

    请教一下:
    如果我想测试 一个trigger 里的每行的执行效率。这个trigger又调用了一个procedure过程,
    那么我在测试 时使用
    BEGIN
    DBMS_PROFILER.START_PROFILER(‘any comment’);
    insert into t_1 values(1,2) ;– 这样insert的语句,触发上面的trigger
    DBMS_PROFILER.STOP_PROFILER;

    END;
    --
    可是出来的html报告只是包含你调用的引用的的哪个触发器和过程。
    并没有列出里面触发器里每一行的代码。
    不知道这样的结果是不是正常的,还是有别的方法可以看到trigger和procedure里每行的执行时间

    谢谢!

  2. kamus says:

    @cityvigil
    将profiler放在最终你想检查性能的存储过程里。

  3. cityvigil says:

    发现PROF.zip这个包里的profiler.sql是能过serveroutput on size 1000000 这个来打印html的,最大只能输出这么多。
    如果对于一个代码量很大的过程
    就会报ERROR: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at “SYS.DBMS_OUTPUT”, line 238 ORA-06512: at “SYS.DBMS_OUTPUT”, line 260 ORA-06512: at line 1

    哎,不知道 在11g DBMS_HPROF.START_PROFILING
    这个包能提供 什么 样的报告

  4. kamus says:

    @cityvigil
    其实我个人认为,当调整一个存储过程的时候,应该对它有一定了解了,从一个存储过程的最开始到最后做profiler并不是一个很好的选择,通常profiler都是加在有疑惑的那部分代码前后的。因此基本上serveroutput on size 1000000是够用的。

  5. cityvigil says:

    赞同!:) 3Q

Leave a Reply to cityvigil Cancel reply

Your email address will not be published. Required fields are marked *