Introduction
The manual defines the Hierarchical Profiler as follows "The DBMS_HPROF package provides an interface for profiling the execution of PL/SQL applications. It provides services for collecting the hierarchical profiler data, analyzing the raw profiler output and profiling information generation."
It is similar to the DBMS_PROFILER but more useful on cloud platforms where the DBMS_PROFILER is not always accessible.
Full details of the packages and their usage can be found here - https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_HPROF.html
However, despite numerous attempts, I have been unable to get some of the examples given in the documentation working as advertised. When using the examples in the documentation I have encountered memory - errors ORA-64218: Unable to dynamically allocate additional memory - which also generated trace files containing such errors as - ORA-6544 [pevm_peruws_callback-1] [64218]
This page shows a working method of using the DBMS_HPROF package avoiding the above issues.
It is also worth noting, that unlike the DBMS_PROFILER, DBMS_HPROF is installed by default (at least on 19c), so there is no need to install the packages. Furthermore, the tables relating to DBMS_HPROF are installed using the packages, making this tool viable on cloud platforms where full DBA access is not available.
Step-By-Step
Create the HPROF Tables
- Log onto the user that will run the PLSQL package
- Create the tables for the HPROF.
- SQL> EXEC DBMS_HPROF.create_tables(force_it => TRUE); # If this is the first time you are creating the tables, you can omit the force_it parameter, as this forces a drop and recreate.
Start the Profiling of the PLSQL
- Log onto the User from the previous step.
- The profiler needs to be started, the PLSQL run and the profiler stopped using the following code
- SQL>
BEGIN
DBMS_HPROF.start_profiling (
location => 'DATA_PUMP_DIR',
filename => 'HPROF_MyStoredProc.trc');
MyStoredProc(Param1 => 'Hello World');
DBMS_HPROF.stop_profiling;
END;
/
Analyse the Profiler Trace File
- Log onto the User from the previous step.
- SQL>
SET SERVEROUTPUT ON
DECLARE
trc_id NUMBER;
BEGIN
trc_id := DBMS_HPROF.analyze (
location => 'DATA_PUMP_DIR',
filename => 'HPROF_MyStoredProc.trc',
run_comment => 'MyStoredProc HPROF Run 1');
END;
/
Note: The output from the DBMS_HPROF.analyze command is written to the HPROF tables. The list of these tables can be shown with the following SQL - select * from user_tables where table_name like 'HPROF%'
Addendum
The raw trace file written to the DATA_PUMP_DIR can be analysed and read directly. Details on how to read the raw trace files and using the CLI plshprof will be addressed in a future post
Published 4th January 2023