Using the Oracle PL/SQL Profiler Part 1
Oracle has provided the ability to see how much time each step of a PL/SQL routine takes since Oracle8i. The environment is easy to setup and the information is easy to retrieve.
Introduction
Why Profile? When tuning SQL, it is easy because there is just the single SQL statement. With PL/SQL, there are SQL statements, SQL imbedded in implicit and explicit cursors, called routines (functions/procedures), and the PL/SQL code itself. When a PL/SQL routine is taking 5 minutes to run, exactly what code is taking how much time. This is the information that the PL/SQL profiler provides. Without this information, the person trying to tune the PL/SQL is only shooting in the dark, perhaps pulling out and tuning the SQL within the code, but otherwise has no idea what a PL/SQL routine is doing when it comes to time spent on each line of code.
The profiler is easy to setup and easy to use. Tools like Quest Software’s TOAD provides a nice GUI interface to this profiler.
Installation
The profiler has two scripts that setup the environment. Both are found in the /rdbms/admin folder.
The PROFLOAD.sql script needs to be run as SYS (connect AS SYSDBA). This script will create the DBMS_PROFILER package and create synonyms and permissions for usage.
The PROFTAB.sql script is recommended to be executed for each user desiring to run the DBMS_PROFILER package. This script sets up the three main tables: PLSQL_PROFILER_RUNS, PLSQL_PROFILER_UNITS, and the PLSQL_PROFILER_DATA. This script can be setup so that all the users share these tables but this topic is beyond the scope of this blog series.