It is widely known that the Oracle server uses kslgetl – Kernel Service Lock Management Get Latch function to acquire the latch. In 2006 Tanel Poder first demonstrated that oradebug call kslgetl/kslfre can be used to acquire the latch manually. This is very useful to simulate latch related hangs and contention.
For several years it was commonly supposed that kslgetl() has two parameters – latch address and wait. But on AIX 5L we have unique procstack tool. This tool shows the actual number of parameters for function. It occurs that kslgetl has four parameters:
... sskgpwwait(??, ??, ??, ??, ??) + 0x38 skgpwwait(??, ??, ??, ??, ??) + 0xbc kslges(??, ??, ??, ??, ??) + 0x54c kslgetl(??, ??, ??, ??) + 0x33c ksfglt(??, ??, ??, ??, ??) + 0x198 ...
We do know the meaning the first two of parameters. What about the others?
At my work I had to analyze a lot of errostacks for latch related problems. My guess is that these kslgetl parameters should be named like:
- kslgetl(laddr, wait, why, where) – Get exclusive latch
More precisely, to request the latch Oracle kernel needs:
- laddress – address of latch in SGA
- wait – flag. If true, this is latch get in willing-to-wait mode..
- where – code for location from where the latch is acquired.
- why - context why the latch is acquired at this where.
“Where” and “why” parameters are using for the instrumentation of latch get. Integer “where” value is the reason for latch acquisition. This is the index in an array of “locations” strings that literally describe “where” . Oracle externalize this array to SQL in x$ksllw fixed table.
SQL> select indx, ksllwnam,ksllwlbl from x$ksllw; INDX KSLLWNAM KSLLWLBL ----- ----------------------------------- ---------------------- 0 No latch 1 kywmcrpln: creating new WLM plan … 82 ksuxds session ptr 83 ksukls: mark killed session ptr 84 ksukls: move session session ptr … 3707 kghupr1 Chunk Header 3708 kghchk:dsidx heap descriptor …
Note that this “location” is not the name of function from which the latch have gotten. These strings the DBA commonly see in v$latch_misses and AWR/Statspack reports.
Fixed view v$latch_misses is based on x$kslwsc fixed table. In this table Oracle maintains an array of counters for latch misses by “where” location. X$kslwsc allows us to find which “where” location allowed by Oracle for each latch:
select indx from x$kslwsc where ksllasnam = ‘<latch name>’;
Also, these strings present as “Location from where latch is held” in process state object dumps. For example, look at the process holding cache buffers chains latch:
PROCESS 299: ---------------------------------------- SO: 0x2d93be720, type: 2, owner: (nil), flag: INIT/-/-/0x00 (process) Oracle pid=299, calls cur/top: 0x2e9028a38/0x2e9028a38, flag: (0) … (latch info) wait_event=0 bits=2 holding 2dee1ac50 Child cache buffers chains level=1 child#=124200 Location from where latch is held: kcbgtcr: fast path: Context saved from call: 39022946 …
Tanel Poder wrote the excellent article about systematically troubleshooting latch contention. His famous latchprofx.sql tool samples v$latchholder/x$ksuprlat at extremely high rate. In this article he also discussed meaning of commonly seen “where” parameters
“Why” parameter is named “Context saved from call” in dumps. It specifies why the latch is acquired at this “where”. Its meaning depends on latch and “where”. For example, “why” contains DBA address of block that accessed under protection of cache buffers chain latch. Tanel Poder elegantly used this to investigate the root cause of cache buffers chain latch contention.
“Why” meaning for some of “where” may be guessed from ksllwlbl column of x$ksllw. From the above listing one can conclude that “why” contain the SGA chunk address for shared pool latch and the session address for session idle bit latch correspondingly.
“Where” and “why” parameters instrument the latch get. When the latch will be acquired, Oracle will save these values into the latch structure. Oracle 11g externalizes latch structures in x$kslltr_parent and x$kslltr_children fixed tables for parent and child latches respectively. Versions 10g and before used x$ksllt table. Fixed views v$latch and v$latch_children were created on these tables.
“Where” and “why” parameters for last latch acquisition may be seen in kslltwhr and kslltwhy columns of these tables. Note the column name structure, this is common. These columns is the externalization of corresponding latch structure members.
Fixed table x$ksuprlat shows latches that processes are currently holding. View v$latchholder created on it. Again, “where” and “why” parameters of latch get present in ksulawhr and ksulawhy columns.
And the last, but not the least. When Oracle process waits (sleeps) for the latch, it puts latch address into ksllawat, “where” and “why” values into ksllawer and ksllawhy columns of corresponding x$ksupr row. This is the fixed table behind the v$process view. These columns are extremely useful when exploring why the processes contend for the latch.
Unfortunately Oracle does not include any of these columns in v$latchholder, v$latch and v$process views. This is why I usually prefer to select X$ tables, not V$ views.
Let us see how it works. I will intentionally choose not usually used latch ‘DMON Work Queues Latch’ for my test 184.108.40.206 Linux IA-32 instance:
SQL> select addr, name from v$latch where name='DMON Work Queues Latch'; ADDR NAME -------- ------------------------ 200222A0 DMON Work Queues Latch
Possible “where” values for this latch I found in x$kslwsc:
SQL> select indx from x$kslwsc where ksllasnam ='DMON Work Queues Latch' INDX ----------- 4642 … SQL> oradebug setmypid Statement processed. SQL> oradebug call kslgetl 0x200222A0 1 100 4642 Function returned 1
The process now holds the exclusive latch. One can see this from another session in x$ksuprlat/v$latchholder .
SQL> select ksuprpid pid,ksuprsid sid ,ksuprlat laddr,ksuprlnm name, 2 ksuprlmd,ksulawhy,ksulawhr, ksulagts gets 3 from x$ksuprlat; PID SID LADDR NAME KSUPRLMD KSULAWHY KSULAWHR GETS ----- ----- -------- ----------------------- --------- -------- -------- ---- 31 138 200222A0 DMON Work Queues Latch EXCLUSIVE 100 4642 2
Indeed, process with PID=31 hold ‘DMON Work Queues Latch’ at “where”=ksulawhr=4642 with “why”=ksulawhy=100.
Let us look into the latch structure itself. Parameters of the last latch get:
SELECT kslltwhr,kslltwhy FROM x$kslltr_parent where kslltnam='DMON Work Queues Latch'; KSLLTWHR KSLLTWHY ---------- ---------- 4642 100
If I request the latch from the another session with PID=41, process will sleep for latch.
SQL> oradebug setmypid Statement processed. SQL> oradebug call kslgetl 0x200222A0 1 100 4643 <... wait ...>
Process number 41 currently waits for the latch. Its latch get parameters are:
SQL> SELECT ksllawhy,ksllawer FROM x$ksupr where indx=41; KSLLAWHY KSLLAWER ---------- ---------- 100 4643
This is all about kslgetl parameters.