資料緩衝區熱鏈和熱塊爭用及解決方法

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.