Oracle性能优化工具profiler

Oracle提供了profiler工具包，利用该工具包可以查看PL/SQL执行过程中各模块的性能。使用方法可以参考metalink doc 243755.1。本文主要简单介绍其使用方法。
1、进入业务用户，查看dbms_profiler是否已安装，如没有安装则需执行安装脚本profiler.sql
可以看到本数据库中已安装dbms_profiler
[quote]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[/quote]
2、创建数据字典，用于存放统计数据
[quote]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
[/quote]
3、跟踪PL/SQL模块性能，如本例所示addemp为需要跟踪的PL/SQL模块
[quote]
SQL> BEGIN
2 DBMS_PROFILER.START_PROFILER('only test hr procedure');
3 [color=red]addemp;[/color]
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[/quote]
也可以用以下SQL进行查询
[quote]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#; [/quote]
5、跟踪完成后，可在当前目录找到profiler_<runid>.htm的l一html文件，里面存放了PLSQL各模块的执行效率。
6、如果要查看PL/SQL的源代码，可以profgsrc.sql脚本查看，执行该脚本后会在当前目录生成存放源代码的sql文件
[quote]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[/quote]