视图学习：v$Latch (X$KSLLT) 与 v$Latch_Children(X$KSLLT, X$KSLLD)
10gR2中关于Latch的视图：有5个除了v$Latch之外还有以下4个,他们都可以和v$Latch视图连接，连接字段如下：
v$Latch_CHILDREN.NAME/LATCH#v$LatchHOLDER.NAMEv$LatchNAME.NAME/LATCH#v$Latch_MISSES.PARENT_NAME
什么是Latch
latch有40余种，但作为DBA关心的主要应有以下几种：
Cache buffers chains latch：当用户进程搜索SGA寻找database cache buffers时需要使用此latch。见《Buffer Cache Management》Cache buffers LRU chain latch：当用户进程要搜索buffer cache中包括所有 dirty blocks的LRU (least recently used) 链时使用该种latch。见《Buffer Cache Management》Redo log buffer latch：这种latch控制redo log buffer中每条redo entries的空间分配。稍后Row cache objects latch：当用户进程访问缓存的数据字典数值时，将使用Row cache objects latch。稍后
首先来看看v$latch，在10gR2下统计有 382种latch，V$LATCH视图在选取X$KSLLT记录时，进行了Group By及SUM运算，从而得出了一个汇总信息。本视图保存自实例启动各类栓锁的统计信息。常用于当v$session_wait中发现栓锁竞争时鉴别SGA区中问题所在区域。
SQL> select count(*) from v$LATCH;COUNT(*)----------382SQL> select VIEW_DEFINITION from V$FIXED_VIEW_DEFINITION where VIEW_NAME like 'V$LATCH';VIEW_DEFINITION--------------------------------------------------------------------------------select addr,latch#,level#,name,hash,gets,misses,sleeps,immediate_gets, immediate_misses,waiters_woken,waits_holding_latch,spin_gets, sleep1,sleep2,sleep3,sleep4,sleep5,sleep6,sleep7,sleep8,sleep9, sleep10,sleep11,wait_time from gv$latch where inst_id = USERENV('Instance')SQL> select * from V$FIXED_TABLE where name like 'V$LATCH_CHILDREN';NAME OBJECT_ID TYPE TABLE_NUM------------------------------ ---------- ----- ----------V$LATCH 4294950921 VIEW 65537SQL> select VIEW_DEFINITION from V$FIXED_VIEW_DEFINITION where VIEW_NAME like 'GV$LATCH';VIEW_DEFINITION--------------------------------------------------------------------------------select d.inst_id,d.kslldadr,la.latch#,d.kslldlvl,d.kslldnam,d.kslldhsh, la.gets,la.misses, la.sleeps,la.immediate_gets,la.immediate_misses,la.waiters_woken, la.waits_holding_latch,la.spin_gets,la.sleep1,la.sleep2,la.sleep3,la.sleep4,la.sleep5,la.sleep6,la.sleep7,la.sleep8,la.sleep9,la.sleep10, la.sleep11, la.wait_time from x$kslld d, (select kslltnum latch#, sum(kslltwgt) gets,sum(kslltwff) misses,sum(kslltwsl) sleeps, sum(kslltngt) immediate_gets,sum(kslltnfa) immediate_misses, sum(kslltwkc)waiters_woken,sum(kslltwth) waits_holding_latch, sum(ksllthst0) spin_gets,sum(ksllthst1) sleep1,sum(ksllthst2) sleep2, sum(ksllthst3) sleep3,sum(ksllthst4) sleep4,sum(ksllthst5) sleep5, sum(ksllthst6) sleep6,sum(ksllthst7) sleep7,sum(ksllthst8) sleep8, sum(ksllthst9) sleep9,sum(ksllthst10) sleep10,sum(ksllthst11) sleep11, sum(kslltwtt) wait_time from x$ksllt group by kslltnum) la where la.latch# = d.indxX$KSLLT [K]ernel [S]ervice [L]ock Management Latch statistics + Child latchesSQL> select * from V$FIXED_TABLE where name like 'GV$LATCH_CHILDREN';NAME OBJECT_ID TYPE TABLE_NUM------------------------------ ---------- ----- ----------GV$LATCH_CHILDREN 4294951269 VIEW 65537SQL> select VIEW_DEFINITION from V$FIXED_VIEW_DEFINITION where VIEW_NAME like 'X$KSLLT';no rows selected 看来X$KSLLT已经是最低层的表而不是视图了SQL> select * from V$FIXED_TABLE where name like 'X$KSLLT';NAME OBJECT_ID TYPE TABLE_NUM------------------------------ ---------- ----- ----------X$KSLLT 4294950993 TABLE 6
V$LATCH中的常用列：拿《library cache lock和library cache pin到底是什么》里边的这条语句来解释V$LATCH视图SQL> select name,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch where name like 'library%';
NAME LEVEL# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
-------------------------------------------------- ---------- ---------- ---------- ---------- -------------- ----------------
library cache 5 9221760 1608 800 2596 76766
library cache lock 6 13548247 582 6 0 0
library cache lock allocation 3 208273 0 0 0 0
library cache pin 6 4207462 193 0 2 0
library cache hash chains 9 0 0 0 0 0
library cache pin allocation 3 57276 0 0 0 0
library cache load lock 5 24848 0 0 1 0
NAME：latch名称IMMEDIATE_GETS：以Immediate模式latch请求数IMMEDIATE_MISSES：请求失败数GETS：以Willing to wait请求模式latch的请求数MISSES：初次尝试请求不成功次数SLEEPS：成功获取前sleeping次数还有两个重点的没有出现在这里SPIN_GETS：第一次尝试失败，但在以后的轮次中成功WAIT_TIME：花费在等待latch的时间
再来看看V$LATCH_CHILDREN，V$LATCH_CHILDREN视图和v$Latch视图的差别与v$sql和v$sqlarea的区别相似并不进行汇总运算，所以也就显示了子Latch的相关信息
SQL> select count(*) from V$LATCH_CHILDREN;COUNT(*)----------1925SQL> select VIEW_DEFINITION from V$FIXED_VIEW_DEFINITION where VIEW_NAME like 'V$LATCH_CHILDREN';VIEW_DEFINITION--------------------------------------------------------------------------------select ADDR , LATCH# , CHILD# , LEVEL# , NAME , HASH , GETS , MISSES , SLEEPS, IMMEDIATE_GETS , IMMEDIATE_MISSES , WAITERS_WOKEN , WAITS_HOLDING_LATCH , SPIN_GETS , SLEEP1 , SLEEP2 , SLEEP3 , SLEEP4 , SLEEP5 , SLEEP6 , SLEEP7 , SLEEP8 ,SLEEP9 , SLEEP10 , SLEEP11 , WAIT_TIME from GV$LATCH_CHILDREN where inst_id = USERENV('Instance')SQL> select * from V$FIXED_TABLE where name like 'V$LATCH_CHILDREN';NAME OBJECT_ID TYPE TABLE_NUM------------------------------ ---------- ----- ----------V$LATCH_CHILDREN 4294951168 VIEW 65537SQL> select VIEW_DEFINITION from V$FIXED_VIEW_DEFINITION where VIEW_NAME like 'GV$LATCH_CHILDREN';VIEW_DEFINITION--------------------------------------------------------------------------------select t.inst_id,t.addr,t.kslltnum,t.kslltcnm,n.kslldlvl, n.kslldnam,n.kslldhsh, t.kslltwgt,t.kslltwff,t.kslltwsl,t.kslltngt,t.kslltnfa, t.kslltwkc,t.kslltwth,t.ksllthst0,t.ksllthst1, t.ksllthst2,t.ksllthst3,t.ksllthst4,t.ksllthst5, t.ksllthst6,t.ksllthst7,t.ksllthst8, t.ksllthst9,t.ksllthst10, t.ksllthst11, t.kslltwtt from x$ksllt t, x$kslld n where t.kslltcnm > 0 and t.kslltnum = n.indxX$KSLLD [K]ernel [S]ervice [L]ock Management Latch [D]escriptor (name)SQL> select * from V$FIXED_TABLE where name like 'GV$LATCH_CHILDREN';NAME OBJECT_ID TYPE TABLE_NUM------------------------------ ---------- ----- ----------GV$LATCH_CHILDREN 4294951269 VIEW 65537
对比v$latch与v$latch_children视图
重复一下上边说的：V$LATCH视图在选取X$KSLLT记录时，进行了Group By及SUM运算，从而得出了一个汇总信息V$LATCH_CHILDREN视图和v$Latch视图的差别与v$sql和v$sqlarea的区别相似并不进行汇总运算，所以也就显示了子Latch的相关信息所谓子Latch就是library cache在系统中包含了多少个具体的物理数量的library cache，他们的分配原则是这样的v$latch_children内容的条数一般都是v$latch cpu+1倍，例如：下边这个v$latch的相同查询，都是查询有多少相关的library cache latch。这里cpu的个数为2，显然上述library cache latches的子latch应该为3：SQL> select name,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch where name like 'library%';NAME LEVEL# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES-------------------------------------------------- ---------- ---------- ---------- ---------- -------------- ----------------library cache 5 9221760 1608 800 2596 76766library cache lock 6 13548247 582 6 0 0library cache lock allocation 3 208273 0 0 0 0library cache pin 6 4207462 193 0 2 0library cache hash chains 9 0 0 0 0 0library cache pin allocation 3 57276 0 0 0 0library cache load lock 5 24848 0 0 1 0SQL> select name,level#,gets,misses,sleeps,immediate_gets,immediate_misses from v$latch_children where name like 'library%';NAME LEVEL# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES-------------------------------------------------- ---------- ---------- ---------- ---------- -------------- ----------------library cache 5 3274551 1301 94 187 0library cache 5 2218356 116 80 933 0library cache 5 3731320 191 626 1476 76766library cache lock 6 5339737 362 3 0 0library cache lock 6 6223353 194 3 0 0library cache lock 6 1987799 26 0 0 0library cache pin 6 1484918 184 0 0 0library cache pin 6 891695 3 0 2 0library cache pin 6 1831837 6 0 0 0library cache pin allocation 3 23177 0 0 0 0library cache pin allocation 3 8272 0 0 0 0library cache pin allocation 3 25849 0 0 0 0library cache lock allocation 3 75900 0 0 0 0library cache lock allocation 3 28229 0 0 0 0library cache lock allocation 3 104237 0 0 0 0library cache hash chains 9 0 0 0 0 0library cache hash chains 9 0 0 0 0 0library cache hash chains 9 0 0 0 0 0
--EOF--
作者：Buro#79xxd
出处：http://www.cnblogs.com/buro79xxd/
文章版权归本人所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
