视图学习: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.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--
posted @ 2011-06-02 11:30  xxd  阅读(...)  评论(...编辑  收藏