Scops
Docs Download

Oracle problems

latch: cache buffers chains: The Plan Behind It, and the Fix

3 November 2026

latch: cache buffers chains is the wait for the small lock that protects one hash chain of the buffer cache, the list Oracle walks to find whether a block is in memory. Every logical read takes the latch of the block's chain for a moment, so the wait appears when many sessions read the same few blocks at a very high rate. That is a plan far more often than a hardware problem: a nested loop that probes one hot index a million times a minute, a small lookup table joined to everything, a function that queries the same row for every row of a report. Oracle's reference describes the latch wait in general terms, the queries below name the latch, the block under it and the statement, and the fix is in the plan, not in the latch.

What the wait means

The reference's entry for a latch wait is short: "The process waits for a latch that is currently busy (held by another process)." The wait time "increases exponentially and does not include spinning on the latch (active waiting)", which is why the event also burns CPU: before a session sleeps on the latch it spins, and a database with a hot chain shows high CPU and this event together. The parameters are the latch address, the latch number that indexes V$LATCHNAME, and the number of tries. A cache buffers chains latch covers a set of hash chains, and each chain holds the buffers whose block addresses hash to it, so one hot block makes its whole chain hot, and the sessions that want any block on that chain queue behind the ones reading the hot one.

Which latch, which statement

SELECT s.sql_id, s.p1raw AS latch_addr, COUNT(*) AS sessions
FROM   v$session s
WHERE  s.event = 'latch: cache buffers chains'
GROUP  BY s.sql_id, s.p1raw
ORDER  BY sessions DESC;

One latch address and one sql_id at the top is the usual picture. The children of the latch, ranked by how often a session had to sleep on them, confirm it:

SELECT addr, gets, misses, sleeps, wait_time
FROM   v$latch_children
WHERE  name = 'cache buffers chains'
ORDER  BY sleeps DESC FETCH FIRST 10 ROWS ONLY;

A single child with sleeps far above the rest is a hot chain; sleeps spread evenly across all the children is the whole cache being hammered, which is a CPU and a workload question rather than a block. The blocks under the hot child are only visible to SYS, in X$BH, which is not a documented view but is where the mapping lives: the touch count tch ranks the buffers on the chain, and obj joins to the object.

SELECT o.owner, o.object_name, o.object_type,
       bh.dbarfil AS file#, bh.dbablk AS block#, bh.tch AS touches
FROM   x$bh bh
JOIN   dba_objects o ON o.data_object_id = bh.obj
WHERE  bh.hladdr = HEXTORAW('&latch_addr')
ORDER  BY bh.tch DESC FETCH FIRST 10 ROWS ONLY;

Without SYS, the statement is enough. Its plan says which object it visits and how:

SELECT id, operation, options, object_name, cardinality
FROM   v$sql_plan
WHERE  sql_id = '&sql_id' AND child_number = 0
ORDER  BY id;

SELECT sql_id, executions,
       ROUND(buffer_gets / NULLIF(executions, 0)) AS gets_per_exec,
       buffer_gets, ROUND(cpu_time / 1e6)         AS cpu_s
FROM   v$sql
ORDER  BY buffer_gets DESC FETCH FIRST 15 ROWS ONLY;

The first query shows the NESTED LOOPS with an INDEX RANGE SCAN or INDEX UNIQUE SCAN on the inner side, which is the operation that reads the same root and branch blocks of one index on every iteration. The second ranks the statements by logical reads since they were loaded; the one with gets per execution in the millions, or executions in the millions with a few gets each, is the one standing on the latch. V$SEGMENT_STATISTICS with the statistic logical reads ranks the objects the same way.

The fix, in order

  1. Change the plan that visits the block. A nested loop over a large driving set probes the inner index once per row; a hash join reads the inner table once. A lookup that runs per row inside a PL/SQL function runs once as a join. An index that covers the query's columns saves the table visit, which halves the gets. This is the fix that removes the wait rather than moving it.
  2. Stop asking the database the same question. A small reference table joined to every statement, or read on every request, belongs in the application's memory or in the result cache; a statement executed a million times a minute to read one row is a loop in the application that a single statement replaces.
  3. Spread a hot block that must be hot. When the block is a single table row everyone reads or an index root that every lookup starts from, the plan is already right and the block is simply popular. A higher PCTFREE on the table puts fewer hot rows in one block; hash partitioning the index gives the lookups several roots instead of one; the hot block page has the rest.
  4. Count the CPU. Sessions spinning on a latch are on CPU, and a host with as many active sessions as cores shows this event because the latch holder is not getting scheduled. The CPU page has the checks; the fix is the same statement, because it is both the spinner and the reason.
  5. Leave the latch parameters alone. The number of cache buffers chains latches and the spin count are internal settings sized by Oracle; changing them moves the queue without shortening it, and support will ask you to put them back.
  6. Keep the plan from coming back. A plan that flipped into the nested loop after new statistics returns with the next statistics; a SQL plan baseline pins the one that does not, and the plan change page has the steps.

See it coming

Radar Engine, the detection layer of Scops, keeps the figures of every statement every 10 minutes and the sessions every 5 seconds, so a statement whose gets per execution multiplied, or whose plan changed into a nested loop, is a finding before the latch is the top event:

DetectorClockWhen it fires
SQL on a new planLive, every 5 minutesA new plan: Warning. Three times slower, and it adds a full active session: Critical.
SQL taking most of the loadLive. Half or more of the active sessions.Above the usual of this hour: Warning. More sessions than CPUs: Critical. Always so at this hour: Watch.
CPU saturatedLive. Sessions on CPU or in the queue, against the usable CPUs.As many as the CPUs: Critical. Rising toward them: Warning, with an ETA.
Concurrency or Application waits surgingLive, against the hour beforeTwice the hour before, well above its usual spread: Watch. With a second sign, such as 5 blocked sessions: Warning.
Sudden wait eventLiveA sudden large share of the waits: Watch. The largest one, if it continues: Warning.

The finding names the statement, its gets per execution before and after, and its plan, so the fix starts at the right line. The Activity chart draws the Concurrency class and the CPU by statement, on any edition and without the Diagnostics Pack.

Questions

What is latch: cache buffers chains?
The wait for the latch that protects one hash chain of the buffer cache, taken for a moment on every logical read. It appears when many sessions read the same few blocks at a very high rate, which is usually one statement's plan: a nested loop over a hot index, or a lookup repeated per row.

Is latch: cache buffers chains a hardware problem?
Rarely. It is logical reads, not physical ones, so the storage is not involved. The CPU is: sessions spin on the latch before they sleep, so a saturated CPU makes the wait worse and the wait makes the CPU worse. The fix for both is the statement that does the reads.

How do I find the hot block?
V$SESSION gives the latch address in P1 for the waiting sessions and the SQL_ID they run; V$LATCH_CHILDREN ranks the children of the latch by sleeps; and as SYS, X$BH lists the buffers on that child's chain with their touch count and object. Without SYS, the statement's plan and its gets per execution name the object just as well.

Should I add more latches or raise the spin count?
No. The latch count and the spin count are internal settings that Oracle sizes; changing them does not reduce the reads, and the reads are the problem. A hash join instead of a nested loop, a covering index, or a lookup moved out of the per-row path removes the wait.

Related problems

Sources