SystemTap and Oracle RDBMS: Aggregate by database

Introduction

The purpose of this post is to share a way to aggregate by Oracle database within SystemTap probes. Let’s describe a simple use case to make things clear.

Use Case

Let’s say that I want to get the number and the total size of TCP messages that have been sent and received by an Oracle database. To do so, let’s use 2 probes:

• probe::tcp.recvmsg
• probe::tcp.sendmsg

and fetch the command line of the processes that trigger the event thanks to the cmdline_str() function. In case of a process related to an oracle database, the cmdline_str() output would look like one of those 2:

• ora_<something>_<instance_name>
• oracle<instance_name> (LOCAL=<something>

So let’s write two embedded C functions to extract the Instance name from each of the 2 strings described above.

Functions

get_oracle_name_b:

For example, this function would extract BDT from “ora_dbw0_BDT” or any “ora_<something>_BDT” string.

The code is the following:

function get_oracle_name_b:string (mystr:string) %{
char *ptr;
int  ch = '_';
char *strargs = STAP_ARG_mystr;
ptr = strchr( strchr( strargs , ch) + 1 , ch);
snprintf(STAP_RETVALUE, MAXSTRINGLEN, "%s",ptr + 1);
%}

get_oracle_name_f:

For example, this function would extract BDT from “oracleBDT (LOCAL=NO)”, “oracleBDT (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))” or any “oracleBDT (LOCAL=<something>” string.

The code is the following:

function get_oracle_name_f:string (mystr:string) %{
char *ptr;
int    ch = ' ';
char substr_res[30];
char *strargs = STAP_ARG_mystr;
ptr = strchr( strargs, ch );
strncpy (substr_res,strargs+6, ptr - strargs - 6);
substr_res[ptr - strargs - 6]='\0';
snprintf(STAP_RETVALUE, MAXSTRINGLEN, "%s",substr_res);
%}

Having in mind that the SystemTap aggregation operator is “<<<” (as explained here) we can use those 2 functions to aggregate within the probes by Instance Name (passing as parameter the cmdline_str()) that way:

probe tcp.recvmsg
{

if ( isinstr(cmdline_str() , "ora_" ) == 1 && uid() == orauid) {
tcprecv[get_oracle_name_b(cmdline_str())] <<< size
} else if ( isinstr(cmdline_str() , "LOCAL=" ) == 1 && uid() == orauid) {
tcprecv[get_oracle_name_f(cmdline_str())] <<< size
} else {
tcprecv["NOT_A_DB"] <<< size
}
}

probe tcp.sendmsg
{

if ( isinstr(cmdline_str() , "ora_" ) == 1 && uid() == orauid) {
tcpsend[get_oracle_name_b(cmdline_str())] <<< size
} else if ( isinstr(cmdline_str() , "LOCAL=" ) == 1 && uid() == orauid) {
tcpsend[get_oracle_name_f(cmdline_str())] <<< size
} else {
tcpsend["NOT_A_DB"] <<< size
}
}

As you can see, non oracle database would be recorded and displayed as “NOT_A_DB”.

Based on this, the tcp_by_db.stp script has been created.

tcp_by_db.stp: script usage and output example

Usage:

$> stap -g ./tcp_by_db.stp <oracle uid> <refresh time ms>

Output Example:

$> stap -g ./tcp_by_db.stp 54321 10000
---------------------------------------------------------
NAME              NB_SENT   SENT_KB   NB_RECV   RECV_KB
---------------------------------------------------------
VBDT              5439      8231      10939     64154
NOT_A_DB          19        4         41        128
BDT               19        50        35        259

---------------------------------------------------------
NAME              NB_SENT   SENT_KB   NB_RECV   RECV_KB
---------------------------------------------------------
VBDT              267       109       391       2854
NOT_A_DB          102       19        116       680
BDT               26        55        47        326

---------------------------------------------------------
NAME              NB_SENT   SENT_KB   NB_RECV   RECV_KB
---------------------------------------------------------
VBDT              340       176       510       2940
NOT_A_DB          150       8         151       1165
BDT               42        77        61        423

Remarks:

• The oracle uid on my machine is 54321
• The refresh time has been set to 10 seconds
• You can see the aggregation for 2 databases on my machine and also for all that is not an oracle database

Whole Script source code

The whole code is available at:

• This github repository
• This page

Conclusion

Thanks to the embedded C functions we have been able to aggregate by Oracle database within SystemTap probes.