0

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

eygle 2019-09-05
326

从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/<pwd>

SQL> START profiler.sql <runid>

 

Enter value for 1: 1

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

2038.JPG


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

2039.JPG


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


最后修改时间:2019-09-05 10:56:59
「喜欢文章,快来给作者赞赏墨值吧」
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论

关注
最新发布
暂无内容,敬请期待...
数据库资讯
最新 热门 更多
本月热门
近期活动
全部
暂无活动,敬请期待...
相关课程
全部