热点块
select latch#,name,gets,misses,sleeps from v$latch where name like 'cache buffer%';
alter index oss.T_C_REGION storage(buffer_pool keep);
select object_name from dba_objects where data_object_id in (select obj from x$bh where hladdr in(select addr
from (select addr from v$latch_children order by sleeps desc) where rownum < 11)) ;
select object_name from dba_objects where data_object_id in(select obj from (select obj from x$bh order by tch desc) where rownum < 11) ;
select sql_text
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 a.hash_value,a.address,a.piece;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10322123/viewspace-611555/ ，如需转载，请注明出处，否则将追究法律责任。
