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 本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1276759