<< Click to Display Table of Contents >>

DBMS_Profiler

Top Previous Next

The DBMS_Profiler package lets you analyze the efficiency of your PL/SQL code by gathering statistics in dedicated tables. Hora automates the process of setting up DBMS_Profiler, starting and stopping it, and viewing the statistics in a meaningful way.

Hora's interface to DBMS_Profiler is available from the toolbar on most pages, and from some context menus.

Setup

To enable DBMS_Profiler, a user with DBA privileges first needs to run the profload.sql script found in $ORACLE_HOME/rdbms/admin to install the package.

Next, each schema that will be using the package should run the proftab.sql script in the same directory to install the tables that will store the statistics. This will allow each user to keep track of his own results, and to delete old rows from the results tables when they are no longer needed.

These steps need to be performed only once. You will then be ready to use Hora's interface.

dlgDBMS_Profiler6

 

Starting the Profiler

When you invoke the Profiler, you see a window with a Start Profiler button, a Stop Profiler button, and some information about previous runs. Press the Start Profiler button and accept or change the default comment and the additional comment. The DBMS_Profiler package will then monitor all PL/SQL code that you execute. If you wish, you can close the Profiler window while you execute your code.

Stopping the Profiler

When you are finished executing the code that you wanted to monitor, just press the Stop Profiler button, and you will see a new entry with your comment in the tree that is described below.

Using Hora to interpret the results

Each time that you run DBMS_Profiler, a new run id is created through a sequence. The run id is the primary key of the PLSQL_PROFILER_RUNS table. Run id and unit number are the composite primary key for PLSQL_PROFILER_UNITS. Finally, run id, unit number and line number are the composite primary key for PLSQL_PROFILER_DATA.

A run is created each time that the START_PROFILER and STOP_PROFILER functions are run successfully in sequence. Within the run, each anonymous block, procedure, or function represents a new unit number. And within the unit, each new line number represents a line of executable source code.

The left-hand area of Hora's interface shows all runs in the current schema's PLSQL_PROFILER_RUNS table.

The upper-right area shows the comments associated with the START_PROFILER procedure. They are stored in PLSQL_PROFILER_ RUNS.

The lower-right window lists information from the PLSQL_PROFILER_ UNITS table for the stored procedures and functions of the highlighted run, including the total time in nanoseconds.

Finally, you can expand the runs in the left-hand area and see the following detail from  the PLSQL_PROFILER_DATA table for the highlighted program unit:

run id

unit number

line number

total time spent executing in nanoseconds

minimum execution time in nanoseconds

maximum execution time in nanoseconds

Cleaning up the tables

When you are no longer interested in the output for one or more run id's you can delete the corresponding rows from the three tables. Because of the two levels of foreign-key constraints, they should be deleted in the following  order:

1.

PLSQL_PROFILER_DATA

2.

PLSQL_PROFILER_UNITS

3.

PLSQL_PROFILER_RUNS

More information

Additional information about DBMS_Profiler is available from Oracle in the Oracle-Supplied Packages User Guide.