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的整數倍。