V$SQL_CS_STATISTICS return no rows in 12C ! It’s time to trace ! [systemtap tracing]

In my last blog post i tried to demonstrate the effect of activating row source statistics on the calculation of “rows_processed” in v$sql_cs_statistics and the effect this may have in Adaptive cursor sharing. Sadly V$SQL_CS_STATISTICS  appear to return no rows in 12c (12.1.0.2) and also as Mohamed Houri said “Even in 11g the number of rows processed was not updated before a cursor is bind aware” (In the comment section of my last post ). So there is apparently no way to see the different raw execution statistics used by the monitoring component of adaptive cursor sharing in 12C or before the cursor is marked bind aware in 11G ! Really  ? It’s time to dig deeper !

The first step is to find the list of functions responsible for collecting the different raw execution statistics so i used  systemtap tool and a script for listing function call graph when running the example from my previous post.After analyzing the function name and argument stored in the CPU register (RDX,RCX,RSI,RDI)  i get this interesting function kkocsGetRowsRws which seem to retrieve the raw execution statistics and do some calculation.Here is the systemtap script that we will use for the analysis :

#! /usr/bin/env stap
probe process(“oracle”).function(“kkocsGetRowsRws”) {
 if (pid() == target()) {
    printf(“function %s %d  \n”, ppfunc(), register(“rdx”));
 }
}

I will now redo the same test done in the last post with recode_type = 3 :

In 11.2.0.3.9 :

11g test

So we have 50003 row processed , 520 buffer gets , 0 cpu time .

In 12.1.0.2 :

Capture 12c

Emmm nothing let’s retry after enabling tracing of the process with systemtap :

Capture 2

Yeep we can use the value stored on the register “RDX” when calling the function “kkocsGetRowsRws” to determine the number of row processed (The calculation of the final row_processed can become tricky with more complex plan i haven’t determinate yet how to combine them but we can always get an approximation) .

I think that buffer_gets and cpu_time are simply peeked from V$SQL buffer_gets and cpu_time but it’s only an assumption

Let’s now see the result after activating row source statistics :

Capture effect

So it’s now clearer that the collection of row source statistics affect on some way the calculation of row_processed !

This is simply a quick investigation (so there are maybe some false assumption ) to demonstrate that we can always try to find the information we are looking for at lower level when we can not find it the usual way.

That’s it 😀

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s