For PL/SQL Profiler articles, refer:
Use DBMS_PROFILER to locate PL/SQL bottleneck code
Compare the analysis results of PL/SQL profiler
Use PL/SQL Developer to analyze PL/SQL code
-- The following steps directly refer to the Metalink Document (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. callto 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.
-- Because Profiler has been configured, we will analyze a and start ProfilerXXX_ADMIN @ SYBO2> EXEC DBMS_PROFILER.START_PROFILER ('Prepare TRADE tbl'); PL/SQL procedure successfully completed. b. Execute the process to be parsed 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: = '000000'; 8 ERR_NUM: = NULL; 9 ERR_MSG: = NULL; 10 11 XXX_ADMIN.XXX_GEN_DTL_PKG.PREPARE_TRADE_DTL_TB L (TRADE_DATE_IN, ERR_NUM, ERR_MSG); 12 COMMIT; 13 END; 14/PL/SQL procedure successfully completed. c. Stop profilerXXX_ADMIN @ ORAID> EXEC DBMS_PROFILER.STOP_PROFILER; PL/SQL procedure successfully completed. -- Author: Leshami -- Blog: Author> @ profiler RUNID RUN_OWNER RUN_DATE RUN_COMMENT ------------------------------------------------------------- --------------- 3 XXX_ADMIN 29-SEP-13 :53 prepare trade TBLParameter 1: RUNID (required) Enter value for 1: 3 ---> Enter the corresponding Profiler run IDValue passed: ~~~~~~~~~~~~ RUNID: "3" PROFILER file has been created: profiler_SID_xxxxxdb01uv_10.2.0.3.0_20130929_115525.html.
Use DBMS_PROFILER to locate PL/SQL bottleneck code
Compare the analysis results of PL/SQL profiler