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、開始ProfilerXXX_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、停止profilerXXX_ADMIN@ORAID> EXEC DBMS_PROFILER.STOP_PROFILER;PL/SQL procedure successfully completed.--Author : Leshami--Blog   : http://blog.csdn.net/leshamid、產生Profiler報告XXX_ADMIN@ORAID> @profiler RUNID RUN_OWNER                 RUN_DATE        RUN_COMMENT------ ------------------------- --------------- ------------------------------     3 XXX_ADMIN                29-SEP-13 11:53 PREPARE TRADE TBLParameter 1:RUNID (required)Enter value for 1: 3    --->輸入對應的Profiler的運行IDValue 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 剖析結果