一般情况下是含有全表扫描的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 '% '||b.segment_name|| '% ' and b.segment_type= 'TABLE ') order by hash_value,address,piece;
oracle 找热点块,需要找到造成oracle 热点块的sql
最新推荐文章于 2021-04-15 16:26:26 发布