一般情况下是含有全表扫描的sql会造成热点块。
1、找到最热的数据块的latch和buffer信息
select b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch,b.gets,b.misses,b.sleeps from
(select * from (select addr,ts#,file#,dbarfil,dbablk,tch,hladdr from x$bh order by tch desc) where rownum <11) a,
(select addr,gets,misses,sleeps from v$latch_children where name= 'cache buffers chains ') b
where a.hladdr=b.addr;
2、找到热点buffer对应的对象信息:
col owner for a20
col segment_name for a30
col segment_type for a30
select distinct e.owner,e.segment_name,e.segment_type from dba_extents e,
(select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc) where rownum <11) b
where e.relative_fno=b.dbarfil
and e.block_id <=b.dbablk
and e.block_id+e.blocks> b.dbablk;
3、找到操作这些热点对象的sql语句:
break on hash_value skip 1
select /*+rule*/ hash_value,sql_text from v$sqltext where (hash_value,address) in
(select a.hash_value,a.address from v$sqltext a,(select distinct a.owner,a.segment_name,a.segment_type from dba_extents a,
(select dbarfil,dbablk from (select dbarfil,dbablk from x$bh order by tch desc) where rownum <11) b where a.relative_fno=b.dbarfil
and a.block_id <=b.dbablk and a.block_id+a.blocks> b.dbablk) b
where a.sql_text like