SYSTEMTAP Oracle session perf (CPU + WAITS) Direct SGA access (StapOra V0.2)

UPDATE 26/05/2015 :  For the new version of StapOra including bug fixes and enhancement please click here

In the previous post i have developed a systemtap script to monitor CPU usage (Oracle CPU monitor version 0.1). So here i’am going to extend the script to include oracle wait events and CPU usage from the point of view of the oracle database using direct SGA access.

Here is a quick overview of the systemtap script (Renamed StapOra V0.2 )

  • Top wait events
  • Time spent on the run queue
  • IO wait time
  • Top kernel function
  • Top user function
  • Consistent Read by object
  • Consistent Read elapsed time and cpu time
  • Number of context switches

I will Explain here only the new added part and how it was developed:

PART 6 :

Capture

The first step was how to find the session address without using SQL to gain access to the wait event information. After many tests i have found that it was possible by putting a probe point on function ksl_get_shared_latch(laddress,wait,why,where) .In fact when acquiring the “session idle bit” latch with the  “where” equal to some value it appear that the “why” is pointing to session address.

probe process(“oracle”).function(“ksl_get_shared_latch”) {
  if (pid() == target() && session_addr == 0 && ( s64_arg(4) == 115 || s64_arg(4) == 116 ) ) {
    session_addr =  s64_arg(3)
    printf(“Session initialized %d\n”,pointer_arg(3))
  }
}

session idle bit (http://www.toadworld.com/platforms/oracle/w/wiki/852.latch-free.aspx)

Every time the session starts to execute a call, the active bit will be set by the session and when the session finishes the call, it will clear the active bit. The status field in v$session will change from ACTIVE to INACTIVE (protected by this latch).

UPDATE 14/10/2016 : You have to use the following query to determine interesting where location to probe for (where 115 and 116 correspond respectively to “ksupucg: set busy” and “ksupop: clear busy” in my 12.1.0.2.6 instance)

SELECT indx
FROM x$ksllw
WHERE     KSLLWLBL = 'session ptr'
AND KSLLWNAM like '%set busy%'
AND indx IN (SELECT indx
FROM x$kslwsc
WHERE KSLLASNAM = 'session idle bit');

I will add other pobe point in the near future to try to capture the session_addr from other functions.

The next step is to find the different offset of the columns of the table “X$KSUSECST” (V$SESSION_WAIT). Thanks to Kyle Haiely (http://fr.slideshare.net/khailey/sga-access) and Luca Canali  (http://externaltable.blogspot.com/2014/09/systemtap-into-oracle-for-fun-and-profit.html) they are defined on “x$kqfco “.

This step have to be done only one time to determine the offset specific to your platform.

set linesize 190 pagesize 0;
select ‘global ‘ ||  c.kqfconam ||’ = ‘ || c.kqfcooff  from x$kqfco c, x$kqfta t
where t.indx = c.kqfcotab
and t.kqftanam=’X$KSUSECST’
and c.kqfconam in (‘KSUSSTIM’,’KSUSENUM’,’KSUSSOPC’)
order by c.kqfcooff;

Take the output and replace the part indicated on the scripts :

Capture

Next we have to access the memory address and capture the wait events :

probe timer.ms(1) {
 
# Profiling wait events
if ( session_addr > 0 )
{
   sess_id = user_uint16(session_addr + KSUSENUM)
   wait_time = user_uint32(session_addr + KSUSSTIM)
   event_num = user_uint32(session_addr + KSUSSOPC)

   if ( sess_id != 0  ) {
       total_samples <<< 1      
       session_id = sess_id
       if (wait_time == 0 )
       {
        session_waits[event_num] <<< 1
       }
       else
       {
        session_waits[-1] <<< 1
       }
   }

}

Note : There are cases when user_uint16 return 0  (controled by  if ( sess_id != 0  ) ) and it mean that we cannot access the specified address so it reduce considerably the number of samples that we can take and that affect the final result.I haven’t yet investigated this problem i hope i will fix it on the next version.(Any helps or suggestion  are welcomes.)

The final step is to print the TOP wait events.To translate the event# to the actual event_name i used Luca Canali scripts (http://canali.web.cern.ch/canali/res/All_Files/stap_scripts/eventsname.sql) so we must add | sed -f eventsname.sed at the end of the script.

      printf (“\n———————————————————\n”)
      printf (“——- Oracle wait events/CPU (CPU WAIT)—————-\n”)
      printf (“———————————————————\n”)
      if ( session_addr > 0 )
       {
        printf(“Total samples : %d\n”,@count(total_samples))
       foreach ([w] in session_waits- limit 5) {
              event_ratio = 10000 * @count(session_waits[w]) / @count(total_samples)
              if ( w == -1 ) {
                printf (“%2d.%02d %s samples CPU/CPU WAIT   \n”,event_ratio/100,event_ratio%100,”%”)
              } else {
                printf (“%2d.%02d %s samples  event#=%ld  \n”,event_ratio/100,event_ratio%100,”%”,w)
              }
        }
        delete session_waits
        delete total_samples
      }
      else
      {
        printf(“No wait events captured (Session not initialized yet)\n”);
      }

Here is a sample output :

Running stapora.stp :

lance

Output when session is idle :

idle

Output for an active session :

running

That’s it, StapOra is only on his first version so it still plenty of work to be done 😀

Download StapOra 

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