VMCD.ORG

Focus on database architecture

Oracle latch internals

Posted by admin on June 6th, 2012

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
 
Copyright (c) 1982, 2011, OracleAll rights reserved.
 
 
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):



 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 355 sleeps 3
   gotten 0 times nowait, failed : 0
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″

4 Responses to “Oracle latch internals”

  1. NICK Says:

    [oracle@testdb ~]$ ora si
    版权所有啊

  2. admin Says:

    who’re you ?

  3. NICK Says:

    Q号多少?

  4. admin Says:

    9642934

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>