From memory request to PL/SQL source line [ errorstack dump ]

In my last blog post i described a geeky way to trace back the responsible PL/SQL code for a particular memory request into the PGA. It was based essentially on a dynamic tracing tool “systemtap” to probe on  specific functions entry such as KGHAL memory allocator functions (Based on Stefan Koehler dtrace script )  and relied on calling an internal oracle function “pfrln0lookup” using “oradebug call” to  get the actual PL/SQL line number.It would have been safer if we reverse engineered the function “pfrln0lookup” to extract only the thing that matter and avoid calling it but this need time and is forbidden :p  !

So here i will describe a safer and simpler approach relying only on collecting multiple errorstack dump samples and a little shell script to parse the trace file !

From memory request to PL/SQL source line

Here is a little experimental script based on a great blog post by Stefan Koehler entitled Trace back to responsible SQL or PL/SQL code for a particular (PGA) memory request by intercepting process with DTrace and one of my old blog post  Geeky PL/SQL tracer/profiler : First step

And here is what it does :

Tracing PL/SQL subprogram calls with parameters values [Dynamic tracing]

The purpose of this blog post is demonstrate again the power of Linux dynamic tracing/instrumentation tools.

In my last blog post Enhancing DBMS_OUTPUT using systemtap i showed how we can track  the parameter values passed to “dbms_output.put_line” routine using systemtap.That was a very simple example because we already know the type of the arguments passed (a simple VARCHAR2) and also because there is only ONE parameter.

Tracking PL/SQL routine calls arguments using dynamic tracing utility like perf or systemtap can become quite complex depending on many things like :

• Argument types
• Argument number
• Argument passed  By Value/By reference
• Subprograms type (nested/package/standalone subprogram)
• Optimization level (ex: inlining of call of procedure)

Time for the serious stuff  with dynamic tracing tool PERF ! 

Injecting a Backdoor in an ORACLE database

In this blog post i wanted to test some of Dennis Yurichev findings described in “Oracle RDBMS rootkits and other modifications” in recent oracle database 12.1.0.2  (The same can be done with 12.2) .So the idea here is to inject a backdoor in oracle LISTENER so that we can access Linux shell remotely.

Enhancing DBMS_OUTPUT using systemtap

This is a short and quick note to show how we can enhance DBMS_OUTPUT capabilities using a small systemtap script without modifying the source code.Basically it will allow us to display the DBMS_OUTPUT message incrementally (the program don’t need to finish it’s execution) by attaching to an already running session (no need to enable DBMS_OUTPUT). The output can also be easily redirected to a file.

The idea is to try to access the function parameters.This can become complex in case of different arguments types and number but in our case there is only one argument of type varchar2.

Deeper look at CPU utilization : SLOB example

This is a followup to my previous posts on Deeper look at CPU utilization :

• Deeper look at CPU utilization : The power of PMU events
• Deeper look at CPU utilization : TMAM Example

Following a comment from Kevin Closson here is the hierarchical execution cycles breakdown based on the TMAM method before and after enabling HUGEPAGES when running SLOB for testing Logical I/O.

Try these experiments with cached SLOB. That'll be an eye opener. https://t.co/j5l7EljdR2

— Kevin (@kevinclosson) October 27, 2017

This will let’s us identify our micro-architectural bottlenecks and correctly characterize the SLOB workloads !

Deeper look at CPU utilization : TMAM Example

This is a followup to my previous post on Deeper look at CPU utilization : The power of PMU events .

So let’s go back to my previous example using the General Exploration View of Intel VTune  :

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

Latch state objects from SGA memory : The fun part !

Here is the followup to my previous blog post Getting Latch holder info out of the state objects in SGA memory  where i described how we can extract latch state objects directly from the SGA memory. So let’s begin !

Suspending a newly forked oracle process

Suppose we want to investigate/debug the initial phases of an oracle process creation such as the authentication step (Oracle getting anyone’s password) , how to processed ? We will have to attach to the process just after it’s creation ! Here is a systemtap script that may help !