VMCD.ORG » Oracle latch internals

Oracle latch internals

Server latch implementation called KSL, e.g. every table with name starting with x$ksl… is latch
related and every SGA has ksl… structure as well. In order to get complete list of x$ksl table,
connect with sysdba permissions to oracle and run query:

SELECT kqftanam
FROM x$kqfta
WHERE kqftanam LIKE ‘X$KSL%’

eg:

[oracle@testdb ~]$ ora si

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 6 15:37:53 2013

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT kqftanam
FROM x$kqfta
WHERE kqftanam LIKE ‘X$KSL%’ 2 3 ;

KQFTANAM
——————————
X$KSLLTR
X$KSLHOT
X$KSLLCLASS
X$KSLECLASS
X$KSLEMAP
X$KSLLD
X$KSLED
X$KSLCS
X$KSLSCS
X$KSLES
X$KSLSESHIST

KQFTANAM
——————————
X$KSLEI
X$KSLLW
X$KSLPO
X$KSLWSC
X$KSLWH
X$KSLWT

17 rows selected.

There are two structures associated with a latch in KSL – ksllt and kslla.

latch instance structure
struct ksllt {
union ksllt.kslltlat kslltlat;
eword kslltlvl; /* latch level, to enforce hierarchy */
ub2 kslltnum; /* latch number, to index descriptors */
uword kslltwhr; /* the context from where the latch is held */
ubig_ora kslltwhy; /* why the latch is being acquired */
ub4 kslltwgt; /* count of times gotten wait */
ub4 kslltngt; /* count of times gotten nowait */
eword kslltefd; /* error frame depth when gotten, this is used to */
ub4 kslltwff; /* count of wait gets that failed first try */
ub4 kslltnfa; /* count of times failed nowait get */
ub4 kslltntg; /* count of total gets of latch */
ub4 kslltwts; /* number of waiters */
ub4 ksllttmr; /* down/hold time timer */
kstat kslltwtt; /* wait time for this latch */
kstat ksllthdt; /* hold time for this latch */
kstat kslltdnt; /* down time for this latch */
kstat kslltwtw; /* wait time for associated wait list latch */
ub4 kslltwxs; /* count of X process waiting because of S holders */
ub4 kslltwsx; /* count of S process waiting because of X holder */
ub4 kslltwsw; /* count of S process waiting because of X waiter */
ub4 kslltmxs; /* X misses owing to S holders */
ub4 kslltmsx; /* S misses owing to X holders */
ub4 kslltmxw; /* S misses owing to X waiters */
ub4 kslltwsl; /* count of times slept when failed to get latch */
struct ksllt * kslltchg; /* last child gotten as part of get-parent op */
ub4 kslltwkc; /* count of wakeups that have been done */
ub4 kslltwth; /* count of sleeps that were holding another latch */
ub4 yields_ksllt; /* count of yields */
BitMask for “struct ksllt.yields_ksllt”
KSLHSTMAX BIT 4 // if you change this, must change x$ table

ub4 ksllthst[4];
ub4 gets_wl_ksllt; /* number of gets of wait list latch */
ub4 misses_wl_ksllt; /* number of misses on wait list latch */
ub4 sleeps_wl_ksllt; /* number of sleeps on wait list latch */
ub4 yields_wl_ksllt; /* number of yields on wait list latch */
struct kslla * waitproc_ksllt; /* log change to wait list */
kgglk waiters_ksllt; /* head of waiter list */
kgglk kslltlnk; /* linked list of children */
void * kslltrec; /* recovery info for this latch */
b1 kslltsp1[3]; /* 1 spare 1 byte field */
uword kslltcnm; /* child number */
uword class_ksllt; /* latch class */
skgslt wl_ksllt;
}

struct kslla {
ksllt * ksllalat[( 8 + 7 )]; /* latches owned at each level */
ksllt * ksllalaq; /* latch being acquired */
ksllt * wl_flux_kslla; /* latch whose wait list I’m acquiring */
ksllt * ksllaxbs; /* EXWAIT bit set in shared latch */
ksllt * ksllawat; /* latch being waited for: not protected by any latch, */
ubig_ora ksllawhy; /* context for the latch req */
uword ksllawere; /* location from where the req is being made */
word ksllaevt; /* event waiting for */
uword ksllalow; /* bit array of latches owned, for levels [0..9] */
ubig_ora ksllaevx; /* extension to the event id ksllaevt */
kgglk waiters_kslla; /* link on waiter list */
struct ksllt * ksllawtr; /* latch waiter list this process is on. */
struct ksllt * ksllaspn; /* latch this process is spinning on */
uword ksllawst; /* process wait state (asleep/awake) */
ub4 ksllamd[( 8 + 7 )]; /* For r/w latches, mode in which latch is held */
uword ksllalpe; /* latch post expected */
kgglk ksllapwl; /* Link in post/wait queue the process is on */
kslpwq * ksllapwq; /* post/wait queue the process is on */
sword ksllapwi; /* Index of p/w queue the process was last on */
krmid ksllapid; /* the res ID for the last post received */
krmid ksllasid; /* the res ID for the last post sent */
uword ksllaprv; /* loc ID for the last post received */
uword ksllapsn; /* loc ID (where) for the last post sent */
ub4 ksllapsc; /* count of # of posts sent by the process */
ub4 ksllaprc; /* count of # of posts received by the process */
struct ksupr * ksllapos; /* the last process to post me */
struct ksupr * ksllalpo; /* the last process posted by me */
kgslfx ksllafac; /* context for wait/post facilities */
uword ksllawrk; /* is there work for a process about to be posted */
ub2 ksllasfa; /* service managed wait facility */
BitMask for “struct kslla.ksllasfa”
KSLLASFA_IO BIT 0x0001 // ksfd I/O pending
KSLLASFA_IPC BIT 0x0002 // ksxp I/O pending

}

struct kslld {
text * kslldnam; /* human-readable latch name */
void (*kslldcln); /* _ sword action, ksllt *lp _ cleanup proc*/
void (*kslldgcl); /* _ kgsmp *gp, sword action, struct kgslt *lp _ */
size_t kslldrsz; /* recovery structure size */
b1 kslldlvl; /* latch level */
b1 kslldpnt; /* TRUE if parent of a class of latches */
ub1 kslldlng; /* TRUE if latch can be held for long periods */
b1 kslldg2c; /* parent allowing wait get of 2 children */
uword kslldshl; /* TRUE if latch is shareable */
uword kslldcls; /* latch class */
}

struct ksllt is a definition of the latch itself and all these structures (latches itself and pointers to
hold latches v processes) are visible on instance level via fixed SGA, while kslla is a process
related structure and is visible as part of X$KSUPR(Kernel Services User Process) structure

All available kslla structures can be identified by using the following query:

SELECT c.kqfconam field_name, c.kqfcooff offset , kqfcotyp,
DECODE (kqfcotyp ,
0, ‘hex string’,
1, ‘varchar2’ ,
2, ‘number’ ,
11, ‘word’,
12, ‘datetime’ ,
23, ‘raw’ ,
‘other’
) typ,
kqfcosiz sz
FROM x$kqfco c , x$kqfta t
WHERE t.indx = c.kqfcotab
AND kqftanam = ‘X$KSUPR’
AND kqfconam LIKE ‘KSLLA%’
AND c.kqfcooff > 0
ORDER BY offset ASC

For example, if you are using Oracle 9.2.0.5 you will see the following results

KSLLALAQ ,296 ,0,hex string ,8
KSLLAWAT ,320 ,0,hex string ,8
KSLLAWHY ,328 ,11,word,8
KSLLAWER ,336 ,11,word,4
KSLLALOW ,344 ,0,hex string ,4
KSLLASPN ,384 ,0,hex string ,8
KSLLAPRV ,544 ,11,word,4
KSLLAPSN ,548 ,11,word,4
KSLLAPSC ,552 ,11,word,4
KSLLAPRC ,556 ,11,word,4

Only ksllawat and ksllaspn are actually available via V$PROCESS; ksllawat represents latch waits and ksllaspn represents latch spins. Other fields can be used mostly for debugging purposes such as halt analysis, etc. For example, ksllalaq has pointer ksllt* to acquired latch set by KSLBEGIN, pointing to the head of linked list used in latch wait posting implementation (KSLBEGIN is a begin macro in the defined in the Oracle ksl.h code). Once a process acquires a latch at a certain level, it cannot acquire anymore latch at level that is equal or less than that level (unless it acquires nowait).

To support this issue, another nondocumented column ksllalow from X$KSUPR is used.
To see all the latches in the fixed SGA, the following query can be used:

select k.ksmfsadr , ksmfsnam, ksmfstyp, ksmfssiz, kslldnam, kslldlvl
from x$ksmfsv k , x$kslld a
where k .ksmfstyp like ‘%ksllt%’ and k.ksmfsadr = a.kslldadr
order by ksmfsnam

X$KSLLD : [K]ernel [S]ervice [L]atch [L]ock [D]escriptor
KSLLDLVL : [K]ernel [S]ervice [L]atch [L]ock [D]escriptor [L]atch Le[V]e[L]

eg:

SQL> select * from v$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
PL/SQL Release 11.2.0.3.0 – Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 – Production
NLSRTL Version 11.2.0.3.0 – Production

SQL> select distinct kslldlvl from x$kslld order by 1;

KSLLDLVL
———-
0
1
2
3
4
5
6
7
8
9
10

KSLLDLVL
———-
11
14
15
16

15 rows selected.

SQL> select * from v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bi
PL/SQL Release 10.2.0.5.0 – Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 – Production
NLSRTL Version 10.2.0.5.0 – Production

SQL> select distinct kslldlvl from x$kslld order by 1;

KSLLDLVL
———-
0
1
2
3
4
5
6
7
8
9
11

KSLLDLVL
———-
12
13

13 rows selected.

11g got more levels than 10g but Most latches have a level between 0 and 8

Let’s see how we can lock / unlock enqueues latch ksqeql_ via oradebug. This latch is used to protect operation on KSE (enqueue) related structures in memory. Assum e , value of k.ks mf sadr of this latch th at received from the previous query was 000000038000A0C0 (0x000000038000A0C0 = 15032426688)

In order to lock the latch, we can use function

word kslgetl(ksllt *latch_to_lock , word wait )
sqlplus “/ as sysdba”
oradebug setmypid
SQL> oradebug call kslgetl 15032426688 1

Function returned the value 1, meaning that we locked the latch.

Now let’s try to connect to Oracle. You can see that your session was halt because you are holding enqueue latch, therefore Oracle is unable to update even its own X$ table entries. Let’s return to oradebug and will release (free) the enqueue latch.

SQL> oradebug call kslfre 15032426688Function returned 8000A0C0

This time yours another session continued immediately.
Now let’s check the enqueu latch by queriynig v$latch:

select wait_time
from v$latch
where name = ‘enqueues’

The wait time returned is extremely big (in exam ple it was 619714031, e.g. 10 m i nutes).
List of all latch related tables as following:

GV$LATCH
GV$LATCH_CHILDREN
GV$LATCH_PARENT
GV$DLM_LATCH

*Note: list excludes x$kslld which is process internal structure and not represented in the SGA at
whole.

GV$DLM_LATCH is a special fixed table, used in the OPS/RAC environment only to monitor distributed lock manager latch. This fixed view exists only for historical reasons, since DLM did not used KSL ksllt and kslla latch related structures prior to version 8.1.5. DLM had own kjsllt and kjslla structures. DLM uses standard KSL structures from version 8.1.5 and up, therefore DLM latches can be monitored via V$LATCH fixed views.
GV$LATCHHOLDER is a process, e.g. X$KSUPR, fixed array of process structures,
GV$LATCH_MISSES is table which is non-directly points to the latch structures.

The next interesting question is where in the Oracle code each latch is used. It is possible to see the latch contention on some latch. However its name would be meaningless so at least you can identify where in oracle code (up to function name) either this latch has been locked. Such identification can be done, in normal case by running the following query:

select parent_name, location, nwfail_count, sleep_count from v$latch_misses;

where column location divided to 1 – 3 parts divided by ‘:’
1) Oracle kernel function.
2) Optional kslbegin (macro to lock latch) or operation name.
3) Optional description or comment if single function has several locks/unlocks for the same
latch.

For example, let’s look at the function “kcbgtcr” used to “get cache buffer for consistent read” or in other words it reads buffer from disk to buffer cache and then roll it back up to first SCN of the query, caused CR read.

In Oracle 9.2.0.5 this function has 4 different places where “cache buffers lru chain” latch to which the appropriate related block can be locked. Execute the following query:

SELECT t1 .ksllasnam “parent_name” , t2 .ksllwnam “location” ,
t2.ksllwlbl “unit to guard”
FROM x$ksllw t2, x$kslwsc t1
WHERE t2 .indx = t1 .indx
AND t2 .ksllwnam LIKE ‘kcbgtcr%’
AND ksllasnam = ‘cache buffers lru chain’

Result of query above will be something like:

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — –
parent_name location unit to guard
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — –
cache buffers lru chain kc bgtcr:CR Scan:KCBRSTOP buffer header
cache buffers lru chain kc bgtcr:CR Scan:KCBRSAVE buffer header
cache buffers lru chain kc bgtcr:CR Scan:KCBRSKIP buffer header
cache buffers lru chain kc bgtcr:CR Scan:best buffer header
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

*Note: In next topic about buffer cache and buffer cache related latches in the future discussion we will talk about CR blocks and related parameters.

PMON invokes a clean up function for releasing latches if the holding process dies.PMON will initiate the clean up function.

for 10g:

NAME VALUE DESCRIPTION
——————————————— ——————– ———————————————————————-
_max_exponential_sleep 0 max sleep during exponential backoff
_max_sleep_holding_latch 4 max time to sleep while holding a latch
_spin_count 2000 Amount to spin waiting for a latch

The sleep time on latches approximately doubles with each wait until upper limit is reached. This is an exponential backoff.

If Oracle instance is halt and you have some reason to think that is caused by latching problem, then you can use oradebug to dump latch statistics:

1. connect as sysdba
2. In order to dump latch states, perform the following operations:

? oradebug setmypid
? oradebug dump latches 2
? oradebug tracefile_name
Trace file will be generated with name, for example such as /oravl01/oracle/adm/bigsun/udump/bigsun_ora_21039.trc

Open this trace file to see the latch with high and constantly increasing between dumps sleeps count in the case of willing-to-wait latch and failed count in the case of no-wait latch.

For example, if you performed 2 dumps with insignificant interval between and have seen in the first dump for some child redo allocation latch sleeps count of 3. In the second dump sleeps count specific cache buffers lru chain latch is guard for specific part of buffer lru chain and block we are going to use has entry in this hash chain of 13 with “failed first” increased to the same number as sleeps count and “gotten” counts remains the same. All this means that some (and at least one) process is waited constantly for this latch all
the time between two dumps, e.g. another process hold this latch.

Example:

dump (1):

dump (2):

396670178 Child redo allocation level=5 child#=1
Location from where latch is held : kcrfwi : more space: strand #
Context saved from call : 0
state=free
gotten 7776125 times wait, failed first 365 sleeps 13
gotten 0 times nowait, failed : 0

Let’s try and emulate such “bug” example. To do that, we will need two sessions namely sess_1 and sess_2. Our child redo allocation latch address is 0x396670178 that can be converted into 15408234872. sess_1 will be connected as sysdba and sess_2 as oracle dba:

step 1. sess_1: oradebug setmypid
step 2. sess_1: oradebug dump latches 2
step 3. sess_1: oradebug call kslgetl 15408234872 1
step 4. sess_2: create table test1 as select * from dba_objects;
step 5. wait 1 minute
step 6. sess_1: oradebug call kslfre 15408234872
step 7. sess_1: oradebug dump latches 2

On the step 4 session sess_2 was halted. If you will try performing dump latches while some latch is locked, you will have a good chance to receive well known ORA-03113 (end-of-file on communication channel). However this is relates to latch and platform dependent.

Due to the error stacks it is looks like this is Oracle bug since latch state represented in the latch dump. However during all tests we have performed, we never seen in the dump files states other then “state=free”. It’s seems that Oracle waits several seconds until each latch will be freed and then dumps its state. If latch has not been freed during several seconds, ORA-03113 may occur…

396670178 Child redo allocation level=5 child#=1
Location from where latch is held : kcrfwi : more space: strand #
Context saved from call : 0
state=free
gotten 7784997 times wait, failed first 362 sleeps 10
gotten 0 times nowait, failed : 0

396670178 Child redo allocation level=5 child#=1
Location from where latch is held : kcrfwi: before write: strand #
Context saved from call : 0
state =free
gotten 7786065 times wait, failed first 367 sleeps 14
gotten 0 times nowait, failed: 0

In this case “gotten” increased significantly since to perform following statement:

create table test1 as select * from dba_objects;

more then 1000 times redo the space allocations has been performed.

In the general case, even if your instance is not halt but you want to see latch statistics in this format, you can dump latches statistics by running

ALTER SESSION SET EVENTS ‘immediate trace name latches level 2’;

Dump latches have two available levels:
? level 1 when dump just basic latch information (without statistics)
? level 2 when dump just latch information with statistics

and you can also user event 10005 before 9i

eg:

event = “10005 trace name context forever, level 1”