oracle dbms_profiler,使用 Oracle 的 DBMS_PROFILER 包诊断与优化存储过程

从Oracle 8i开始就提供了一个具有类似功能的Package用来追踪PL/SQL 的执行过程及性能。这个Package名为DBMS_PROFILER。

在某些数据库版本中，这个包并未默认安装，如果要使用它，须在SYS用户下执行$ORACLE_HOME/ rdbms/admin下的profload.sql脚本来创建。

通过这个脚本的文件注释信息，可以了解到这段程序早在1998年就被创立出来：[oracle@jumper admin]$ cat profload.sql

Rem $Header: profload.sql 18-dec-2001.08:13:14 jmallory Exp $

Rem profload.sql

Rem Copyright (c) 1998, 2001, Oracle Corporation.  All rights reserved.

Rem    NAME

Rem      profload.sql - Load PROFiler server side packages

Rem

Rem    DESCRIPTION

Rem      Installs the server side PL/SQL profiler package

Rem

Rem    NOTES

Rem      Must be executed as sys

Rem

Rem    MODIFIED   (MM/DD/YY)

Rem    jmallory   12/18/01 - Set serveroutput off at end

Rem    jmuller    05/28/99 - Fix bug 708690: TAB -> blank

Rem    astocks    10/21/98 - Detect non-sys users

Rem    ciyer       10/10/98 - install profiler package

Rem    ciyer       10/10/98 - Created

在8i、9i版中，如果通过desc dbms_profiler命令发现这个Package没有被创建：

[oracle@jumper oracle]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Sun Mar 8 21:37:26 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> desc dbms_profiler

ERROR:

ORA-04043: object dbms_profiler does not exist

此时可以通过profload.sql调用来创建它：SQL> @?/rdbms/admin/profload.sql

Package created.

Grant succeeded.

Synonym created.

Library created.

Package body created.

Testing for correct installation

SYS.DBMS_PROFILER successfully loaded.

PL/SQL procedure successfully completed.

然后这个Package就可以被调用，用于数据库的性能诊断：SQL> desc dbms_profiler

FUNCTION FLUSH_DATA RETURNS BINARY_INTEGER

PROCEDURE FLUSH_DATA

PROCEDURE GET_VERSION

Argument Name                     Type                       In/Out  Default?

------------------------------ ----------------------- ------ --------

MAJOR                              BINARY_INTEGER           OUT

MINOR                              BINARY_INTEGER           OUT

FUNCTION INTERNAL_VERSION_CHECK RETURNS BINARY_INTEGER

FUNCTION PAUSE_PROFILER RETURNS BINARY_INTEGER

PROCEDURE PAUSE_PROFILER

FUNCTION RESUME_PROFILER RETURNS BINARY_INTEGER

PROCEDURE RESUME_PROFILER

PROCEDURE ROLLUP_RUN

Argument Name                     Type                       In/Out  Default?

------------------------------ ----------------------- ------ --------

RUN_NUMBER                        NUMBER                     IN

PROCEDURE ROLLUP_UNIT

Argument Name                     Type                       In/Out  Default?

------------------------------ ----------------------- ------ --------

RUN_NUMBER                        NUMBER                     IN

UNIT                               NUMBER                     IN

FUNCTION START_PROFILER RETURNS BINARY_INTEGER

Argument Name                     Type                       In/Out Default?

------------------------------ ----------------------- ------ --------

RUN_COMMENT                       VARCHAR2                  IN       DEFAULT

RUN_COMMENT1                      VARCHAR2                  IN       DEFAULT

RUN_NUMBER                        BINARY_INTEGER           OUT

PROCEDURE START_PROFILER

Argument Name                     Type                       In/Out Default?

------------------------------ ----------------------- ------ --------

RUN_COMMENT                       VARCHAR2                  IN       DEFAULT

RUN_COMMENT1                      VARCHAR2                  IN       DEFAULT

PROCEDURE START_PROFILER

Argument Name                     Type                       In/Out Default?

------------------------------ ----------------------- ------ --------

RUN_COMMENT                       VARCHAR2                  IN       DEFAULT

RUN_COMMENT1                      VARCHAR2                  IN       DEFAULT

FUNCTION STOP_PROFILER RETURNS  BINARY_INTEGER

PROCEDURE STOP_PROFILER

接下来，如果想要验证某个用户的PL/SQL代码的运行，须在该用户下执行另外一个重要的脚本：$ORACLE_HOME/rdbms/admin/proftab.sql

这个脚本用来创建repository tables，包含PLSQL_PROFILER_RUNS、PLSQL_PROFILER_UNITS、PLSQL_PROFILER_DATA这三张表。

当然还需要确保测试用户具有执行dbms_profile包的权限。

在待优化的PL/SQL程序前后加上运行dbms_profiler包的代码，如下：BEGIN

DBMS_PROFILER.START_PROFILER('any comment');

...'your pl/sql code'

DBMS_PROFILER.STOP_PROFILER;

...

END;

/

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

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

执行PROF.zip文件中解包出来的脚本，可以列出数据库中记录的采样数据，然后根据输入的ID生成一个HTML格式的报告：SQL> @/tmp/prof/profiler.sql

RUNID  RUN_DATE                RUN_COMMENT

---------- --------------------- --------------------

1  08-MAR-09 18:27:27     CREATE RECORD

Usage:

sqlplus apps/

SQL> START profiler.sql

Enter value for 1: 1

这个报告会列出PL/SQL执行的详细状况，包括最消耗资源的Top 10源码行：

以及进一步的源码执行情况与各步骤的时间消耗：

这是一个非常有价值的工具，在此介绍给大家，它在很多实际的环境中帮助我们定位了很多复杂的PL/SQL问题。