To diagnose PL/SQL performance problems, Oracle provides the Hierarchical profiler. The profiler produces a stack trace with timings. After feeding this trace into the Oracle provided tools a bunch of html files are created that provide you with all sorts of aggregates to find the performance bottleneck in your PL/SQL code.
Imagine all those numbers visualised in a graph, that would provide an insight in your code in the blink of an eye. Well, such a tool exists. Brendan Gregg created Flame Graph.
Brendan’s site lists all sorts of scripts for a whole plethora of programming languages. But, PL/SQL is missing.
Get the stack collapse perl script below and Follow the steps outlined to create a PL/SQL flame graph.
Step 1. Profile your code
SQL> exec dbms_hprof.start_profiling('PROFILER', 'flame_trace.trc'); PL/SQL procedure successfully completed (0 ms) SQL> <Execute your code here> SQL> exec dbms_hprof.stop_profiling; PL/SQL procedure successfully completed (16 ms) SQL>
Step 2. Stack collapse your trace file
Next, grab the trace file (flame_trace.trc in this case) from the ‘PROFILER’ directory and run it through the stackcollapse-hprof.pl script (get it here )
js@hercules# ./stackcollapse-hprof.pl flame_trace.trc > flame_trace_collapse.trc
Stop 3. Create the flame graph
js@hercules# ./flamegraph.pl --minwidth 0.001 --title "Stack collapsed" flame_trace_collapse.trc > flame.svg
Et voilà, an interactive Flame Graph SVG is created.