Oracle Database : Profiling PL/SQL Code

AWR gathers and reports performance statistics that are useful in monitoring and tuning databases. Even though the combination of AWR/ASH reports and explain plans are very useful in analyzing the performance of queries, their usefulness is limited when dealing with PL/SQL-heavy applications. AWR and ASH reports help figure out if the database is spending significant amount of time and/or CPU cycles in executing PL/SQL code and in what PL/SQL modules -- however to identify performance bottlenecks withinin PL/SQL modules/routines and to pin-point hot spots in PL/SQL code at line level, DBMS_PROFILER, a database package, might be the right tool to use. Also check the documentation of DBMS_HPROF package out.

Rest of this post lists out the steps involved (on a high level) in generating the profile and subsequently generating a report or extracting relevant pieces of information.

Steps:

1. [Pre-11g env only] Create PLAN_TABLE as SYS user

   SQL> @?/rdbms/admin/utlxplan.sql
   SQL> GRANT ALL ON sys.plan_table TO public;
   SQL> CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
   

2. Create PL/SQL Profiler tables under the application schema

   As application user:

   SQL> @?/rdbms/admin/proftab.sql
   

3. Install DBMS_PROFILER package as SYS user, if missing

   SQL> @?/rdbms/admin/profload.sql
   

4. Start PL/SQL Profiler

   As application user:

   SQL> EXEC DBMS_PROFILER.START_PROFILER('SOME_COMMENT');
   

5. Execute one or more transactions to be profiled

6. Stop the PL/SQL Profiler

   As application user:

   SQL> EXEC DBMS_PROFILER.STOP_PROFILER;
   

7. At this point, there are couple of options to analyze the profile.

   • Easy option - if you have access to My Oracle Support (MOS), download the profiler.sql from MOS Document ID 243755.1 "Script to produce HTML report with top consumers out of PL/SQL Profiler DBMS_PROFILER data", and execute.

     As application user:

     SQL> profiler.sql
     

     Select appropriate runid from the list displayed on stdout. profiler.sql script generates an HTML report that shows top time consumers for the duration of the execution of the PL/SQL Profiler run.

   • Not-so-Easy option - refer to the DBMS_PROFILER documentation to extract relevant information and details such as top time consumers, top lines sorted by total time, profiled PL/SQL module list, top modules etc.,

     Checking the documentation out for the following three key tables is a good start -- PLSQL_PROFILER_RUNS (information related to a profiling session), PLSQL_PROFILER_UNITS (information about each PL/SQL unit that was executed during the profiler session) and PLSQL_PROFILER_DATA (execution statistics for each line of code in PL/SQL units). Ancillary tables: DBA_SOURCE and PLAN_TABLE.