How to solve Cache buffers Chain Latch Contention :
-----------------------------------------------------------------
Since the Oracle SGA holds lots of information in various types of memory structures, it is not enough to have just one latch for each type of memory structure to be protected.
For example, if you have an 8 GB buffer cache for 8 KB blocks, then you have one million buffers in the buffer cache.
Reasonable concurrent access to all these million buffers can't be provided by a single latch (in this case a "cache buffers chains" latch).
Therefore, Oracle has split the memory structures that need protection into logical sub-pieces and each piece is protected by a separate sub-latch known as a child latch
For example, if you run a database with large buffer cache, you may have thousands of cache buffers chains child latches in use.
When accessing a data buffer block, Oracle will run a modulus function on the data block address (DBA) of the block
to determine which cache buffers chains latch to get to satisfy that access
Cache buffers chains (CBC) latch contention can occur due a number of reasons. One of the most common reasons is that you have an extremely hot block in the buffer cache
1) First, there are many CBC latches in an Oracle instance
SQL> select name, count(*) from v$latch_children where name like '%chains%' group by name;
NAME COUNT(*)
-------------------------------------------------- ----------
cache buffers chains 2048
enqueue hash chains 8
library cache hash chains 11
2) So, as a first step we should identify which CBC latches are experiencing the contention:
i.e. is the contention lightly spread across many CBC latch children or is there a single child latch experiencing much heavier contention compared to others?
V$LATCH_CHILDREN.WAIT_TIME --> find the latch children that has highest number of wait time
ex: in a sql trace we found following:
WAIT #1: nam='latch: cache buffers chains' ela= 204 address=15742234376 ...
WAIT #1: nam='latch: cache buffers chains' ela= 8 address=15742234376 ...
WAIT #1: nam='latch: cache buffers chains' ela= 2 address=15742234376 ...
WAIT #1: nam='latch: cache buffers chains' ela= 7 address=15742234376 ...
From address: (sql trace shows address and we need to change it to hexadecimal)
SQL> select name, gets, misses, sleeps from v$latch_children where addr = hextoraw(trim(to_char(15742234376, '0XXXXXXXXXXXXXXX')));
NAME GETS MISSES SLEEPS
------------------------- ---------- ---------- ----------
cache buffers chains 62178598 125541 805
from ASH:
SQL> SELECT *
FROM
( SELECT
event,
TRIM(TO_CHAR(p1, 'XXXXXXXXXXXXXXXX')) latch_addr,
TRIM(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100, 1))||'%' PCT,
COUNT(*)
FROM
v$active_session_history
WHERE
event = 'latch: cache buffers chains'
AND session_state = 'WAITING'
GROUP BY
event,
p1
ORDER BY
COUNT(*) DESC
) WHERE ROWNUM <= 10;
EVENT LATCH_ADDR PCT COUNT(*)
----------------------------- ----------------- ------ ----------
latch: cache buffers chains 3AA4F6F08 4.1% 50
latch: cache buffers chains 3AA4D4908 3.7% 45
latch: cache buffers chains 3AA4E2558 3.4% 42
latch: cache buffers chains 3AA4F00E0 3.2% 39
latch: cache buffers chains 3AA519440 2.9% 36
latch: cache buffers chains 3AA527090 2.8% 34
latch: cache buffers chains 3AA4FDD30 2.8% 34
latch: cache buffers chains 3AA50B8B8 2.7% 33
latch: cache buffers chains 3AA4DB730 2.6% 32
latch: cache buffers chains 3AA534C18 2.6% 32
10 rows selected.
3) find the sql:
After getting the latch_children address we can find the session and corresponding sql.
SQL> desc v$latchholder;
Name Null? Type
----------------------------------------- -------- ------
PID NUMBER
SID NUMBER
LADDR RAW(8)
NAME VARCHAR2(64)
GETS NUMBER
