Ask Maclean

Most of library cache latches have gone in 11g

10g

SELECT t1.ksllasnam "parent_name",
t2.ksllwnam "location"
FROM x$ksllw t2, x$kslwsc t1
WHERE t2.indx = t1.indx
AND ksllasnam like '%library cache%'

parent_name location
-------------------------------------------------- ----------------------------------------------------------------
library cache kqlftc
library cache kqlmbfre: parent
library cache kqlmbpil: parent
library cache kqlmbinv: parent
library cache kqlmbfre: child: in loop
library cache kqlmbfre: child: no obj to free
library cache pin kqlmbpil: child
library cache pin kqlmbpil: child: free and continue
library cache pin kqlmbp: child
library cache pin kqlmbpil: child: post parent after timeout
library cache kgluka: parent
library cache kglScanDependency
library cache kglscn: parent
library cache kglsca: parent
library cache kglLockCursor
library cache kgldrp: child
library cache kgldrx: child
library cache kgldmc: parent
library cache kglhdiv: child
library cache kglivl: parent
library cache kglidp: child
library cache kgldtld: parent
library cache kgldte: parent
library cache kglilf: parent
library cache kglmvsca: parent
library cache kgldte: child 0
library cache kglget: KGLDSBYA
library cache kglget: set reserved lock
library cache kglget: hash collision
library cache kglget: unpin heap 0
library cache kglpin: child: KGLMX
library cache kglupi: child: outside loop
library cache kglkep: child
library cache kglukp: child
library cache kglupc: child
library cache kglgfc: child
library cache kglalt: child
library cache kglupd: child
library cache kglsts: child
library cache kglpur: child
library cache kglprg: child
library cache kglini: child
library cache kglver: child
library cache kglivl: child
library cache kglivr: child
library cache kgldte: child
library cache kglhdbrnl: child
library cache kglobr: child
library cache kglpnc: child
library cache kglati
library cache kglpim
library cache kglupkp
library cache kgllfr
library cache kglnti
library cache kglic
library cache kglpnp: child
library cache kglhfr: child
library cache kglrtl
library cache kglpin
library cache kglswp
library cache kgldti: 2child
library cache kgldtld: 2child
library cache kgl 2child pad56
library cache kgllkal: parent
library cache kglhdal: parent
library cache kglhdunp: parent: flush
library cache kglhdiv0: parent: invalidate
library cache kglhdbr0: parent: invalidate
library cache kgllldl: child:
library cache kglhdbr: child
library cache kgl parent pad2132
library cache kglhdgn: child:
library cache kglhdgc: child:
library cache kglhdgh: child:
library cache kglobpn: child:
library cache kglpndl: child: before processing
library cache kglhdiv0: child
library cache kglpndl: child: after processing
library cache kglobld
library cache kglpin: child: heap processing
library cache kglpnal: 2child
library cache load lock kglllal: llatch
library cache lock kglget: un-set reserved lock
library cache lock kgllkal: child: multiinstance
library cache lock kgllkal: child: in loop
library cache lock kgllkal: child: deadlk det: error
library cache lock kgllkdl: free heap 0
library cache lock kgllkdl: child: cleanup
library cache lock kgllkdl: child: multiinstance
library cache lock kgllkdl: child: no lock handle
library cache lock kgllkc: child
library cache lock kglpsl: child
library cache lock kglhdbrnl: child
library cache lock kglhdcb: child
library cache lock ksucallcbksafely: kqllhng
library cache pin ksucallcbksafely: kqlphng
library cache load lock ksucallcbksafely: kqldlhn
library cache pin kglobld: child:
library cache pin kgllkdl: child: free pin
library cache pin kglpnal: child: alloc space
library cache pin kglpnal: child: check granted
library cache pin kglpndl: child: multiinstance
library cache pin kglpnck
library cache pin kglupc
library cache pin kglpin: child: KGLMS
library cache pin kglupi: child: within loop
library cache pin kgldnp: child
library cache pin kglpnc: child
library cache pin kglpnp: child
library cache pin kglobf0
library cache pin kglpndl
library cache pin dummy
library cache pin kglivl0
library cache hash chains kglpnrel
library cache hash chains kglpnget
library cache hash chains spare
library cache pin allocation kglpnrel
library cache pin allocation kglpnget
library cache pin allocation spare1
library cache pin allocation spare2
library cache lock allocation kgllkrel
library cache lock allocation kgllkget
library cache lock allocation spare1
library cache lock allocation spare2
library cache load lock kglllal: llatch: in loop
library cache load lock kgllldl: llatch
library cache kgl 2child pad2137
library cache kgl 2child pad2138
library cache kksftc

11g

SELECT t1.ksllasnam "parent_name",
t2.ksllwnam "location"
FROM x$ksllw t2, x$kslwsc t1
WHERE t2.indx = t1.indx
AND ksllasnam like '%library cache%'

parent_name location
--------------------------------------- ---------------------------------------------
library cache load lock kglllal: llatch
library cache load lock ksucallcbksafely: kqldlhn
library cache load lock kglllal: llatch: in loop
library cache load lock kglllal: llatch: in loop 2
library cache load lock kgllldl: llatch

latch:
library cache lock
library cache lock allocation
library cache pin
library cache pin allocation
library cache

ALL above latches have gone(latch library cache load lock left), library cache lock/pin don't need acquire library cache any more in 11g ;

library cache lock/pin structure now are protected by Library cache type mutex also.
But We can not find out how kglpin/kgllkdl and else function are utlized with mutex from any V$/X$ dynamic views.
Mutex is more black-box than Latch.

SQL> select mutex_type ,location from v$mutex_sleep_history group by mutex_type,location;

MUTEX_TYPE LOCATION
-------------------------------- ---------------------------------------------
Library Cache kglhdgn2 106
Library Cache kglget1 1
Cursor Pin kksfbc [KKSCHLCREA]
Cursor Parent kksfbc [KKSPRTLOC1]
Library Cache kglrfcl1 79
Library Cache kgllkal1 80
Library Cache kgllkdl1 85
Cursor Pin kkslce [KKSCHLPIN2]
Library Cache kglhdgh1 64
Library Cache kglpin1 4
Library Cache kglpndl1 95
Cursor Pin kksfbc [KKSCHLPIN1]
Library Cache kglhdgn1 62
Library Cache kglpur1 28
Library Cache kglpnal1 90
Library Cache kglobpn1 71
Library Cache kglpnal2 91

Posted

in

by

mac

Tags:

Comments

One response to “Most of library cache latches have gone in 11g”

  1. Maclean Avatar
    Maclean

    10g

    SQL> select * from v$mutex_sleep;

    MUTEX_TYPE LOCATION SLEEPS WAIT_TIME
    ——————————– —————————————- ———- ———-
    Cursor Stat kksIterCursorStat [KKSSTALOC6] 32 38
    Cursor Stat kksFindCursorStat [KKSSTALOC3] 15061 82994
    Cursor Parent kksfbc [KKSCHLCREA] 25 29
    Cursor Parent kkspsc0 [KKSPRTLOC27] 1 3
    Cursor Parent kkspsc0 [KKSPRTLOC26] 8474 35465
    Cursor Parent opiaba [KKSPRTLOC17] 5 7
    Cursor Parent kkshbbr [KKSPRTLOC15] 13 17
    Cursor Parent kksLoadChild [KKSPRTLOC4] 11 23
    Cursor Parent kksfbc [KKSPRTLOC2] 1932 9382
    Cursor Parent kksfbc [KKSPRTLOC1] 2517 5691
    Cursor Pin kksLockDelete [KKSCHLPIN6] 4445944 54029214
    Cursor Pin kkslce [KKSCHLPIN2] 4368258 2329806970
    Cursor Pin kksfbc [KKSCHLPIN1] 4332439 1293086591
    Cursor Pin kksfbc [KKSCHLFSP2] 12267731 1685511902

    SQL> select mutex_type,location from v$mutex_sleep_history group by mutex_type,location;

    MUTEX_TYPE LOCATION
    ——————————– —————————————-
    Cursor Pin kksfbc [KKSCHLPIN1]
    Cursor Pin kksLockDelete [KKSCHLPIN6]
    Cursor Parent kkspsc0 [KKSPRTLOC26]
    Cursor Pin kksfbc [KKSCHLFSP2]
    Cursor Pin kkslce [KKSCHLPIN2]
    Cursor Stat kksFindCursorStat [KKSSTALOC3]

    11g

    SQL> select * from v$mutex_sleep;

    MUTEX_TYPE LOCATION SLEEPS WAIT_TIME
    ——————————– —————————————- ———- ———-
    Library Cache kglllal3 111 2 6244
    Library Cache kglhdgn2 106 99 10566220
    Library Cache kglpnal2 91 1 5915
    Library Cache kglpnal1 90 2 35798
    Library Cache kgllkdl1 85 21 1478216
    Library Cache kgllkal1 80 1 22
    Library Cache kglhdgh1 64 2 2631
    Library Cache kglhdgn1 62 1 126806
    Library Cache kgllkc1 57 7 10075
    Library Cache kglati1 45 1 3890
    Library Cache kglrdtin1 44 2 506
    Library Cache kgldtin1 42 1 90
    Library Cache kglini1 32 1 238
    Library Cache kglpin1 4 1 19070
    Library Cache kglget2 2 2 53448
    Library Cache kglget1 1 1 4744
    Cursor Pin kkslce [KKSCHLPIN2] 511 5662439
    Cursor Pin kksfbc [KKSCHLFSP2] 1 11

    SQL> select mutex_type ,location from v$mutex_sleep_history group by mutex_type,location;

    MUTEX_TYPE LOCATION
    ——————————– —————————————-
    Cursor Pin kksfbc [KKSCHLFSP2]
    Library Cache kgllkc1 57
    Library Cache kglini1 32
    Library Cache kglhdgh1 64
    Library Cache kglhdgn1 62
    Library Cache kgldtin1 42
    Library Cache kglllal3 111
    Library Cache kglrdtin1 44
    Library Cache kglget2 2
    Cursor Pin kkslce [KKSCHLPIN2]
    Library Cache kgllkal1 80
    Library Cache kgllkdl1 85
    Library Cache kglpnal1 90
    Library Cache kglhdgn2 106
    Library Cache kglget1 1
    Library Cache kglpnal2 91
    Library Cache kglati1 45
    Library Cache kglpin1 4

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *