Geeky PL/SQL tracer/profiler : Another step

This is my second post under the theme of how to extend our capabilities to trace and profile PL/SQL code.This time motivated by a comment from Luca Canali on my previous post :

Nice work,.. do you take requests? 😉 I'd love to have a tool for PL/SQL Flame Graphs improving on the method based on DBMS_HPROF (see fig) pic.twitter.com/nOYRA2YQAq

— Luca Canali (@LucaCanaliDB) September 16, 2017

So based on my previous work on geeky PL/SQL tracer let’s see how we can obtain a geeky PL/SQL on-CPU Flame Graph !

TEST ENV : oracle 12.2.0.1/OEL6/UEK4 /PLSQL_CODE_TYPE=INTERPRETED/systemtap [Workaround for systemtap issue : Oracle Tracing [registration error (rc 0) !]

To draw an extended on-CPU Flame Graph of a PL/SQL code we need a way to profile the call stack of an oracle process.Sadly there seem to be a problem with the user-space stack unwinding using systemtap as it give us only a partial call stack . For this reason i decided to use Perf.

As i am now working with two different tools (Perf to profile the on-CPU call stack and systemtap to get information about the current executing PL/SQL program and line number) i must figure out a way to combine the two ! Hopefully Frits Hoogland already done something similar here using perf -T switch and local_clock_us() function on systemtap !

So basically what i will do is this :

Collect PL/SQL execution context (PL/SQL program/line number/depth) using systemtap
Collect on-CPU stack profile using Perf
Merge the output using a shell script based on the “timestamp column”
Generate a flame graph

After the geeky PL/SQL tracer here is the geeky PL/SQL profiler !

Let’s test it :

PL/SQL code to profile :

Tracing exec system.profile_me; [Don’t create object in the system schema :p]

Normal on-CPU flamegraph :

So here even if we spot the functions that consume most of our CPU time it’s not evident to know which PL/SQL code line we must focus on !

Extended on-CPU flamegraph :

Here the call stack is further divided by PL/SQL program and line number so that we know where to focus our attention !

Download :

Note: This is only an experimental program with many limitations (For example : Getting the call stack and the PL/SQL execution context is not an atomic operation and this can introduce errors / systemtap instrumentation intrusion effect / The code is far from being optimized)

Note : You must update the path to the FlameGraph directory in “plsql_profiler.sh” before usage.

That’s it 😀