Cache Buffers Chains

Find latches with high contention

select addr,gets,misses,sleeps 
from v$latch_children 
where name='cache buffers chains' 
and misses > 100 order by 3 desc;

ADDR                   GETS     MISSES     SLEEPS
---------------- ---------- ---------- ----------
000000015BF383D8   38773656       8016          6
000000015B6E0968   11215621       4215          0
000000015B5A27B8   26410038       3834          0
000000015B58B5D0   14755159       2289          6 

use the address to get the file id and block number

select file#,dbablk,state,class from x$bh where hladdr='000000015BF383D8';

     FILE#     DBABLK      STATE      CLASS
---------- ---------- ---------- ----------
         1      78757          1          1
        11     614483          1          4
         9     249625          1          1
        11     714956          1          1
         3      33047          1          1 

using the block number find out the object and the owner

select owner,segment_name 
from dba_extents 
where file_id=&p1 
and &p2 between 
block_id and block_id + blocks -1;

Enter value for p1: 11
Enter value for p2: 614483 

OWNER     SEGMENT_NAME
--------- ------------------
TRNSTG    STATIC_CACHE_QUEUE

Investigate the sqls accessing the objects for hot blocks


Metalink (Doc ID: 163424.1) provides following query for identifying hot blocks

column segment_name format a35
     select /*+ RULE */
       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
       x.hladdr  = &address and
       e.file_id = x.file# and
       x.hladdr = l.addr and
       x.dbablk between e.block_id and e.block_id + e.blocks -1
     order by x.tch desc ;

Metalink recommendations

Depending on the TCH column (The number of times the block is hit by a SQL
statement), you can identify a hotblock. The higher the value of the TCH column,
the more frequent the block is accessed by SQL statements.

In order to reduce contention for this object the following mechanisms can be put in place:

1) Examine the application to see if the execution of certain DML and SELECT statements
can be reorganized to eliminate contention on the object.

2) Decrease the buffer cache -although this may only help in a small amount of cases.

3) DBWR throughput may have a factor in this as well.
If using multiple DBWR's then increase the number of DBWR's.

4) Increase the PCTFREE for the table storage parameters via ALTER TABLE
or rebuild. This will result in less rows per block.

5) Consider implementing reverse key indexes
(if range scans aren't commonly used against the segment)