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)

Lbrary cache latch contention

Lbrary cache latch contention is typically caused by NOT using bind variables. It is due
to excessive parsing of statements.

One way to see if this might be the case in your situation is to run a script like:


create table t1 as select sql_text from v$sqlarea;

alter table t1 add sql_text_wo_constants varchar2(1000);

create or replace function
remove_constants( p_query in varchar2 ) return varchar2
as
l_query long;
l_char varchar2(1);
l_in_quotes boolean default FALSE;
begin
for i in 1 .. length( p_query )
loop
l_char := substr(p_query,i,1);
if ( l_char = '''' and l_in_quotes )
then
l_in_quotes := FALSE;
elsif ( l_char = '''' and NOT l_in_quotes )
then
l_in_quotes := TRUE;
l_query := l_query || '''#';
end if;
if ( NOT l_in_quotes ) then
l_query := l_query || l_char;
end if;
end loop;
l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
for i in 0 .. 8 loop
l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
end loop;
return upper(l_query);
end;
/
update t1 set sql_text_wo_constants = remove_constants(sql_text);

select sql_text_wo_constants, count(*)
from t1
group by sql_text_wo_constants
having count(*) > 100
order by 2
/



The output of that last query will show you statements that are identical in the shared
pool after all numbers and character string constants have been removed. These
statements -- and more importantly their counts -- are the potential bottlenecks. In
addition to causing the contention, they will be HUGE cpu consumers.