cache buffers chains

If "cache buffer chains" lacth is intensive then

1-Check if "cache buffer chains" exists
--If it is top in top latches


select substr(name,1,40),gets,misses*100/decode(gets,0,1,gets) misses,
spin_gets*100/decode(misses,0,1,misses) spins, immediate_gets igets,
immediate_misses*100/decode(immediate_gets,0,1,immediate_gets) imisses
from v$latch order by gets + immediate_gets
/

2-Check latch holders


set numwidth 5
select distinct lh.inst_id, s.sid, s.username, p.username os_user, lh.name
from gv$latchholder lh, gv$session s, gv$process p
where (lh.sid = s.sid and lh.inst_id = s.inst_id)
and (s.inst_id = p.inst_id and s.paddr = p.addr)
order by lh.inst_id, s.sid
/

3-List the latches and note the latch# number of most latch

SELECT latch#, substr(name,1,40), gets, misses, sleeps
FROM v$latch WHERE sleeps>0
ORDER BY sleeps ;

4-Find the latch child and note the addr of most used child

SELECT addr, latch#, gets, misses, sleeps
FROM v$latch_children
WHERE sleeps>100 and latch# = &LATCH_NUMBER_WANTED
ORDER BY sleeps ;
5-Find the file# number and block number of hot block

SELECT File# , dbablk, class, state ,tch
FROM x$bh WHERE hladdr='&ADDR_OF_CHILD_LATCH' order by tch;

6-Find the hot block

SELECT distinct owner, segment_name, segment_type
FROM dba_extents
WHERE file_id= &FILE_ID
and &BLOCK_NUMBER between block_id and block_id+blocks-1
/