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  :

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 :

  1. Collect PL/SQL execution context (PL/SQL program/line number/depth) using systemtap
  2. Collect on-CPU stack profile using Perf
  3. Merge the output using a shell script based on the “timestamp column”
  4. 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 :

Capture 01

Capture 02

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

Normal on-CPU flamegraph :

Capture 07

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 :

Capture 05

Capture 06

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 😀

 

 

 

 

One thought on “Geeky PL/SQL tracer/profiler : Another step

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s