/* hotblocks.sql
    show the hottest blocks in the buffer cache based
      on the touch count (TCH)
*/
col owner format a20 trunc
col object_name format a30
col touches format 9,999,999
select * from (
  select count(*),file#,dbablk, sum(tch) TOUCHES , u.name OWNER, o.name OBJECT_NAME
    from x$bh x, obj$ o, user$ u
   where x.obj = o.obj#
     and o.owner# = u.user#
 group by file#, dbablk, u.name, o.name
  order by 4 desc)
where rownum < 11;