PL/SQL Profiler 剖析报告生成html
有关PL/SQL Profiler的文章可以参考：
使用 DBMS_PROFILER 定位 PL/SQL 瓶颈代码
对比 PL/SQL profiler 剖析结果
使用PL/SQL Developer剖析PL/SQL代码
--下面的步骤直接参考了Metalink文档(ID 243755.1)
a、If needed, create the PL/SQL Profiler Tables under your application schema: @?/rdbms/admin/proftab.sql
b、If needed, install the DBMS_PROFILER API, connected as SYS: @?/rdbms/admin/profload.sql
c、Start PL/SQL Profiler in your application: EXEC DBMS_PROFILER.START_PROFILER('optional comment');
d、Execute your transaction to be profiled. Calls to PL/SQL Libraries are expected.
e、Stop PL/SQL Profiler: EXEC DBMS_PROFILER.STOP_PROFILER;
f、Connect as your application user, execute script profiler.sql provided in this document: @profiler.sql
g、Provide to profiler.sql the "runid" out of a displayed list.
h、Review HTML report generated by profiler.sql.
--由于我们已经配置好了Profiler，因此下面直接进行剖析
a、开始Profiler
XXX_ADMIN@SYBO2> EXEC DBMS_PROFILER.START_PROFILER('PREPARE TRADE TBL ');
PL/SQL procedure successfully completed.
b、执行需要剖析的过程
XXX_ADMIN@ORAID> DECLARE
2 TRADE_DATE_IN VARCHAR2(32767);
3 ERR_NUM NUMBER;
4 ERR_MSG VARCHAR2(32767);
5
6 BEGIN
7 TRADE_DATE_IN := '20130911';
8 ERR_NUM := NULL;
9 ERR_MSG := NULL;
10
11 XXX_ADMIN.XXX_GEN_DTL_PKG.PREPARE_TRADE_DTL_TBL ( TRADE_DATE_IN, ERR_NUM, ERR_MSG );
12 COMMIT;
13 END;
14 /
PL/SQL procedure successfully completed.
c、停止profiler
XXX_ADMIN@ORAID> EXEC DBMS_PROFILER.STOP_PROFILER;
PL/SQL procedure successfully completed.
--Author : Leshami
--Blog : http://blog.csdn.net/leshami
d、生成Profiler报告
XXX_ADMIN@ORAID> @profiler
RUNID RUN_OWNER RUN_DATE RUN_COMMENT
------ ------------------------- --------------- ------------------------------
3 XXX_ADMIN 29-SEP-13 11:53 PREPARE TRADE TBL
Parameter 1:
RUNID (required)
Enter value for 1: 3 --->输入对应的Profiler的运行ID
Value passed:
~~~~~~~~~~~~
RUNID: "3"
PROFILER file has been created:
profiler_SID_xxxxxdb01uv_10.2.0.3.0_20130929_115525.html.
使用 DBMS_PROFILER 定位 PL/SQL 瓶颈代码
对比 PL/SQL profiler 剖析结果