/* bcfootprint.sql
      see the objects with the biggest overall footpring in the buffer cache */
set lines 132
col Name format a30 trunc
col blkcnt format 999,999 
col pctavailblks format 999.90 head "% of Avail"
select * from (
select round(((count(v.block#) / a.availblks) * 100),2) pctavailblks,
       count(v.block#) blkcnt,
       o.owner,
       substr(o.object_name,1,30) "Name",
       o.object_type "Type"
from dba_objects o, v$bh v,
     (select file#,dbablk,count(*)
        from sys.x$bh x
       where x.file# > 0
         and x.hladdr in (select c.addr 
                            from v$latch_children c, v$latchname n
                           where c.latch# = n.latch#
                             and n.name like 'cache buffers chain%')
       group by file#, dbablk
       order by 2 desc
      ) h,
      (select count(*) availblks
         from sys.x$bh
        where file# > 0) a
where v.file# = h.file#
  and v.block# = h.dbablk
  and v.objd = o.object_id
group by a.availblks,o.owner,substr(o.object_name,1,30),o.object_type
order by 2 desc)
where rownum < 11;