Investigating Oracle locks using systemtap

A few weeks ago i was reading a great article written by Franck Pachot  about investigating lock issue using event 10704 , so come to me the idea to use systemtap for this investigation as it give us more flexibility (For example : monitoring TM lock only on a specific table).

The first step is to identify the name of the functions responsible for lock acquisition and release (Lock conversion is left as an exercise to the reader). Hopefully , Franck already identified them using the 10704 event. The second step is to identify which registers may contain interesting value such as lock address,type,etc accessible from our probe points , after a few trial and errors it was easy to determine them.

Here is it :  (My test database is 12.1.0.2.6/OEL6/UEK3)

Lock get : ksqgtlctx

  • Register(R08) : Lock type  (INDX column on X$KSIRESTYP)
  • Register(RSI) : Mode
  • Register(R09) : id1
  • Register(R12) : id2
  • Register(RDI) : lock address

Lock release : ksqrcl

  • Register(RDI) : lock address

That’s it 😀 we can now write the scripts we want based on probes on the previous functions.

This simple script print the process id of the sessions requesting a TM lock (INDX 79 on table X$KSIRESTYP) on object id 112769  (Table TX).


#! /usr/bin/env stap

probe process("oracle").function("ksqgtlctx") {
if ( register("r9") == 112769 && register("r8") == 79 )
{
printf("Lock of type TM was requested on table TX by process %d \n",pid() ) ;
}
}

Capture 1

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