数据缓冲区热链和热块争用及解决方法-CSDN博客

CBC LATCH争用(热链)
latch:cache buffers chains

多个server_process同时以排他模式访问了同一个CBC latch管辖的资源时出现。这个资源：
可以是不同bucket中的不同buffer
可以是相同bucket中的不同buffer
可以是相同bucket中的相同buffer
解决方法:
1.唯一索引
2.加大buffer_cache内存,目的是将bucket数量增加,CBC latch数量增加
3.非内存不足情况,修改隐藏参数_db_block_hash_latches

buffer_pin争用(热块)
buffer busy waites
多个server_process同时改一个buffer
解决:
要区分是什么类型的数据块，根据不同热块采取不同的解决方法，具体如下：

Undo Header
If using Automatic Undo Management (AUM), increase the size of the undo tablespace.
If not using AUM, add more rollback segments.
Undo Block -
If using AUM, increase size of the undo tablespace.
If not using AUM, increase rollback segment sizes.

Data Block
Data blocks are the blocks that actually hold the row data in a table or index.
Problem: Multiple sessions are requesting a block that is either not in cache or in an incompatible mode.

Solution 1: 优化sql.比如NL选择小表做被驱动表造成
Tune inefficient queries. Inefficient queries read too many blocks into the buffer cache.
These queries could flush out blocks that may be useful for other sessions in the buffer cache.
By tuning queries, the number of blocks that need to be read into the cache is reduced,
reducing aging out of the existing “good” blocks in the cache.

Solution 2: 将热块内的行分散到其他块中.比如使用hint /*+ APPEND */,或者调整PCT-FREE
Delete some of the hot rows and insert them back into the table.
Most of the time, the rows will be place in a different block.
The DBA may need to adjust pctfree and/or pctused to ensure the rows are placed into a different block.

Solution 3: 使用KEEP池cache热块表.防止一个会话读入,其他会话等待.(10开始read by other session)
Cache the table or keep the table in the KEEP POOL.
When multiple sessions are requesting the blocks that reside in the disk,
it takes too much time for a session to read it into the buffer cache.
Other session(s) that need the same block will register ‘buffer busy wait’.
If the block is already in buffer cache, however, this possibility is eliminated.
Another alternative is to increase the buffer cache size.
A larger buffer cache means less I/O from disk.
This reduces situations where one session is reading a block from the disk subsystem
and other sessions are waiting for the block.

Solution 4: 在低基数列上并行操作,会导致索引块热点.避免索引建立在低基数列上.在选择性好的列上建立索引.
Look for ways to reduce the number of low cardinality indexes.
A low cardinality index is an index on a column(s) with a relatively low number of unique values such as a U. S.
state column that has only fifty unique values.
Low cardinality indexes could result in excessive block reads.
Concurrent DML operations on low cardinality columns could also cause contention on a few index blocks.

选择性的计算方法:count(distinct COL) / count(COL) * 100
如果列选择性低于30%,就不要在这个列上建立索引,可以组合索引非前导列.
SYS@ora10g> select count(distinct owner) / count(owner) * 100 owner_selectivity,
count(distinct object_name) / count(object_name) * 100 obj_name_selectivity,
count(distinct object_id)/count(object_id) * 100 obj_id_selectivity
from dba_objects;

OWNER_SELECTIVITY OBJ_NAME_SELECTIVITY OBJ_ID_SELECTIVITY
----------------- -------------------- ------------------
.047699493 59.7416277 100

Segment Header blocks

Solution 1: MSSM增加freelist数量和合理的PCT-FREE&PCT-USED,ASSM设置合理的PCT-FREE
When sessions insert rows into a block,
the block must be taken out of the freelist if the PCTFREE threshold reached.
When sessions delete rows from a block,
the block will be put back in the freelist if PCTUSED threshold is reached.
If there are a lot of blocks coming out of the freelist or going into it,
all those sessions have to make that update in the freelist map in the segment header.
This can cause contention for the segment header block which manifests itself as ‘buffer busy wait’.
One solution to this problem is to create multiple freelists.
This will allow different insert streams to use different freelists and thus update different freelist maps.
This reduces contention on the segment header block.
You should also look into optimizing the PCTUSED/PCTFREE parameters
so that the blocks don’t go in and out of the freelists frequently.

Solution 2: 增加extent.防止insert时现分配更新extent map的等待.
Increase the size of the extents.
If extents are too small, Oracle must constantly allocate new extents causing contention in the extent map

Solution 3: 不建议修改隐藏参数,高水位争用建议手动插入垃圾数据方式扩充高水位.
Increase the undocumented database parameter, _bump_highwater_mark_count,
from the default of 5. Updating the high water mark on the table can become a bottleneck.