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 :

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 :

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)