Latch: cache buffers chains
Blocks in the buffer cache are placed on linked lists
(cache buffer chains) which hang off a hash table.
The hash chain that a block is placed on is based on the DBA
and CLASS of the block. Each hash chain is protected by a
single child latch. Processes need to get the relevant latch
to allow them the scan a hash chain for a buffer so that the
linked list does not change underneath them.
Contention: Contention for these latches can be caused by:
– Very long buffer chains.
There is a known problem that can result in long
buffer chains –
– very very heavy access to a single block.
This would require the application to be reviewed.
To identify the heavily accessed buffer chain look at
the latch stats for this latch under
and match this to .
*** IMPORTANT: As of Oracle8i there are many hash buckets
to each latch and so there will be lots
of buffers under each latch.
In 8i the steps below will not help much.
Eg: Given ADDR from V$LATCH_CHILDREN for a heavily contended
select dbafil, dbablk, class, state
from X$BH where HLADDR=’address of latch’;
One of these is ‘potentially’ a hot block in the database.
**Please see Note 163424.1 How To Identify a Hot Block Within The Database
to correctly identify this issue
Once the object/table is found you can reduce the number of blocks requested
on the particular object/table by redesigning the application or by
spreading the hits in the buffer cache over different hash chains.
You can achieve this by implementing PARTITIONING and storing segements of
the same table/object in different files.
*NOTE* IF YOU ARE RUNNING 8.1.7:
Please see Note 176129.1 ALERT: LATCH FREE And FREE_BUFFER_WAITS
Cause Performance Degradation/Hang
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;
column segment_name format a35 select /*+ RULE */ e.owner ||'.'|| e.segment_name segment_name, e.extent_id extent#, x.dbablk - e.block_id + 1 block#, x.tch, l.child# from sys.v$latch_children l, sys.x$bh x, sys.dba_extents e where x.hladdr = 'ADDR' and e.file_id = x.file# and x.hladdr = l.addr and x.dbablk between e.block_id and e.block_id + e.blocks -1 order by x.tch desc ;
SELECT FILE# , dbablk, class, state ,tch FROM x$bh WHERE hladdr='' order by tch;
select * from dba_extents where file_id= and between block_id and block_id + blocks -1
If you have high contention, you need to look at the statements that perform the most buffer gets and then look at their access paths to determine whether these are performing as efficiently as you would like.
Typical solutions are:-
- Look for SQL that accesses the blocks in question and determine if the repeated reads are necessary. This may be within a single session or across multiple sessions.
- Check for suboptimal SQL (this is the most common cause of the events) – look at the execution plan for the SQL being run and try to reduce the gets per executions which will minimize the number of blocks being accessed and therefore reduce the chances of multiple sessions contending for the same block.
Further information can be found in:
Note:163424.1 How To Identify a Hot Block Within The Database Buffer Cache.
Note:62172.1 Understanding and Tuning Buffer Cache and DBWR (Doc ID 62172.1)
Problem: Database is slow and ‘latch: cache buffers chains’ is high in the waits in AWR.
Start with Top 5 Waits:
High cache buffers chains latch indicates that there is likely to be something reading a lot of buffers. Typically the SQL with the most gets is likely to be that which is contending:
The Query with SQL_ID a9nchgksux6x2 is reading 100x more buffers than the 2nd most ‘hungry’ statement and CPU and Elapsed are off the ‘scale’ of the report. This is a prime candidate for the cause of the CBC latch issues.
You can also link this information to the Top Segments by Logical Reads:
The top object read is SALES and the top SQL is a select from SALES which appears to correlate towards this being a potential problem select.
This SQL should be investigated to see if the Gets per Exec or the Executions figure per hour has changed in any way (comparison to previous reports would show this) and if so the reasons for that change investigated and resolved.
In this case the statement is reading > 10,000 buffers per execution and executing > 15,000 times
so both of these may need to be adjusted to get better performance.
So, if the first SQL is not the culprit then look at the others.