Oracle Database Discussions

I have found that there are 'Bufffer Cache Chain" latch by following sql

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

I picked up one of the addr and execute following sql.
select /*+ ordered */
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 from above query' and
e.file_id = x.file# and
x.dbablk between e.block_id and e.block_id + e.blocks -1
order by x.tch desc;

But this query is taking such a long time. I look at explain plan and it's dba_extents that has so many full table scan internally. Is there any way to re-write this query to get same resule or to improve it.

Thanks

~Keyur

Message was edited by:
Keyur Patel