Profilers are helpful tools to investigate programs and identify slow program parts and bottle necks. Furthermore you can determine which procedure, function or any other code part is executed how many times. To be able to use the DBMS_PROFILER package you have to install once for your database the following packages. Do this as user SYS:

Suppose Oracle is installed in ORACLE_HOME = D:OracleProduct8.1.7

sqlplus sys/

@D:OracleProduct8.1.7Rdbmsadminprofload.sql
@D:OracleProduct8.1.7Rdbmsadminproftab.sql
@D:OracleProduct8.1.7Rdbmsadmindbmspbp.sql
@D:OracleProduct8.1.7Rdbmsadminprvtpbp.plb

Additionally install again the proftab file as the owner your packages belong to:

sqlplus scott/tiger

@D:OracleProduct8.1.7Rdbmsadminproftab.sql

A typical profile cycle looks like this:

• Start profiler

• Execute your PL/SQL package

• Stop profiler

• Evaluate results stored in PLSQL_PROFILER_DATA
  and PLSQL_PROFILER_UNITS tables

1. Start Profiler

Start your profiler before every profile cycle:

sqlplus scott/tiger
exec DBMS_PROFILER.START_PROFILER;

2. Execute your PL/SQL Code

For example, create and start the following procedure, to create a random number using the DBMS Package DBMS_RANDOM

CREATE OR REPLACE FUNCTION myrand (n IN NUMBER) RETURN NUMBER IS
my_random_number NUMBER;
BEGIN
DBMS_RANDOM.INITIALIZE (n);
my_random_number := DBMS_RANDOM.RANDOM;
DBMS_RANDOM.TERMINATE;
RETURN (my_random_number);
END;
/

SELECT myrand (5000) FROM dual;

3. Stop the profiler and get the run id:

exec DBMS_PROFILER.STOP_PROFILER;
SELECT plsql_profiler_runnumber.currval FROM dual;

CURRVAL
----------
1

The run id (in our example = 1) is used to identify the profile cycle. All results for all cycles remain stored. You can use the run ids to compare different cycles with different program codes against each other.

4. Evaluate PLSQL_PROFILER_DATA and PLSQL_PROFILER_UNITS

Here is a code sample how to select profiler information:

col line format 9999 heading "Line"
col total_occur format 999,999 heading "Tot Occur"
col total_time format 999,990.999 heading "Tot Time[ms]"
col text format a50 heading "Code"

SELECT s.line,
p.total_occur,
p.total_time,
s.text
FROM all_source s, (
SELECT u.unit_owner,
u.unit_name,
u.unit_type,
d.line#,
d.total_occur,
d.total_time/1000000 total_time
FROM plsql_profiler_data d, plsql_profiler_units u
WHERE u.unit_number = d.unit_number
AND u.runid = d.runid
AND u.runid = 1 -- Set run id here
) p
WHERE s.name = 'MYRAND' -- Set Procedure Name here
AND s.owner = 'SCOTT' -- Set Procedure Owner here
AND s.owner = p.unit_owner (+)
AND s.name = p.unit_name (+)
AND s.type = p.unit_type (+)
AND s.line = p.line# (+)
ORDER BY s.line;

You have to set your run id, a package name and the package owner. To specify the source lines is not mandatory (not set in the example) but very convenient. Some development tools support the evaluation of these two profiler tables. However if you select them in SQL*Plus you will get a lot of lines. In a first step you can display all the lines and later on, if you focus down to certain code parts, you may like to restrict your selection by line numbers.

A typical output looks like this.

Tot Tot
Line Occur Time[ms] Code
----- ----- -------- ----------------------------------------------
1 FUNCTION myrand (n IN NUMBER) RETURN NUMBER IS
2 my_random_number NUMBER;
3 BEGIN
4 1 0.035 DBMS_RANDOM.INITIALIZE (n);
5 2 0.009 my_random_number := DBMS_RANDOM.RANDOM;
6 1 0.005 DBMS_RANDOM.TERMINATE;
7 1 0.002 RETURN (my_random_number);
8 0 0.000 END;

The file number is followed by the number of executions and the total time in milliseconds.