10gR2中关于Latch的视图：有5个除了v$Latch之外还有以下4个,他们都可以和v$Latch视图连接，连接字段如下：
v$Latch_CHILDREN.NAME/LATCH#
v$LatchHOLDER.NAME
v$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(*)
----------
382
SQL> 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 whe
re 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
65537
SQL> 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, l
a.gets,la.misses, la.sleeps,la.immediate_gets,la.immediate_misses,la.wait
ers_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, su
m(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(k
sllthst4) sleep4,sum(ksllthst5) sleep5, sum(ksllthst6) sleep6,sum(ksllths
t7) sleep7,sum(ksllthst8) sleep8, sum(ksllthst9) sleep9,sum(ksllthst10) s
leep10,sum(ksllthst11) sleep11, sum(kslltwtt) wait_time from x$ksllt g
roup by kslltnum) la where la.latch# = d.indx
X$KSLLT [K]ernel [S]ervice [L]ock Management Latch statistics + Child latches
SQL> select * from V$FIXED_TABLE where name like 'GV$LATCH_CHILDREN';
NAME OBJECT_ID TYPE TABLE_NUM
------------------------------ ---------- ----- ----------
GV$LATCH_CHILDREN 4294951269
VIEW
65537
SQL> 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(*)
----------
1925
SQL> 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 = US
ERENV('Instance')
SQL> select * from V$FIXED_TABLE where name like 'V$LATCH_CHILDREN';
NAME OBJECT_ID TYPE TABLE_NUM
------------------------------ ---------- ----- ----------
V$LATCH_CHILDREN 4294951168 VIEW 65537
SQL> 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.ks
lldhsh, t.kslltwgt,t.kslltwff,t.kslltwsl,t.kslltngt,t.kslltnfa, t.
kslltwkc,t.kslltwth,t.ksllthst0,t.ksllthst1, t.ksllthst2,t.ksllthst3,t.ks
llthst4,t.ksllthst5, t.ksllthst6,t.ksllthst7,t.ksllthst8, t.ksllth
st9,t.ksllthst10, t.ksllthst11, t.kslltwtt from x$ksllt t, x$kslld n wh
ere t.kslltcnm > 0 and t.kslltnum = n.indx
X$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 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
SQL>
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 0
library cache 5 2218356 116 80 933 0
library cache 5 3731320 191 626 1476 76766
library cache lock 6 5339737 362 3 0 0
library cache lock 6 6223353 194 3 0 0
library cache lock 6 1987799 26 0 0 0
library cache pin 6 1484918 184 0 0 0
library cache pin 6 891695 3 0 2 0
library cache pin 6 1831837 6 0 0 0
library cache pin allocation 3 23177 0 0 0 0
library cache pin allocation 3 8272 0 0 0 0
library cache pin allocation 3 25849 0 0 0 0
library cache lock allocation 3 75900 0 0 0 0
library cache lock allocation 3 28229 0 0 0 0
library cache lock allocation 3 104237 0 0 0 0
library cache hash chains 9 0 0 0 0 0
library cache hash chains 9 0 0 0 0 0
library cache hash chains 9 0 0 0 0 0
--EOF--
转载于:https://www.cnblogs.com/buro79xxd/archive/2011/06/02/2068289.html
