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