Using the DBMS_PROFILER Package
The Profiler API is implemented as a PL/SQL package, called DBMS_PROFILER. This package provides services for collecting statistics related to the execution of PL/SQL code and persistently storing it in three profiler tables (PLSQL_PROFILER_DATA, PLSQL_PROFILER_RUNS, and PLSQL_PROFILER_UNITS) in order to analyze the code and identify performance problems.
The DBMS_PROFILER package is not created by default by the database. It must be generated by the ProfLoad.sql script. This script is supplied with Oracle 8.1.6 and is located in the $ORACLE_HOME/Rdbms/Admin directory. (It's not provided in version 8.1.5 but can be downloaded from technet.oracle.com. OTN registration is required.) This script has to be executed by a SYS or INTERNAL user, and access has to be granted to PUBLIC. This script calls two other scripts, Dbmspbp.sql and Prvtpbp.plb, located in the same directory.
A typical profiling session consists of the following steps:
- Start the profiler data collection.
- Execute the PL/SQL code.
- Stop the profiler data collection.
The Oracle 8i profiler gathers information at the PL/SQL virtual machine level, which includes the total number of times each line has been executed, the total amount of time that has been spent executing that line, and the minimum and maximum times that have been spent on a particular execution of that line.
Creating the Test Data and Stored Procedure
Listing 1 shows the necessary code for creating the table and stored procedure, which I use in this article's examples. Let's create the TestData table and TestProc stored procedure to use in our profiling sessions.