Tracing PLSQL

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

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License