Geeky PL/SQL tracer/profiler : First step

This blog post is about how to extend our capabilities to trace and profile PL/SQL code.It’s primarily motivated by few tweets from Franck Pachot and of course because it’s FUN !

So in the first part of this series we are going to answer to this questions : Can we map those underling function to the source PL/SQL object and line number ? Can we obtain a full trace ? Of course yes otherwise there will be no blog post :p

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

Whenever an exception is raised in our PL/SQL code or when requesting a dump of an ERROR STACK we can track the actual line executed .

So it seem that there is a way to revert back to source code line number and PL/SQL object from the PL/SQL bytecode.Armed with the PIN TOOLS debugtrace.so i was able to identify the secret weapon and it’s named pfrln0lookup ! 

Reverse engineering this function seem quite complex so i decided to use it as it’s.

This function when executed will return the actual line number in HEX. The two parameter that are important here are KGLHDADR of the actual pl/sql object and the bytecode offset  (Al least it seem to be).

The second step is how to extract those parameters from the code ! Few days later  :p here is a prototype (Far from being complete) of the geeky PL/SQL tracer coded as a systemtap  script:

Pl/SQL code to trace :

 

Extract from the output of the geeky tracer :(Tracing exec system.profile_me; [Don’t create object in the system schema :p])

Stay tuned more to come !

That’s it 😀

DOWNLOAD :

• plsql_tracer.stp
• sql_resolver.sh