latch free 中 cache buffer chain 的整理

• 在data buffer中，所有的塊都在一個hash table 的 link list 中。每一個 hash chain 都由一個single child latch 保護著。一個latch必須要得到該latch 才能夠掃描該 hash chain 以保證該hash chain 上的blocks 不被修改
• 引發 cache buffer chain latch 的競爭的原因大概由如下原因：
  1. buffer chain 太長了： 太長的意義是指：lots of blocks all hashing to the same list (eg: having hundreds of consistent
  read versions of the same block and having hundreds of blocks on a list can add up) 增加 DB_BLOCK_LRU_LATCHES 引數
  2. 有havey access to the same block ： 調整SQL
• 如果我們執行以下查詢：select count(*) from v$latch_children where name = 'cache buffers chains'; 我們就能夠得到當前我們buffer cache中存在 hash chain 的個數。 這個數字要根據buffer cache 的大小而定，buffer越大，hash chain就越多，hash chain越多，關於這個latch 的競爭就越少
• db_block_lru_latches and cache buffers chains are different beasts. db_block_lru_latches is normally used in conjunction with multiple buffer pools or multiple dbwr's. cache buffers chains are latches to the individual, hashed lists of buffered blocks. they are separate. db_block_lru_latches 一般用在多個buffer pools 或者 多個DBWR 程式間的；
  在8i中，該值的預設值為CPU的一半，在9i中已經預設為CPU的2倍了，如果這個值過於小，就會發生該等待事件

主要診斷辦法：

1. 執行如下sql，檢視有多少session 正在處在cache buffer chain 等待：
   select a.sid,a.SEQ#,a.SECONDS_IN_WAIT,b.NAME,b.GETS,b.MISSES,b.SLEEPS
   from v$session_wait a ,v$latch b
   where a.EVENT='latch free' and a.p2=b.LATCH#
2. 執行如下sql，確定sleeps 次數最多的 session
   select CHILD# "cCHILD"
   , ADDR "sADDR"
   , GETS "sGETS"
   , MISSES "sMISSES"
   , SLEEPS "sSLEEPS"
   from v$latch_children
   where name = 'cache buffers chains'
   order by 5, 1, 2, 3;
3. 執行如下sql，得到引發 cache buffers chains 等待得熱塊：
   SELECT owner, segment_name,a.segment_type,a.file_id,b.dbablk,
   decode(b.state, 0, 'FREE', /* not currently is use */
   1, 'XCUR', /* held exclusive by this instance */
   2, 'SCUR', /* held shared by this instance */
   3, 'CR', /* only valid for consistent read */
   4, 'READ', /* is being read from disk */
   5, 'MREC', /* in media recovery mode */
   6, 'IREC') state,/* in instance(crash) recovery mode */
   decode (b.flag, 1, 'buffer dirty' ,
   2, 'about to modify; try not to start io' ,
   4, 'modification started, no new writes',
   8, 'block logged' ,
   16, 'temporary data - no redo for changes',
   32, 'being written; cant modify',
   64, 'waiting for write to finish',
   128, 'checkpoint asap',
   256, 'recovery reading, do not reuse, being read',
   512, 'unlink from lock element - make non-current',
   1024, 'write block & stop using for lock down grade',
   2048, 'write block for cross instance call',
   4096, 'reading from disk into KCBBHCR buffer',
   8192, 'has been gotten in current mode',
   16384, 'stale - unused CR buf made from current',
   32768, 'Direct Access to buffer contents',
   131072, 'Hash chain Dump used in debug print routine',
   524288, 'sequential scan only flag',
   1048576, 'Set to indicate a buffer that is NEW',
   2097152, 're-write if being written (sort)',
   4194304, 'buffer is "logically" flushed',flag) flag
   FROM DBA_EXTENTS a , x$bh b
   WHERE a.file_id = b.file# and b.hladdr in ('C000000005EB8468', 'C000000005F3A8C8', 'C000000005D0C148')
   AND b.dbablk between a.block_id AND a.block_id + a.blocks - 1;
   其中，括號中得地址為第二步產生得sAddr。這個是8i中的sql，從817開始，已經有了TCH一列，可以作為最熱塊的標記
4. 檢視第三步生產的結果
   If, you see unique block#s, then contention is at the hash bucket's chain level (a). If the same block# appears, then the block is receiving high-traffic (b).
   if a) --&gt increase _db_block_hash_buckets (i put it on 4096)
   else if b) --&gt increase freelists/initrans, etc (depending on the type of lock , insert, update, etc). Locally managed Tablespaces. View querys, etc
5. High waits on the cache buffers chains latch can happen if your db_block_lru_latches setting is too low. The default value is CPU_COUNT/2 in Oracle8i, but a good value to try is CPU_COUNT * 2. The db_block_lru_latches parameter is hidden in Oracle9i and defaults to _CPU_COUNT * 2.

ref: http://carson.yculblog.com/post.1252940.html

[@more@]