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 :
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 18.104.22.168/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 !
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 😀