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 <> is about implementing and using the PL/SQL Profiler on any 8i or higher instance, in order to debug the performance of any PL/SQL
application Library.  This Note can be used on any Oracle Applications instance using 8i or higher, but its use is not restricted to solely Oracle Apps.

The main script provided in this Note (profiler.sql) generates a comprehensive HTML report on the performance data extracted by the DBMS_PROFILER
package.

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


PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected. The script will produce an output file named [outputfile].
This file can be viewed in a browser or uploaded for support analysis.

Prerequisites:
=============
     1. 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;

     2. 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;

     3. 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;
           /

     4. 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.

     5. 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.

Troubleshooting Oracle Apps Performance Issues <>
SQLTXPLAIN.SQL - Enhanced Explain Plan and diagnostic info for one SQL statement <>
TRCANLZR.sql - Trace Analyzer (Interpreting Raw SQL Traces generated by EVENT 10046) <>
Oracle9i Supplied PL/SQL Packages and Types Reference, DBMS_PROFILER Package

The following files are included in the PROF.zip compressed file:

   profiler.sql - Reporting PL/SQL Profiler data generated by DBMS_PROFILER (main script)
   profgsrc.sql - Get source code for PL/SQL Library (package, procedure, function or trigger)
   proftab.sql - Create tables for the PL/SQL profiler
   profiler_7.html - Example of output of profiler.sql (HTML spool file)
   NOTE_243755.htm - This document