Oracle DBAs who are so old that they remember the days before Oracle 11.2 probably remember the tuning efforts for latches. I can still recall the latch number for cache buffers chains from the top of my head: number 98. In the older days this was another number, 157.
But it seems latches have become less of a problem in the modern days of Oracle 11.2 and higher. Still, when I generate heavy concurrency I can see some latch waits. (I am talking about you and SLOB mister Closson).
I decided to look into latches on Oracle 12.1.0.2 instance on Oracle Linux 7. This might also be a good time to go through how you think they work for yourself, it might be different than you think or have been taught.
In order to understand how latching works, I searched for Oracle related traces. I could find event 10005, but it did not return anything latching related. My current understanding is that event 10005 is Oracle KST tracing, for which the results are put in X$TRACE.
Luckily, I could get a great headstart by studying the work of Andrey Nikolaev. However, that work seems to be strictly Solaris based.
I decided to take a look on how this works on Linux. In order to do this, I did setup a system for the specific purpose of this test. Disclaimer: The techniques below are for educational purposes only, and should never be done on a real database!
The work of Andrey shows kslgetl() as the overal latching function, which does:
kslgetl
– sskgslgf (immediate latch get)
– kslges (wait latch get)
— kskthbwt
— kslwlmod (setup wait list)
— sskgslgf (immediate latch get)
— skgpwwait (sleep latch get)
— semop
In order to do predictable latch gets and generate latch misses, in a very predictable way, I used the method that Andrey shows on his website (which he attributed to Tanel), which is using oradebug call to call the latch get function. In order to get latch waits, you need at least two processes doing something with a latch, one holding the latch, and another one requesting it. In order to facilitate this, I setup two sqlplus / as sysdba sessions.
Taking a latch manually can be done using the kslgetl or the ksl_get_shared_latch functions. Freeing a latch is done using the kslfre function. The kslfre function just takes the latch address as argument. The kslgetl and ksl_get_shared_latch functions take the following arguments:
1-latch address
2-immediate get (0 means yes, 1 means no)
3-where (X$KSLLW.INDX)
4-why (X$KSLWSC.INDX)
5-mode (8=shared,16=exclusive; only for ksl_get_shared_latch function)
Immediate mode get for a non shared latch.
An immediate mode get just tries to fetch a latch once. In order to manually do an immediate latch get, I done:
Session 1:
SQL> oradebug setmypid Statement processed. SQL> oradebug call kslgetl 0x60023a80 0 0 2442 Function returned 1
This means session 1 has gotten latch 0x60023a80. I gotten the latch in willing to wait mode, but that does not really matter here. The session did get the latch.
If you want to check this, simply use V$LATCHHOLDER view to verify (in another session):
SQL> select * from v$latchholder;
PID SID LADDR NAME GETS CON_ID
---------- ---------- ---------------- ---------------------------------------------------------------- ---------- ----------
38 134 0000000060023A80 cache table scan latch 709 0
Please mind that with a latch manually gotten, you need to free the latch before you try to do anything else with your session, otherwise you encounter a (non critical) ORA-600. Freeing a latch is done using oradebug call kslfre and one argument: the latch address.
Session 2:
SQL> oradebug setmypid Statement processed. SQL> oradebug call kslgetl 0x60023a80 0 0 2442 Function returned 0
The ‘Function returned 0’ means the immediate latch get failed. As you can see this was an immediate get because the second argument is 0.
In order to understand which functions are involved, I first used the perf record linux utility. However, because the immediate get does not spin, and a latch get by all means has speed/low overhead as one of the principal design criterions, I could not see the function.
This meant I needed to go to one of the tools I have used extensively in the past: gdb (the GNU debugger). You need to attach to the Oracle database server shared process locally on the database server. Here is what I did:
# gdb -p 4600 ... (gdb) set pagination off (gdb) rbreak ^ksl.* ... Breakpoint 262 at 0x8ea7b0 <function, no debug info> ksl_event_stats_rollup; (gdb) commands 1-262 type commands for breakpoint(s) 1-262, one per line. End with a line saying just "end". >c >end (gdb) c Continuing.
The first gdb function turns off having to press enter for every full screen of output of gdb, the second function breaks on all functions in the oracle executable that start with ‘ksl’. The commands command creates commands that are executed in gdb if breakpoints 1-262 are encountered, which is ‘c’: continue.
Now, with the debugger set, I executed the kslgetl function again:
Breakpoint 251, 0x000000000c8e5720 in kslwtectx () Breakpoint 253, 0x000000000c8e78e0 in kslwt_end_snapshot () Breakpoint 252, 0x000000000c8e7320 in kslwt_update_stats_int () Breakpoint 240, 0x000000000c8dccf0 in ksl_get_shared_latch () Breakpoint 244, 0x000000000c8de960 in kslfre () Breakpoint 247, 0x000000000c8e10a0 in kslws_check_waitstack () Breakpoint 240, 0x000000000c8dccf0 in ksl_get_shared_latch () Breakpoint 244, 0x000000000c8de960 in kslfre () Breakpoint 245, 0x000000000c8dedf0 in kslwtbctx () Breakpoint 246, 0x000000000c8e08e0 in kslwt_start_snapshot () Breakpoint 251, 0x000000000c8e5720 in kslwtectx () Breakpoint 253, 0x000000000c8e78e0 in kslwt_end_snapshot () Breakpoint 252, 0x000000000c8e7320 in kslwt_update_stats_int () Breakpoint 242, 0x000000000c8ddcb0 in kslgetl () Breakpoint 245, 0x000000000c8dedf0 in kslwtbctx () Breakpoint 246, 0x000000000c8e08e0 in kslwt_start_snapshot () Breakpoint 251, 0x000000000c8e5720 in kslwtectx () Breakpoint 253, 0x000000000c8e78e0 in kslwt_end_snapshot () Breakpoint 252, 0x000000000c8e7320 in kslwt_update_stats_int () Breakpoint 245, 0x000000000c8dedf0 in kslwtbctx () Breakpoint 246, 0x000000000c8e08e0 in kslwt_start_snapshot ()
It is important to understand Oracle does a lot of other stuff outside the latch get via kslgetl. Most of the stuff above are functions which start with kslwt, which is the Oracle wait interface. A couple of times a shared latch is taken (as can be seen by the function ksl_get_shared_latch), and freed (kslfre). The important part here is: kslgetl is executed once, and did not go into any other function to try to get the latch.
Getting a latch in willing to wait mode for a non shared latch.
Now let’s do something a bit more exciting: getting a taken latch in willing to wait mode. The first session can do exactly the same, just take the latch. The second session needs to be changed a little bit to indicate it is willing to wait:
SQL> oradebug cell kslgetl 0x60023a80 1 0 2442
This will call additional functions. In order to understand what these functions are, I used perf record, perf report and perf script.
I was able to create a smaller, more specific gdb script to see what is going on:
break kslgetl
commands
silent
printf "kslgetl laddr:%x, willing:%d, where:%d, why:%d\n", $rdi, $rsi, $rdx, $rcx
c
end
break kslges
commands
silent
printf "kslges %x, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx
c
end
break kslwlmod
commands
silent
printf "kslwlmod %d, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx
c
end
break skgpwwait
commands
silent
printf "skgpwwait %d, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx
c
end
break sskgpwwait
commands
silent
printf "sskgpwwait %d, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx
c
end
break semop
commands
silent
printf "semop %d, %d, %d, %d\n", $rdi, $rsi, $rdx, $rcx
c
end
This is how the gdb output looks like when the latch get in willing to wait mode is executed:
kslgetl laddr:60023a80, willing:1, where:0, why:2442 kslges 60023a80, 0, 1, 0 kslwlmod 13311368, -1780327896, 1610758784, 1 skgpwwait 13311608, -1767360, -1780326976, 0 sskgpwwait 13311608, -1767360, -1780326976, 0 semop 360451, 13310840, 1, -1
Interestingly, if the latch is not taken, this is how the latch get sequence looks like:
kslgetl laddr:60023a80, willing:1, where:0, why:2442
In other words, for getting a non shared latch in willing to wait mode:
1-the function kslgetl is called, which tries to fetch the latch.
If the latch can be taken, the function returns, if not:
2-the function kslges (kernel service latch get spinning) is called, which supposedly also tries to take the same latch.
If the latch still can not be taken, the next function is:
3-the function kslwlmod (kernel service latch waiting list modify) is entered.
In this function the process registers itself as waiting in the post/wait list.
4-the function skgpwwait (system kernel generic post/wait wait) is entered.
This function sets up the waiting for the process so it can be posted.
5-the function sskgpwwait (system system kernel generic post/wait wait)
My current understanding is the ‘ss’ function contain the platform specific code for database functions.
6-the (operating system) function semop (semaphore operation) is called.
This will make the process sleep waiting on a semaphore (operating system signalling mechanism). This way, the process will not be runnable on the CPU unless the semaphore is posed.
The information that is missing here, is the spinning. The earlier work of Andrey Nikolaev showed that in the Solaris port of the database, a distinct function (sskgslgf [immediate] and sskgslspin [spin]) was used to get the latch, which made it easy to count.
Some searching around revealed that a CPU register reveals this information. Add this to the above gdb script:
break *0xc29b51
commands
silent
printf " kslges loop: %d\n", $ecx
c
end
And try to get a non shared taken latch in willing to wait mode:
kslgetl laddr:60023a80, willing:1, where:0, why:2442 kslges 60023a80, 0, 1, 0 kslges loop: 19999 kslges loop: 19998 ... kslges loop: 1 kslges loop: 0 kslwlmod 1208568840, -1780327896, 1610758784, 1 skgpwwait 1208569080, -1281169344, -1780326976, 0 sskgpwwait 1208569080, -1281169344, -1780326976, 0 semop 360451, 1208568312, 1, -1
So…this reveals that getting a non shared latch in willing to wait mode will spin 10*_spin_count. In other words: not _spin_count, which is 2000 by default. What is even more interesting, is for the described type of latch, there is no (short) timed sleep done; the kslges function spins 10*_spin_count times for the latch, then puts itself on the waiting list, and goes to sleep on a semaphore. The way the latching mechanism works has been described (by Oracle AFAIK) as that a process will spin for _spin_count times trying to get a latch, then goes to sleep for some time, and then spins trying to get the latch, goes to sleep, etc.
I strace’d the process holding the latch to see if it is doing a semctl systemcall to signal the waiting process if the latch is freed, and indeed that is what is happening. This is different from the “old days” where processes spinning on latches (cache buffers chains latches most of the time) were really hammering the system and would eat up a lot of the CPU slices trying to get a latch.
Part of the classic latching problem, specifically the cache buffers chains latching problem, quite probably is mitigated by having shared latches, which were introduced in Oracle in different versions for different ports.
Watch out for a second blogpost where I do the same investigation for shared latches.
Frits Hoogland Weblog 