histograms_oracle_events_version_independent.stp

#!/usr/local/bin/stap
#
# histograms_oracle_events_version_independent.stp
#
# This is a SystemTap script to gather wait event details from Oracle and print 
# wait event latency histograms
#
# Dependencies and prerequisites: 
#    Use SystemTap 2.5 or higher
#    Kernel must have support for uprobes or utrace (for example RHEL/OL 7.x and 6.x)
#    The oracle DB executable should be in the path: add $ORACLE_HOME/bin in $PATH
#
# Software versions and compatibility:
#     Linux RHEL/OL 6.x and 7.x 
#     Oracle RDBMS 11.2.0.x and 12.1.0.x
#
# Notable exception and issue with Oracle 12.1.0.2: 
# this script will throw "inode-offset registration error" when run against 12.1.0.2 on 
# RHEL/OL7.1 (i.e. kernel 3.10.0-229.x). The workaround is to use an older kernel
# such as RHEL/OL7.0 (kernel 3.10.0-123.x). It seems to work fine on RHEL/OL 7.1 and 11g.
#
# How to run: stap -v histograms_oracle_events_version_independent.stp
# Note optionally add -x <pid> to limit data collection to 1 process
#
# Version 1.0, Aug 2014 by Luca.Canali@cern.ch
# Additional credits for original contributions: @FritsHoogland
#
# Note: this is experimental code, use at your own risk
# 

global eventlatency[2000]
global waittime[2000]

# gather and aggregate wait event latency details into a histogram
probe process("oracle").function("kews_update_wait_time") {
   # update the wait time, the wait event number is captured in the call to kskthewt
   wait_time = u32_arg(2)
   # some false positives have wait_time =0 in particular for background processes, we ignore them
   if (wait_time > 0) {
      waittime[pid()] = wait_time
   }
}

 
probe process("oracle").function("kskthewt") {
   # the event number is in arg2
   event=u32_arg(2)
   # the wait_time was previously recorded into the waittime array
   eventlatency[event] <<< waittime[pid()]
}


# print histogram details every 10 seconds and reset the counters in eventlatency[]
probe timer.sec(10) {
    printf("\nDate: %s\n\n",tz_ctime(gettimeofday_s()))
    foreach ([event] in eventlatency) {
       printf("Latency histogram (value=latency in microsec) for event#=%d\n",event)
       print(@hist_log(eventlatency[event]))
    }
    # comment this if you prefer that histograms grow comulative instead
    delete eventlatency
}