Getting Latch holder info out of the state objects in SGA memory

Updated 12/05/2017

It will be great to have a tool that will extract latch holder information directly from state objects stored inside the SGA. This way we will reduce the overhead when troubleshooting latch contention an beside that it’s also cool !!

This may sound difficult ! How to proceed ? and the answer is …. Memory reference tracing !

Here is my test env : ORACLE 12.2.0.1/OEL6/UEK4/Intel Pin 3.0

Based on the work of Andrey Nikolaev :

Each process has an array of references to the latches it is holding.
This kslla structure is embedded into the process state object.

Also Tanel Poder stated that

“V$LATCHHOLDER scans through the process state object array (V$PROCESS/X$KSUPR) and looks into a field there which points to the latch held by a process”

The idea is if we can found the pointer to the first element of the array “ksllalat” we can deduce the other (fixed size). How to do that ?

Step 1 : Launch any statement you want from a session and suspend it when it’s holding a latch.I used gdb for that (Use v$latchholder to verify)

Step 2 : Attach Intel Pin tool pinatrace.so for memory reference tracing to a process from where you will run “select * from v$latchholder”

Step 3 : Analyze the trace file generated in step 2 and look from where the “latch address” was captured (memory address,function name/offset,etc).

Step 4: Dipper analysis using GDB.

The latch array was accessed inside function “kslgprl–>kslgprl_int” which is called for every entry of the process state object array (320 time as parameter processes is set to 320). Inside function “kslgprl_int” the latch array (Which seem to have 18 elements) is accessed in a loop.I was able to find the address of the first element of the array and then calculate it’s offset from the process address (For future use). The other elements are accessed by adding 16 Bytes to the address of first element of the array .

Here is simple query to find the address of latch state objects elements in memory :

Simple latch acquisition example :

From v$latchholder view :

From system state dump “oradebug dump systemstate 266” :

Latch info is stored at slot 0 of the array let’s verify

Direct memory access using GDB :

Multiple latchs acquisition example :

From v$latchholder view :

v$latchholder show all the latch that are acquired simultaneously by the same process !

From system state dump “oradebug dump systemstate 266” :

Latch are stored at slot 1 and 2 let’s verify :

Cool !

Based on this we can write a program to get Latch holding info for all process out of the state objects in SGA memory.(There is still work that need to be done)

Note 1 : The address of the latch that will be acquired/released seem to be stored at address (process addr + 568 +16*18) before being moved/removed to/from the latch state objects.This correspond to “KSLLALAQ” of x$ksupr fixed table.