SGA中Latch 的分类和查询--结合v$latch视图

SGA中Latch 的分类和查询

SGA中Latch分两类：
（1）愿意等的LATCH(willing-to_wait)
（2）不愿意等 immediate
CBC 多个BH组成 Cache Buffer Chain (CBC)

例如：CBC Latch的产生次数的查询:

SYS@ bys3>col name for a20
SYS@ bys3>select NAME,GETS ,MISSES ,SLEEPS ,IMMEDIATE_GETS, IMMEDIATE_MISSES from v$latch where name like '%cache buffers chains%';
NAME                       GETS     MISSES     SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES
-------------------- ---------- ---------- ---------- -------------- ----------------
cache buffers chains    2006932          0          0          78095                0

v$latch各字段描述：--来自官方文档
GETS          Number of times the latch was requested in willing-to-wait mode在愿意等待模式获取锁的次数
MISSES     Number of times the latch was requested in willing-to-wait mode and the requestor had to wait愿意等待模式，请求者请求LATCH不成功后等待的次数
SLEEPS         Number of times a willing-to-wait latch request resulted in a session sleeping while waiting for the latch在愿意等待模式导致一个会话SLEEP的次数
IMMEDIATE_GETS         Number of times a latch was requested in no-wait mode 立即获取latch成功的次数
IMMEDIATE_MISSES     Number of times a no-wait latch request did not succeed (that is, missed)立即获取latch不成功的次数

###################################

v$latch视图的查询

SYS@ bys3>select count(*) from v$latch;   --ORACLE版本为11.2.0.4，锁有582种
  COUNT(*)
----------
       582
SYS@ bys3>select count(*) from v$latch where IMMEDIATE_GETS+IMMEDIATE_MISSES>0;  --愿意等待willing-to-wait的latch
  COUNT(*)
----------
        35
SYS@ bys3>select count(*) from v$latch where IMMEDIATE_GETS+IMMEDIATE_MISSES=0;   ---不愿意等待immediate的latch
  COUNT(*)
----------
       547
################
关于willing-to-wait模式时请求者请求LATCH不成功后等待的次数涉及_spin_count参数：默认值2000，即SPIN2000次仍不能获取latch才sleep.
SYS@ bys3>@?/rdbms/admin/show_para  --脚本具体内容见：http://blog.csdn.net/haibusuanyun/article/details/17140197 第二小节。
Enter value for p: spin
old   3: WHERE i.inst_id = USERENV ('Instance') AND CV.inst_id = USERENV ('Instance') AND i.indx = CV.indx AND upper(i.ksppinm) LIKE upper('%&p%') ORDER BY REPLACE (i.ksppinm, '_', '')
new   3: WHERE i.inst_id = USERENV ('Instance') AND CV.inst_id = USERENV ('Instance') AND i.indx = CV.indx AND upper(i.ksppinm) LIKE upper('%spin%') ORDER BY REPLACE (i.ksppinm, '_', '')

P_NAME                   P_DESCRIPTION        P_VALUE  ISDEFAULT ISMODIFIED ISADJ
------------------ ------------------------ ---------- --------- ---------- -----
_lm_lms_spin            make lms not sleep     FALSE       TRUE      FALSE   FALSE
_mutex_spin_count     Mutex spin count          255        TRUE      FALSE   FALSE
_spin_count         Amount to spin waiting for a latch 1   TRUE      FALSE   FALSE
这里因为是虚拟机上，使用的单核心，所以_spin_count为1，双核时即为默认值：2000
show parameter cpu 可查看当前CPU情况。
#############

一个latch对应几个桶buckets呢？默认是32，我这里是16.计算如下：

SYS@ bys3>@?/rdbms/admin/show_para
Enter value for p: db_block_hash
old   3: WHERE i.inst_id = USERENV ('Instance') AND CV.inst_id = USERENV ('Instance') AND i.indx = CV.indx AND upper(i.ksppinm) LIKE upper('%&p%') ORDER BY REPLACE (i.ksppinm, '_', '')
new   3: WHERE i.inst_id = USERENV ('Instance') AND CV.inst_id = USERENV ('Instance') AND i.indx = CV.indx AND upper(i.ksppinm) LIKE upper('%db_block_hash%') ORDER BY REPLACE (i.ksppinm, '_', '')

P_NAME                                   P_DESCRIPTION                                P_VALUE                         ISDEFAULT ISMODIFIED ISADJ
---------------------------------------- -------------------------------------------------- ------------------------------ --------- ---------- -----
_db_block_hash_buckets                   Number of database block hash buckets        16384                           TRUE      FALSE      FALSE
_db_block_hash_latches                   Number of database block hash latches        1024                    TRUE      FALSE      FALSE
16384/1024=16
###################

一个latch的大小是：

select to_number(b.addr,'xxxxxxxxxxxxxxxxxxxx')-to_number(a.addr,'xxxxxxxxxxxxx')
 from
(select rownum rn,addr from v$latch_children
 where name='cache buffers chains' order by addr) a,
 (select rownum rn,addr from v$latch_children
 where name='cache buffers chains' order by addr) b
 WHERE a.rn=b.rn+1 and rownum<100;
 在ORACLE版本为11.2.0.4中为124byte.如果连续多个的latch，在查询中会被合并计算，显示为124的整数倍。