DBMS_PROFILER

Implementing and Using the PL/SQL Profiler [ID 243755.1]

---

 修改时间 10-AUG-2011     类型 TROUBLESHOOTING     状态 PUBLISHED 

In this Document
  Purpose
  Last Review Date
  Instructions for the Reader
  Troubleshooting Details
  References

---

Applies to:

PL/SQL - Version: 9.2.0.1 to 11.2.0.2 - Release: 9.2 to 11.2
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.2   [Release: 9.2 to 11.2]
Oracle Application Object Library - Version: 11.5.10.0 to 12.1.3   [Release: 11.5.10 to 12.1]
Information in this document applies to any platform.

Purpose

Implementing and Using the PL/SQL Profiler

When there is a significant gap between user elapsed time and SQL processing elapsed time, and there 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 to process.

For example, if a transaction which uses PL/SQL Libraries (packages, procedures, functions or triggers) executes in one hour of user elapsed time, and reviewing the results of the Trace Analyzer or TKPROF only 10 minutes of the elapsed time can be explained 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, including the total execution time for each line of code, and how many times each of these lines was executed.

The actual PL/SQL Profiler is provided with the core RDBMS code, and it is well documented on the Supplied PL/SQL Packages and Types Reference manual, under the package name DBMS_PROFILER. This Note is about implementing and using the PL/SQL Profiler on any 9i or higher database, in order to debug the performance of any PL/SQL application Library. The main script provided in this Note (profiler.sql) generates a comprehensive HTML report on the performance data extracted by the DBMS_PROFILER package.

Last Review Date

August 10, 2011

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

Troubleshooting Details

Execution Environment:

SQL*Plus

Access Privileges:

Requires SQL*Plus user and password for application code. If used within an Oracle Applications
database, connect as APPS. Otherwise, use main application user and password.

Usage:

#sqlplus APPS/<pwd>
SQL> START profiler.sql <run_id>

Where run_id is the execution id returned by the DBMS_PROFILER (which must be installed first). If run_id is unknown, execute without any parameter and the script will display a list to choose from.

Instructions:

To install DBMS_PROFILER and generate the PL/SQL Profiler data, read the Prerequisites section below.

Once the DBMS_PROFILER START_PROFILER and STOP_PROFILER procedures have been executed for a profiled PL/SQL application Library, execute this profiler.sql script to generate an HTML comprehensive report, which identifies the top "n" lines of PL/SQL code in terms of execution elapsed time. It also shows for each PL/SQL line of executed code, the number of times the line was executed. See example of output included into file PROF.zip.

Use the HTML spool file generated by profiler.sql to debug the most expensive PL/SQL lines in terms of execution time.

For the latest version of this tool, download compressed file PROF.zip

Prerequisites:

• If used for the first time, determine if DBMS_PROFILER is installed by doing a describe on that package

#sqlplus APPS/<pwd>
SQL> DESC DBMS_PROFILER;

• If DBMS_PROFILER is not installed, connect as SYS into SQL*Plus on database server, and execute command below to create the missing package:

#sqlplus SYS/<pwd>
SQL> START ?/rdbms/admin/profload.sql;

•  If used for the first time, and once DBMS_PROFILER is installed, connect as application user into SQL*Plus, and create the repository tables PLSQL_PROFILER_RUNS, PLSQL_PROFILER_UNITS and PLSQL_PROFILER_DATA (proftab.sql is provided within PROF.zip, and is also available under $ORACLE_HOME/rdbms/admin)

#sqlplus APPS/<pwd>
SQL> START proftab.sql;

• Since main script on this Note (profiler.sql) reports on data generated by package DBMS_PROFILER, be sure to profile your PL/SQL Library prior to try reporting the results. To profile a PL/SQL Library (package, procedure, function or trigger), include in its body the two calls to actually start and complete the profiling. Use the example below on any PL/SQL Library to profile.

BEGIN
DBMS_PROFILER.START_PROFILER('any comment to identify this execution');
...
DBMS_PROFILER.STOP_PROFILER;
EXCEPTION -- this line may exist in your code
...
END;
/

• In order to modify your PL/SQL Library, find first the script that creates it, make a backup, and insert manually the START and STOP calls for the profiler. If unable to find the script that creates your package, procedure, function or trigger, use the provided script profgsrc.sql executing with PL/SQL Library name as inline parameter:

#sqlplus APPS/<pwd>
SQL> START profgsrc.sql <PL/SQL Library name>;

• Script profgsrc.sql extracts from USER_SOURCE the actual source code for the requested PL/SQL Library. It generates 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 modified PL/SQL Library by executing it from SQL*Plus and connecting as your application user.

• Once your compiled PL/SQL Library contains the START and STOP profiler procedure calls, execute your Library from your application. Every execution generates a new run_id which can then be reported on, by using the profiler.sql script.

-----------------------------------------

总体来说就是在源PL/SQL块中，加入dbms_profiler.start和stop，然后找到对应的run_id，在运行时会显示
使用提供的profiler.sql run_id然后得到格式化的profiler报告（HTML格式）
 
Description

Prerequisites:=============1. If used for the first time, determine if DBMS_PROFILER is installed by doing a describe onthat package:#sqlplus APPS/<pwd>SQL> DESC DBMS_PROFILER;If DBMS_PROFILER is not installed, connect as SYS into SQL*Plus on database server, andexecute command below to create the missing package:#sqlplus SYS/<pwd>SQL> START ?/rdbms/admin/profload.sql;--确定是否已经存在DBMS_PROFILER，如果不存在，那么调用上面的sql脚本进行创建2. If used for the first time, and once DBMS_PROFILER is installed, connect as application userinto SQL*Plus, and create the repository tables PLSQL_PROFILER_RUNS, PLSQL_PROFILER_UNITSand PLSQL_PROFILER_DATA (proftab.sql is provided withinPROF.zip, and is also available under$ORACLE_HOME/rdbms/admin)#sqlplus APPS/<pwd>SQL> START proftab.sql;
--创建保存profiler的资料的三个表，在需要收集的用户下（不是SYS下）
3. Since main script. on this Note (profiler.sql) reports on data generated by packageDBMS_PROFILER, be sure to profile your PL/SQL Library prior to try reporting the results.To profile a PL/SQL Library (package, procedure, function or trigger), include in its bodythe two calls to actually start and complete the profiling.  Use the example below on anyPL/SQL Library to profile.BEGINDBMS_PROFILER.START_PROFILER('any comment to identify this execution');...DBMS_PROFILER.STOP_PROFILER;EXCEPTION -- this line may exist in your code...END;/4. In order to modify your PL/SQL Library, find first the script. that creates it, make abackup, and insert manually the START and STOP calls for the profiler.If unable to find the script. that creates your package, procedure, function or trigger,use the provided script. profgsrc.sql executing with PL/SQL Library name as inlineparameter:#sqlplus APPS/<pwd>SQL> START profgsrc.sql <PL/SQL Library name>;Script. profgsrc.sql extracts from USER_SOURCE the actual source code for the requestedPL/SQL Library.  It generates a text spool file as a SQL script. to regenerate the PL/SQLLibrary.  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 application user.--3和4的目的都是编辑源存储过程或其他PL/SQL(trigger)等，加入DBMS_PROFILER.START_PROFILER和stop5. Once your compiled PL/SQL Library contains the START and STOP profiler procedure calls,execute your Library from your application.  Every execution generates a new run_id whichcan then be reported on, by using the profiler.sql script.--得到RUN_ID，运行存储过程，得到输出

===============================================

References

NOTE:215187.1 - SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly
NOTE:224270.1 - Trace Analyzer TRCANLZR - Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046

附件

---

PROFILER Scripts (13 KB)

相关内容

---

产品

---

• Oracle E-Business Suite > Applications Technology > Application Object Library > Oracle Application Object Library
• Oracle Database Products > Oracle Database > Application Development > PL/SQL
• Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition

关键字

---

COE; DATA_ANALYSIS; DBMS_PROFILER; DBMS_PROFILER.START_PROFILER; DBMS_PROFILER.STOP_PROFILER; PL SQL; PROFILE