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 !

Capture 02

Capture 01

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 /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 .

Capture 20

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 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.

Capture 04

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 :

Capture 05

Capture 06


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


Capture 08

Capture 09

Capture 10

Stay tuned more to come !

That’s it 😀




2 thoughts on “Geeky PL/SQL tracer/profiler : First step

Leave a Reply

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

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

Facebook photo

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

Connecting to %s