How to Profile PLSQL

Background & Overview

There may be times when a normal SQL trace of a PLSQL package is not sufficient. For example, when the SQL statements seem to be running fast, but the procedure takes a long time. It may be useful to understand which calls in the PLSQL package are taking the time. With this in mind, this document provides a step by step guide to tracing PLSQL using DBMS_HPROF.

The DBMS_HPROF package is a tool used to profile the execution of PL/SQL. It provides methods for collecting and analyzing the raw profiler output.

Step-By-Step Guide

1. Set-up the HRPOF Environment

• sqlplus '/ as sysdba'
• SQL> GRANT EXECUTE ON DBMS_HPROF TO &userid;
• SQL> CREATE OR REPLACE DIRECTORY profiler_dir AS '&loc/profiler';
• SQL> GRANT READ, WRITE ON DIRECTORY profiler_dir TO &userid;
• SQL> quit
• Create the profiler directory on the filesystem in the location specified above.

Notes

• The parameter &userid should be set to the userid that owns the PLSQL package being profiled.
• The parameter &loc should be set to a suitable filesystem location for the Oracle directory. e.g. /u01/app/oracle/admin/profiler

2. Create the DBMS_HRPOF objects.

• Log on to the userid that owns the PLSQL package being profiled.
• sqlplus <user>/<password>
• SQL> @$ORACLE_HOME/rdbms/admin/dbmshptab.sql

3. Profile the PLSQL Package

• Log on to the userid that owns the PLSQL package being profiled
• sqlplus <user>/<password>
• SQL> BEGIN
• SQL> DBMS_HPROF.start_profiling (
• SQL> location => 'PROFILER_DIR',
• SQL> filename => '&prof_file');
• SQL>
• SQL> &enter_plsql;
• SQL>
• SQL> DBMS_HPROF.stop_profiling;
• SQL> END;
• SQL> /

Notes

• The parameter &prof_file should be set to the filename of the raw outptut file. This file will be created in the profiler_dir.
• The parameter &enter_plsql should be set to the PLSQL package being profiled. This will execute the package inside the profiler block.

4. Analyzing the DBMS_HPROF Trace File

• Log on to the userid that owns the PLSQL package being profiled
• sqlplus <user>/<password>
• SQL> SET SERVEROUTPUT ON
• SQL> DECLARE
• SQL> l_runid NUMBER;
• SQL> BEGIN
• SQL> l_runid := DBMS_HPROF.analyze (
• SQL> location => 'PROFILER_DIR',
• SQL> filename => '&prof_file',
• SQL> run_comment => 'Test run.');
• SQL>
• SQL> DBMS_OUTPUT.put_line('l_runid=' || l_runid);
• SQL> END;
• SQL> /

Notes

• The parameter &prof_file should be set to the filename of the raw outptut file created in the previous step.

5. Viewing the output of the DBMS_HPROF analyze.

Query 1

--
-- Display the RUNID of the analyze run.
--

SET LINESIZE 200
SET TRIMOUT ON

COLUMN runid FORMAT 99999
COLUMN run_timestamp FORMAT A30
COLUMN run_comment FORMAT A50

SELECT runid,
run_timestamp,
total_elapsed_time,
run_comment
FROM dbmshp_runs
ORDER BY runid
/

Query 2

--
-- Using the appropriate RUNID value, query the PLSQL steps.
--

COLUMN owner FORMAT A20
COLUMN module FORMAT A20
COLUMN type FORMAT A20
COLUMN function FORMAT A25

SELECT symbolid,
owner,
module,
type,
function
FROM dbmshp_function_info
WHERE runid = 1
ORDER BY symbolid
/

Query 3

--
-- Analyze the Timings of the PLSQL steps.
--

SET LINESIZE 130
COLUMN name FORMAT A30
COLUMN function FORMAT A25

SELECT RPAD(' ', level*2, ' ') || fi.owner || '.' || fi.module AS name,
fi.function,
pci.subtree_elapsed_time,
pci.function_elapsed_time,
pci.calls
FROM dbmshp_parent_child_info pci
JOIN dbmshp_function_info fi ON pci.runid = fi.runid AND pci.childsymid = fi.symbolid
WHERE pci.runid = 1
CONNECT BY PRIOR childsymid = parentsymid
START WITH pci.parentsymid = 2
/

6. An Alternative Way to Analyze the Raw Trace File

• cd <profiler location directory>
• plshprof -output /tmp/<profiler output file> <path and name of the raw profiler file>
• e.g. plshprof -output /tmp/hrprof_file_run1_10dec14 /u01/app/oracle/admin/profiler/hrprof_file_run1_10dec14.trc

Notes

• This will create an HTML file called hrprof_file_run1_10dec14.html

7. Create a Difference report

• cd <profiler location directory>
• plshprof -output /tmp/<profiler output file> <path and name of the raw profiler file 1> <path and name of the raw profiler file 2>
• e.g. plshprof -output /tmp/hrprof_diff_10dec14 /u01/app/oracle/admin/profiler/hrprof_file_run1_10dec14.trc /u01/app/oracle/admin/profiler/hrprof_file_run2_10dec14.trc

Notes

• This will create an HTML file called hrprof_diff_10dec14.html