找熱點物件的sql:

select 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;

 

e.block_id<=b.dbablk and e.block_id + e.blocks > b.dbablk  的意思是

把一個block定位在一個extent裡從而確定這個block是屬於哪個segment（確定這個block屬於哪個物件）