more interesting the latch id(ADDR) is: SQL> select CHILD# "cCHILD" , ADDR "sADDR" , GETS "sGETS" , MISSES "sMISSES" , SLEEPS "sSLEEPS" from v$latch_children where name = 'cache buffers chains' order by 5, 1, 2, 3; Run the above query a few times to to establish the id(ADDR) that has the most consistent amount of sleeps. Once the id(ADDR) with the highest sleep count is found then this latch address can be used to get more details about the blocks currently in the buffer cache protected by this latch. The query below should be run just after determining the ADDR with the highest sleep count. SQL> 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 = '&ADDR' 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 ; Example of the output : SEGMENT_NAME EXTENT# BLOCK# TCH CHILD# -------------------------------- ------------ ------------ ------ ---------- SCOTT.EMP_PK 5 474 17 7,668 SCOTT.EMP 1 449 2 7,668 Depending on the TCH column (The number of times the block is hit by a SQL statement), you can identify a hot block. The higher the value of the TCH column, the more frequent the block is accessed by SQL statements.
5) Consider implementing reverse key indexes (if range scans aren’t commonly used against the segment)