Oracle PL/SQL 性能分析工具 profiler 说明Oracle PL/SQL 性能分析工具 profiler 说明
一.说明Oracle提供了profiler工具包，利用该工具包可以查看PL/SQL执行过程中各模块的性能。可以从MOS上下载该脚本：
Implementing andUsing the PL/SQL Profiler [ID 243755.1]
也可以从我的CSDN上下载：
download.csdn/detail/tianlesoftware/4051100
When there is asignificant gap between user elapsed time and SQL processing elapsed time, andthere is PL/SQL code involved, the PL/SQL Profiler becomes a very useful tool.It helps to identify the lines of PL/SQL code which are taking longer toprocess.
--当user elapsed time 和 SQL processing elapsedtime 有很大差别，且涉及到PL/SQL 代码时，就可以使用PL/SQL Profiler 工具，其可以指明行级PL/SQL 的时间。
For example, ifa transaction which uses PL/SQL Libraries (packages, procedures, functions ortriggers) executes in one hour of user elapsed time, and reviewing the resultsof the Trace Analyzer or TKPROF o
nly 10 minutes of the elapsed time can beexplained with SQL commands being executed, then, by using the PL/SQL Profiler,a line-by-line of the executed PL/SQL application code is reported, includingthe total execution time for each line of code, and how many times each ofthese lines was executed.
--比如执行了一个PL/SQL 库（包，过程，函数或者触发器），userelapsed time 用了1个小时，但是通过trace 显示仅用了10分钟，这时就可以用PL/SQL Profiler，其可以报告每行代码执行的时间。
The actualPL/SQL Profiler is provided with the core RDBMS code, and it is well documentedon the Supplied PL/SQL Packages and Types Reference manual, under the packagename DBMS_PROFILER. This Note is about implementing and using the PL/SQLProfiler on any 9i or higher database, in order to debug the performance of anyPL/SQL application Library. The main script provided in this Note (profiler.sql)generates a comprehensive HTML report on the performance data extracted by theDBMS_PROFILER package.
--实际上，PL/SQL Profiler 仅提供了核心的RDBMS 代码，其在DBMS_PROFILER包里也说明。
PL/SQL Profiler 包含如下3个脚本：
profiler.sql - Reporting PL/SQL Profilerdata generated by DBMS_PROFILER (main script)
profgsrc.sql - Get source code for PL/SQLLibrary (package, procedure, function or trigger)
proftab.sql - Create tables for the PL/SQLprofiler
二.使用前的准备工作
2.1 If used for the first time, determineif DBMS_PROFILER is installed by doing a describe on that package
--如果是第一次使用，需要检查DBMS_PROFILER 包是否安装。
#sqlplus APPS/<pwd> --注意用户SQL> DESC DBMS_PROFILER;
2.2 If DBMS_PROFILER is not installed,connect as SYS into SQL*Plus on database server, and execute command below tocreate the missing package:
--如果DBMS_PROFILER没有安装，使用SYS 用来执行如下命令来
安装该包。
#sqlplus SYS/<pwd>--注意用户SQL> START ?/rdbms/admin/profload.sql;
2.3 If used for the first time, andonce DBMS_PROFILER is installed, connect as application user into SQL*Plus, andcreate the repository tables PLSQL_PROFILER_RUNS, PLSQL_PROFILER_UNITS andPLSQL_PROFILER_DATA (proftab.sql is provided within PROF.zip, and is alsoavailable under $ORACLE_HOME/rdbms/admin)
--如果是第一次使用，且DBMS_PROFILER 已经安装，那么执行proftab.sql 脚本，创建存放统计数据的表：PLSQL_PROFILER_RUNS,PLSQL_PROFILER_UNITS and PLSQL_PROFILER_DATA。 该脚本在PROF 包里有，$ORACLE_HOME/rdbms/admin目录下也有。
#sqlplus APPS/<pwd>SQL> START proftab.sql;
或：
SQL> start ?/rdbms/admin/proftab.sql
drop table plsql_profiler_data cascadeconstraints
*
ERROR at line 1:
ORA-00942: table or view does not exist
drop table plsql_profiler_units cascadeconstraints
*
ERROR at line 1:
ORA-00942: table or view does not exist
drop table plsql_profiler_runs cascadeconstraints
*
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>
2.4 Since main script on this Note(profiler.sql) reports on data generated by package DBMS_PROFILE
R, be sure toprofile your PL/SQL Library prior to try reporting the results. To profile aPL/SQL Library (package, procedure, function or trigger), include in its bodythe two calls to actually start and complete the profiling.
--因为主脚本（profiler.sql）显示的数据是用DBMS_PROFILER包生成的，所以显示结果之前要先profile PL/SQL 代码。
Use the example below on any PL/SQL Libraryto profile.
BEGINDBMS_PROFILER.START_PROFILER('any comment to identify this execution');...DBMS_PROFILER.STOP_PROFILER;EXCEPTION -- this line may exist in END;/
2.5 In order to modify your PL/SQL Library,find first the script that creates it, make a backup, and insert manually theSTART and STOP calls for the profiler. If unable to find the script thatcreates your package, procedure, function or trigger, use the provided scriptprofgsrc.sql executing with PL/SQL Library name as inline parameter:
--为了修改PL/SQL库，首先需要到脚本，备份后，然后手工的添加START 和 STOP，如果不能到原代码，可以使用profgsrc.sql 脚本来获取原代码：
#sqlplus APPS/<pwd>SQL> START profgsrc.sql <PL/SQL Library name>;
SQL> start ?/rdbms/admin/profgsrc.sql logon_audit;
Usage:
sqlplus apps/<pwd>
SQL> START profgsrc.sql <PL/SQLLibrary name>
NAME TYPE
------------------------------ ------------
LOGON_AUDIT TRIGGER
Generating spool file for TRIGGERLOGON_AUDIT
我这里将脚本copy到了$ORACLE_HOME/rdbms/admin下，执行
之后其会在sqlplus 的当前目录下，生成一个同名的sql 脚本，脚本就是我们对象的的SQL 代码。
2.6 Script profgsrc.sql extracts fromUSER_SOURCE the actual source code for the requested PL/SQL Library. Itgenerates a text spool file as a SQL script to regenerate the PL/SQL Library.Make a backup of the spool file before modifying it. Compile your modifiedPL/SQL Library by executing it from SQL*Plus and connecting as your applicationuser.
--profgsrc.sql 从USER_SOURCE中抽取对象的代码。
2.7 Once your compiled PL/SQL Librarycontains the START and STOP profiler procedure calls, execute your Library fromyour application. Every execution generates a new run_id which can then bereported on, by using the profiler.sql script.
--当我们在PL/SQL对象中添加完START 和 STOP profiler 调用后，然后启动应用，每次执行都会生成一个run_id, 然后使用profiler.sql 工具，结合run_id 就可以获取对应的报告。
生成报告命令
SQL> START profiler.sql <run_id>
Where run_id isthe execution id returned by the DBMS_PROFILER (which must be installed first).
If run_id isunknown, execute without any parameter and the script will display a list tochoose from.
--如果没有提供任何参数，那么会自动列出所有的run_id.
三.使用示例测试的存储过程如下：
CREATE OR REPLACE PROCEDURE proc_test
AS
BEGIN
insert intotest_pro select * from all_objects;
commit;
FOR x IN (select * fromall_users where user_id>90)
LOOP
DBMS_OUTPUT.put_line(x.username);
END LOOP;
END proc_test;
3.1 示例1
SQL> set serveroutput on
SQL> begin
2DBMS_PROFILER.START_PROFILER('Dave Test PL/SQL Profiler');
3 proc_test;
4 DBMS_PROFILER.STOP_PROFILER;
5 end;
6 /
XS$NULL
DAVE3
DAVE2
DAVE1
XEZF
MGMT_VIEW
SYSMAN
ANQING
DVD
PL/SQL procedure successfully completed.
--调用PL/SQLprofiler.sql
SQL> @?/rdbms/admin/profiler.sql
RUNID RUN_DATERUN_COMMENT
-
--------- ------------------------------------------------------
1 08-FEB-12 14:55:38 Dave TestPL/SQL Profiler
2 08-FEB-12 15:03:11 Dave TestPL/SQL Profiler
Usage:
sqlplus apps/<pwd>
SQL> START profiler.sql <runid>
Enter value for 1: 2
SQL>
--其会自动列出所有RUNID，并启动，我们这里选择上面对应的RUNID，输入2，回车即可。
执行完毕后会在SQLPLUS的当前目录下生成一个profiler_2.html的报告，这里的2是RUNID.
3.2 示例2
CREATE OR REPLACE PROCEDURE proc_test2
AS
BEGIN
DBMS_PROFILER.START_PROFILER('Dave Test PL/SQL Profiler');
insert intotest_pro select * from all_objects;
commit;
FOR x IN (select * fromall_users where user_id>90)
LOOP
DBMS_OUTPUT.put_line(x.username);
END LOOP;
DBMS_PROFILER.STOP_PROFILER;
END proc_test2;
SQL> exec proc_test2
PL/SQL procedure successfully completed.
SQL> @?/rdbms/admin/profiler.sql
RUNID RUN_DATERUN_COMMENT
---------- -------------
----------------------------------------
1 08-FEB-12 14:55:38 Dave TestPL/SQL Profiler
2 08-FEB-12 15:03:11 Dave TestPL/SQL Profiler
3 08-FEB-12 15:07:26 Dave TestPL/SQL Profiler
4 08-FEB-12 15:09:40 Dave TestPL/SQL Profiler
-
-这里这里的RUN_COMMENT内容，其在DBMS_PROFILER.START_PROFILER 中指定，主要用来辨别我们的RUNID.
Usage:
sqlplus apps/<pwd>
SQL> START profiler.sql <runid>
Enter value for 1: 4
SQL>
3.3 注意事项：1.proftab.sql 脚本需要在PL/SQL对象所在的用户下执行，否则就会报如下错误：
SQL> exec proc_test2
BEGIN proc_test2; END;
drop table if exists user
*
ERROR at line 1:
ORA-06528: Error executing PL/SQL profiler
ORA-06512: at"SYS.DBMS_PROFILER", line 123
ORA-06512: at"SYS.DBMS_PROFILER", line 132
ORA-06512: at "DAVE.PROC_TEST2",line 4
ORA-06512: at line 1
2.起初在win7+ oracle 11gR2 下测试，执行没有报错，但最终生成的报告没有数据。后转移到linux + oracle 10gR2 下测试，正常。
后经过测试，发现问题，之前的环境是64位的win7+32位的Oracle 11gR2，后把Oracle 换成64位 11gR2 后正常。这个原因可能与版本有关系，因为也不能排除重新安装解决的某些未知因素。
测试结果如下：
3. 两个示例的区别在于示例1没有修改PL/SQL 对象，而是在PL/SQL 代码块中start 和stop了DBMS_PROFILER。 而示例2中是修改了PL/SQL 对象，将DBMS_PROFILER 添加到PL/SQL 对象中。
的资料是先备份PL/SQL对象，然后修改，将DBMS_PROFILER 添加到对象里，这样的好处是每次调用时都会统计PROFILER的信息。当然麻烦的是需要修改对象，而在示例1中，在PL/SQL 代码块中启动和关闭，就免于修改PL/SQL 对象。
所以具体采用哪种方式要结合自己的需求，能达到目的即可。