热点块的寻找
如何判断使系统是否有latch争用，可以查看v$latch视图
select latch#,name,misses,gets,sleeps from v$latch where name like ‘cache buffer%’
LATCH#,NAME,GETS,MISSES,SLEEPS
123,cache buffer handles,11025,0,0
117,cache buffers lru chain,47182,7,0
122,cache buffers chains,2797560,6,0
如果sleeps值占misses和gets和值比较大，说明latch争用比较大，存在latch等待
那么我们如何确定哪些块存在latch争用呢？
可以通过查询v$latch_children获得latch地址saddr
select addr,LATCH#,CHILD#,gets,misses,sleeps from v$latch_children
where name = ‘cache buffers chains’
然后在关联x$bh的hladdr,通过x$bh是视图获得block的文件号和块号
select dbarfil,dbablk from x$bh where hladdr in
(select addr from (select addr from v$latch_children
order by sleeps desc) where rownum<20);
知道块的地址就可以联合dba_extents找到对应段了，
select distinct a.owner,a.segment_name from
dba_extents a,
(select dbarfil,dbablk
from x$bh
where hladdr in
(select addr
from (select addr
from v$latch_children
order by sleeps desc)
where rownum < 20)) b
where a.RELATIVE_FNO = b.dbarfil
and a.BLOCK_ID <= b.dbablk and a.block_id + a.blocks > b.dbablk;
