latch free 中 cache buffer chain 的整理

最新推荐文章于 2024-01-01 22:10:08 发布

cl2689163935

最新推荐文章于 2024-01-01 22:10:08 发布

阅读量135

点赞数

在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倍了，如果这个值过于小，就会发生该等待事件

主要诊断办法：

运行如下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#
运行如下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;
运行如下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一列，可以作为最热块的标记
查看第三步生产的结果
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
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.