Cache buffers LRU Chain
Reason
High load cache throughput, inefficient SQL statements (full table scan, or incorrect index range scans)
DBWR write speed is too slow, the foreground process spends a lot of time holding latch to find free buffer.
The cache buffers LRU chain protects the list of buffer in the cache, and when added, removed, moves a buffer from the list, the CBLC latch must be fetched.
On SMP (symmetric multi-processing) systems, Oracle automatically sets the number of LRU Latche to be half of the CPU count.
In a system with many processors, contention for CBLC LATCH affects performance, and LRU LATCH contention can be identified by querying V$latch, V$session_event, v$system_event, and other views.
To avoid contention, consider optimizing the application, bypassing the buffer cache for BATCH,DSS tasks, or redesign the app.
Cache buffers Chains
CBC latch is used to protect the buffer chain list in buffer cache,
When searching in a linked list, adding, removing buffer requires CBC latch.
The contention for CBC latch is usually due to a buffer being used frequently.
If a particular sub-latch is compared with other sub-latch, there are many gets,misses, sleeps.
Gets the memory address of the LATCH, via V$latch_children. Addr is associated with X$BH and can find specific hot blocks.
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 contact counter, tch High indicates that this is a hot block.
A latch will protect a lot of blocks, one of the blocks in the middle may be a hot block, and a high tch value block will be a potential hot block. Execute the following query multiple times
Identify multiple occurrences of the block, query Dba_extent get the block where the segment.
SELECT object_name, Subobject_name
From Dba_objects
WHERE data_object_id = &obj;
Cache buffer Related latch wait events (cache buffers LRU Chain/cache buffers chain)