oracle 的优化工具有哪些,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/

SQL> START profiler.sql

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_.htm的l一html文件，里面存放了PLSQL各模块的执行效率。

6、如果要查看PL/SQL的源代码，可以profgsrc.sql脚本查看，执行该脚本后会在当前目录生成存放源代码的sql文件

引用

SQL> @profgsrc.sql addemp Usage: sqlplus apps/ SQL> START profgsrc.sql NAME                           TYPE ------------------------------ ------------ ADDEMP                         PROCEDURE Elapsed: 00:00:00.01 Generating spool file for PROCEDURE ADDEMP