Extending systemtap scripts with oracle session info

There are many useful systemtap scripts out there that can be used for troubleshooting performance problem or any other tasks. In some case it could be handy to extend them with session information to have a better picture of what’s going on.Based on my previous work on Dynamic tracing tools : Easier access to session/process address [ksupga_] here is some examples :

  • TEST SERVER : OEL 6/UEK4/ORACLE 11.2.0.4
  • schedtimes.stp

This script profiles threads and displays their run times, queued times, wait times, including i/o wait times.After some modification here is what we can do :

capture-01

Old script :schedtimes.stp
New script :schedtimes_wsi.stp

  • socktop.sh

This script is a combination of shell/systemtap script to track reads and writes on sockets by process.  Can be filtered by process IDs and names, protocols, protocol families, users and socket type.After some modification here is what we can do :

capture-02

Old script :socktop.sh
New script :socktop_wsi.sh

Useful scripts :

Run this query to check for the offset of the sid ,sql_hash and client_info fields


select c.kqfconam FIELD_NAME, c.kqfcooff OFFSET from x$kqfco c, x$kqfta t
where t.indx = c.kqfcotab
and t.kqftanam='X$KSUSE'
and c.kqfconam in ('KSUSENUM','KSUSESQH','KSUSECLI')
order by c.kqfcooff;

Script to check the address of ksupga_ symbol


objdump -x -j .bss oracle | grep ksupga_ | awk '{print $1}'

 

That’s it 😀 (Indeed a very short blog post :D)

 

 

2 thoughts on “Extending systemtap scripts with oracle session info

  1. can you help me to correct this error below:

    [root@srvoracle tmp]# ./scok.sh
    semantic error: while resolving probe point: identifier ‘kernel’ at /usr/share/systemtap/tapset/linux/networking.stp:80:5
    source: = kernel.function(“dev_queue_xmit”)

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