DBMS_HPROF Hierarchical Profiling in ORACLE 11G

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.

Subprogram	Description
ANALYZE Function	Analyzes the raw profiler output and produces hierarchical profiler information in database tables
START_PROFILING Procedure	Starts hierarchical profiler data collection in the user's session
STOP_PROFILING Procedure	Stops profiler data collection in the user's session’s

 The PL/SQL hierarchical profiler is implemented by the DBMS_HPROF package and has two components:

• Data collection

The data collection component is an intrinsic part of the PL/SQL Virtual Machine. The DBMS_HPROF package provides APIs to turn hierarchical profiling on and off. The raw profiler output is written to a file.

• Analyzer

The analyzer component processes the raw profiler output and stores the results in hierarchical profiler tables.

Note:

To generate simple HTML reports directly from raw profiler output, without using the Analyzer, you can use the plshprof command-line utility.

Setting up DBMS_HPROF:

Execute the grant from SYS to user account mahendra

grant execute on dbms_href to mahendra

Now, create profiler tables in user schema by executing the following sql

@?/rdbms/admin/dbmshptab.sql

This will create 3 tables and one sequence in user schema

Now, create a directory to write raw profiler information using sys.

create directory mah_hpprof_dir as 'D:\'

grant read, write on directory mah_hpprof_dir to mahendra

The following sql gives directories information

select * from

dba_directories

Example to use DBMS_HPROF:

To collect the profile data,

1.    Start the profiler

2.    Execute the plsql code

3.    Stop the profiler

4.    Analyze the raw profile data

Create 3 procedures proc3 which is called by proc2 which further called by proc1.

create procedure proc3 as

    v_var int;

begin

    for i in 1..1000 loop

       select i into v_var from dual;

    end loop;

end;

create procedure proc2 as

begin

    for i in 1..100 loop

       proc3; -- execute proc3

    end loop;

end;

create procedure proc1 as

begin

    for i in 1..10 loop

       proc2; -- execute proc2

    end loop;

end;

Now, let us start with collecting profiler information for PROC1 procedure

BEGIN

  DBMS_HPROF.start_profiling (    location => 'MAH_HPPROF_DIR', filename => 'profiler_test1.txt'); -- starting the profiler

  proc1; --calling proc1

  DBMS_HPROF.stop_profiling; --stoping the profiler

END;

Note:  Make sure you mention the directory name above in capital letters and you have read, write privileges on the directory failing which the following error may occur

ERROR at line 1:

ORA-29280: invalid directory path

ORA-06512: at "SYS.DBMS_HPROF", line 34

ORA-06512: at line 2

After we have the profiler raw, we have to use analyze function of dbms_hprof to populate profiler tables i.e. dbmshp_runs, dbmshp_parent_child_info, dbmshp_function_info

DECLARE

  v_runid  NUMBER;

BEGIN

  v_runid := DBMS_HPROF.analyze (

               location    => 'MAH_HPPROF_DIR',

               filename    => 'profiler_test1.txt',

               run_comment => 'Test run1');

  DBMS_OUTPUT.put_line('Run Id Information=' || v_runid);

END;

Run Id Information=2

PL/SQL procedure successfully completed.

Now, check the profiler tables:

select * from

dbmshp_runs

select * from

dbmshp_parent_child_info

select * from

dbmshp_function_info

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 = 2
CONNECT BY PRIOR childsymid = parentsymid
START WITH pci.parentsymid = 1;

 

Understanding Raw Profiler Output

P#V PLSHPROF Internal Version 1.0

P#! PL/SQL Timer Started

P#C PLSQL."MAHENDRA"."PROC1"::7."PROC1"#980980e97e42f8ec #1

P#X 125

P#C PLSQL."MAHENDRA"."PROC2"::7."PROC2"#980980e97e42f8ec #1

P#X 58

P#C PLSQL."MAHENDRA"."PROC3"::7."PROC3"#980980e97e42f8ec #1

                P#X 43

P#C SQL."MAHENDRA"."PROC3"::7."__static_sql_exec_line5" #5

P#X 34945

P#R

P#X 30

P#C SQL."MAHENDRA"."PROC3"::7."__static_sql_exec_line5" #5

P#X 70

P#R

P#X 7

P#C SQL."MAHENDRA"."PROC3"::7."__static_sql_exec_line5" #5

P#X 57

P#R

P#X 6

P#C SQL."MAHENDRA"."PROC3"::7."__static_sql_exec_line5" #5

P#X 55

Indicator	Meaning
P#V	PLSHPROF banner with version number
P#C	Call to a subprogram (call event)
P#R	Return from a subprogram (return event)
P#X	Elapsed time between preceding and following events
P#!	Comment

  

Plshprof utility:

 

The plshprof command-line utility (located in the directory $ORACLE_HOME/bin/) generates simple HTML reports from either one or two raw profiler output files.

 $ plshprof -output D:/output_html_format D:/ profiler_test1.txt

PLSHPROF: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

[5 symbols processed]

[Report written to ' D:/output_html_format.html']

$