Oracle性能优化工具profiler

Oracle提供了profiler工具包，利用该工具包可以查看PL/SQL执行过程中各模块的性能。使用方法可以参考metalink doc 243755.1。本文主要简单介绍其使用方法。
1、进入业务用户，查看dbms_profiler是否已安装，如没有安装则需执行安装脚本profiler.sql
可以看到本数据库中已安装dbms_profiler

引用

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
RUN_NUMBER                     BINARY_INTEGER          OUT
FUNCTION START_PROFILER RETURNS BINARY_INTEGER
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

2、创建数据字典，用于存放统计数据

引用

SQL> @proftab.sql
drop table plsql_profiler_data cascade constraints
           *
ERROR at line 1:
ORA-00942: table or view does not exist


drop table plsql_profiler_units cascade constraints
           *
ERROR at line 1:
ORA-00942: table or view does not exist


drop table plsql_profiler_runs cascade constraints
           *
ERROR at line 1:
ORA-00942: table or view does not exist


drop sequence plsql_profiler_runnumber
              *
ERROR at line 1:
ORA-02289: sequence does not exist



Table created.


Comment created.


Table created.


Comment created.


Table created.


Comment created.


Sequence created.

SQL> desc plsql_profiler_data
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
RUNID                                     NOT NULL NUMBER
UNIT_NUMBER                               NOT NULL NUMBER
LINE#                                     NOT NULL NUMBER
TOTAL_OCCUR                                        NUMBER
TOTAL_TIME                                         NUMBER
MIN_TIME                                           NUMBER
MAX_TIME                                           NUMBER
SPARE1                                             NUMBER
SPARE2                                             NUMBER
SPARE3                                             NUMBER
SPARE4                                             NUMBER

3、跟踪PL/SQL模块性能，如本例所示addemp为需要跟踪的PL/SQL模块

引用

SQL> BEGIN
  2  DBMS_PROFILER.START_PROFILER('only test hr procedure');
  3  addemp;
  4  DBMS_PROFILER.STOP_PROFILER;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> @profiler.sql

     RUNID RUN_DATE
---------- ---------------------------
RUN_COMMENT
--------------------------------------------------------------------------------
         1 15-JUL-10 14:11:54
only test hr procedure



Usage:
sqlplus apps/<pwd>
SQL> START profiler.sql <runid>

Enter value for 1: 1

也可以用以下SQL进行查询

引用

column unit_name format a15 truncate;
column occured format 999999 ;
column line# format 99999 ;
column tot_time format 999.999999 ;
select p.unit_name, p.occured, p.tot_time, p.line# line,
       substr(s.text, 1,75) text
  from
       (select u.unit_name, d.TOTAL_OCCUR occured,
               (d.TOTAL_TIME/1000000000) tot_time, d.line#
          from plsql_profiler_units u, plsql_profiler_data d
         where d.RUNID=u.runid and d.UNIT_NUMBER = u.unit_number
           and d.TOTAL_OCCUR >0
           and  u.runid= &RUN_ID) p, 
       user_source s
where p.unit_name = s.name(+) and  p.line# = s.line (+)
order by p.unit_name, p.line#;

5、跟踪完成后，可在当前目录找到profiler_<runid>.htm的l一html文件，里面存放了PLSQL各模块的执行效率。
6、如果要查看PL/SQL的源代码，可以profgsrc.sql脚本查看，执行该脚本后会在当前目录生成存放源代码的sql文件

引用

SQL> @profgsrc.sql addemp
Usage:
sqlplus apps/<pwd>
SQL> START profgsrc.sql <PL/SQL Library name>


NAME                           TYPE
------------------------------ ------------
ADDEMP                         PROCEDURE
Elapsed: 00:00:00.01

Generating spool file for PROCEDURE ADDEMP