DBMS_HPROF Flame Graphs

Generating Flame Graphs from Oracle PL/SQL profiler trace files requires

  1. An Oracle directory object to write the HPROF trace files to
  2. Grants to allow one to execute DBMS_HPROF
  3. The awk script below to convert the HPROF output into the "folded format"
  4. Brendan Gregg's Flame Graph script flamegraph.pl
  5. Profile the PL/SQL code and generate the flame graph

1. Oracle Directory Odds

Within Oracle create the directory
  CREATE DIRECTORY dir_hprof AS '/home/me/data/hprof';
and ensure that the Oracle process will be able to write to the directory (possibly   chmod 777   on the directory).

The account which will be used for tracing the PL/SQL code will need READ and WRITE access
  GRANT READ, WRITE ON DIRECTORY dir_hprof TO me;

2. DBMS_HPROF Grants

The account which will be used for tracing the PL/SQL code will need EXECUTE access to DBMS_HPROF
  GRANT EXECUTE ON dbms_hprof TO me;

3. HPROF trace file processing awk script

I have the following awk script in a file called hprof.awk

#!/usr/bin/awk -f
#
#  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
#

BEGIN {
  # elapse_time    Associative array of the stack position elapse times
  # current_stack  Array containing the position in the HPROF'd code of the current trace line
  call_stack_index = 0;
}

$1 == "P#C" {
    # current_stack[call_stack_index++] = substr($2, 1, match($2, ":")-1);
    current_stack[call_stack_index++] = $2;
}

$1 == "P#R" {
    call_stack_index--;
}

$1 == "P#X" {
  current_stack_definition = "";
  for (i=0; i<call_stack_index; i++)
    if (i==0)
      current_stack_definition = current_stack[i];
    else
      current_stack_definition = current_stack_definition ";" current_stack[i];

  if (elapse_time[current_stack_definition] > 0)
    elapse_time[current_stack_definition] += $2;
  else
    elapse_time[current_stack_definition] = $2;
}

END {
  for (stack in elapse_time) {
    print stack " " elapse_time[stack];
  }
}
    

4. Flame Graph script

The latest version of Brendan Gregg's Flame Graph code can be obtained from https://github.com/brendangregg/FlameGraph, and I have the copy of flamegraph.pl which I have been using today here

5. Producing the Flame Graph from a PL/SQL HPROF trace

The steps here are

  1. Trace the PL/SQL code using DBMS_HPROF
    BEGIN
      DBMS_HPROF.start_profiling('DIR_HPROF', 'hprof_test1.trc');
      PKG_MY_PACKAGE.my_procedure;
      DBMS_HPROF.stop_profiling;
    END;
    /
            

    the PL/SQL code above will generate the trace file hprof_test1.trc in the HPROF directory

  2. The awk script above will can be used to process the HPROF trace file to produce a "folded" file
      ./hprof.awk hprof_test1.trc >hprof_test1.folded
  3. Brendan's perl script can then be used to produce the flame graph
      ./flamegraph.pl hprof_test1.folded >hprof_test2.svg
  4. The flame graph produced can then be viewed in your favourite browser or image viewing software.
Example graph generated from profiling DBMS_STATS.gather_schema_stats on an Oracle 12.2.0.1 instance: