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 !

Continue reading

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

Continue reading

POUG : Memory access tracing slides

You know POUG ? Of course you know POUG ! This is one of the best  Oracle conference out there ! Great peoples ! Great Speakers ! Great Place ! Awesome ambiance !!!

Last week i was honored to be there ! It was a pleasure to meet many other Oracle geeks and to participate to this great event !

Poug_day

bad

So as promised here is my presentation (My first public speak by the way)  on slide share : Just click here !

Many thanks for all the POUG Team for this great adventure ! Don’t miss POUG18 !!!!

Workaround for Linux Perf Probes issue for Oracle Tracing

As explained in my previous post there is some issues with uprobes and the recent kernel/oracle version.Based on the workaround that i described i will show in  this short blog post how we can put a probe point on oracle function using Linux Perf. Sadly i haven’t figured out a way to do that using systemtap (Special thanks to Frank Ch. Eigler for his help)

Continue reading

uprobes issue with oracle 12c

If you are using dynamic tracing tools like systemtap/perf for user space probing (based on uprobes/uretprobes) with recent oracle/kernel version you may have hit this issue.As stated by Luca Canali Ref

“Issues with uprobes and Oracle versions: uprobes works OK for tracing Oracle 11.2. However, for Oracle 12.1 I find that uprobes works OK on  RHEL7.0 (kernel 3.10.0-123) and UEK (kernel 3.8.x), but does not work for kernels that ship with RHEL 7.1,7,2 and anything higher (including UEK4). When testing the easiest is to use Oracle 11.2 or if you want to test Oracle 12.1 use UEK3 or RHEL 7.0 kernel. More investigations are needed on this topic.”

Let’s check :

Continue reading

Dynamic tracing tools : Easier access to session/process address [ksupga_]

When troubleshooting a performance problem or investigating oracle internal using dynamic tracing tools like systemtap,it’s often useful to have the session address at hand. In fact, having the session address we can access many useful information such as : wait_event,p1 and p2  value,sql_id,and many other fields as stored in X$KSUSE (underlying table to V$SESSION). Luca Canali have already done a great work ,he identified that when the function “kskthewt” is called at the end of a wait event the register R13  (tested with Oracle 11.2.0.4 on RHEL6.5 and with Oracle 12.1.0.2 on OEL7 respectively) is pointing to the session addr with some offset and he manged also to determine the offset of the different column of X$KSUSE using X$KQFCO and X$KQFTA as in here.

The question is : Can we determine the session address without probing any function call ?

One way to answer this question is to determine how the value stored in the register R13 was set in the function “kskthewt”. Time to disassemble !

NOTE : This post contain no disassembly code of the oracle executable just the finding !

For basic info on reverse engineering please take look at my previous post.

Continue reading

perf_events : Off/On/Mixed CPU flamegraph extended with oracle wait events

Using  FlameGaphs  for investigating performance problem can be a valuable asset for quick resolution and identification of the root cause. This type of analysis may be needed when the traditional oracle instrumentation are not enough.

This post is based and inspired by the awesome work of Brendan Gregg ,Luca Canali and Frits Hoogland in this area. Please check the references at the end of the post for more info (Worth reading !)

What i will cover here is a tiny script i written for generating 3 types of extended flamegaph using the build in perf tool. I said extend because they actually include the oracle wait events.

  • Off cpu
  • On cpu
  • HOT/COLD flame graph

Continue reading