SQL> select * from v$latchname where name like 'library cache%'
select * from (select addr, ts#, file#, dbarfil, dbablk, tch from x$bh order by tch desc) where rownum<11; 2.2结合dba_extents查询得到这些热点Buffer来自哪些对象 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; 2.3结合SQL视图可以找到操作这些对象的相关SQL,然后通过优化SQL减少数据的访问,或者优化某些容易引起争用的操作(如connect by等操作)来减少热点块竞争 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;