cache buffers chains latch等待事件

Background

A user process acquires this latch to scan the SGA for database cache buffers. Blocks in the buffer cache are placed on linked lists (cache buffer chains). Blocks are put on the hash chain according to their DBA (data block adress) and CLASS of the block. Each hash chain is protected by a single child latch. The latch allows a process to scan a hash chain without having the linked list change while it scans.

Problem:

High or rapidly increasing wait counts on the CACHE BUFFERS CHAINS latch is an indication that the latch is not able to service the different sessions fast enough.

Contention for these latches can be caused by very heavy access to a single block or heavy concurrent execution of poorly written SQL statements which touch too many blocks (i.e. same blocks). This latch used to be big problem in Oracle7 and Oracle8i. In Oracle9i, operations on this latch have been optimized significantly.

Analysis:

Once a buffer chain contention problem has been identified , determine the object that the block belongs to.  First, determine which latch waits are potential problems by examining the number of sleeps for this latch. The higher the sleep count, the more likely that the latch wait is a problem.

The following will give a “sleep count” value.  This should be used in the next query:

select count(*)    “cCHILD”,
sum(GETS)   “sGETS”,
sum(MISSES) “sMISSES”,
sum(SLEEPS) “sSLEEPS”
from v$latch_children
where name = ‘cache buffers chains’
order by 4, 1, 2, 3;

The “sleep count” value (obtained from the sSLEEPS column of the query output) is used as the lower limit for the search for relevant block accesses. Any value of the sSLEEPS column can be used to run the second query. However, choose a relevant value from the top “n” resulting values.

select /*+ ordered */
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
l.name    = ‘cache buffers chains’ and
l.sleeps  > &sleep_count and
x.hladdr  = l.addr and
e.file_id = x.file# and
x.dbablk between e.block_id and e.block_id + e.blocks – 1;
Example of the output :

SEGMENT_NAME       EXTENT#       BLOCK#    TCH     CHILD#
SCOTT.EMP          1             449       2       7,668
SCOTT.EMP_PK       5             474       17      7,668

Depending on the TCH column (The number of times the block is hit by a SQL statement), one can identify a hot block. The higher the value of the TCH column, the more frequent the block is accessed by SQL statements. Be sure to run the query above multiple times to avoid being misled by a one time activity.

Solutions

Where there are hot blocks, contention may be relieved by:

• Splitting the buffer pool into multiple pools
• Altering PCTFREE/PCTUSED to allow fewer rows per block, thus reducing contention on a certain block
• Reducing the frequency the application accesses the object in question.
• In Oracle8i there are often far fewer “cache buffers chains” latches (especially with large buffer caches) and so there can be many buffers covered by a single hash latch. An upgrade might alleviate the problem.
• Tuning queries so that they won’t touch as many blocks. This will alleviate the problem with this latch if the query is heavily executed.
• Avoid doing too many concurrent DML and Queries against the same row/block. Too many concurrent DML and Queries against the same block can result in multiple versions of the block created in the same cache buffer chain. Longer chains means more time spent by the session traversing through the chain while holding on to the latch.