cache buffers chains
The cache
buffers
chains
latches are used to protect a buffer list in the buffer cache. These latches are used when searching for, adding, or removing a buffer from the buffer cache. Contention on this latch usually means that there is a block that is greatly contended for (known as a hot block).
To identify the heavily accessed buffer chain, and hence the contended for block, look at latch statistics for the cache
buffers
chains
latches using the view V$LATCH_CHILDREN
. If there is a specific cache
buffers
chains
child latch that has many more GETS
, MISSES
, and SLEEPS
when compared with the other child latches, then this is the contended for child latch.
This latch has a memory address, identified by the ADDR
column. Use the value in the ADDR
column joined with the X$BH
table to identify the blocks protected by this latch. For example, given the address (V$LATCH_CHILDREN.ADDR
) of a heavily contended latch, this queries the file and block numbers:
SELECT OBJ data_object_id, FILE#, DBABLK,CLASS, STATE, TCH FROM X$BH WHERE HLADDR = 'address of latch' ORDER BY TCH;
X$BH.TCH
is a touch count for the buffer. A high value for X$BH.TCH indicates a hot block.
Many blocks are protected by each latch. One of these buffers will probably be the hot block. Any block with a high TCH
value is a potential hot block. Perform this query a number of times, and identify the block that consistently appears in the output. After you have identified the hot block, query DBA_EXTENTS
using the file number and block number, to identify the segment.
After you have identified the hot block, you can identify the segment it belongs to with the following query:
SELECT OBJECT_NAME, SUBOBJECT_NAME FROM DBA_OBJECTS WHERE DATA_OBJECT_ID = &obj;
In the query, &obj
is the value of the OBJ
column in the previous query on X$BH
.