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)
