Actual Summary Size of "KEEP" buffer pool segments that resides in database cache.

Query to get actual summary size of ‘KEEP’ buffer pool segments that resides in database cache . This can be rewrite to get information about all object (table) segments that resides in db cache .
Objects can be pinned by force in memory or just loaded by LRU algorithm. Database Cache size is managed by Oracle AMM if sga_target parameter is set. (  v$sga_current_resize_ops ) or limited by db_cache_size parameter ( without amm is expected ) 

/* 28-03-2013 kamil.piorekk@gmail.com Kamil Piórek. */

SELECT Sum((blocks*8192)/(1024*1024*1024))

  FROM (
    select decode(pd.bp_id,1,'Keep',2,'Recycle',3,'Default',4,'2K Subcache',5,'4K Subcache',6,'8K Subcache',7,'16K Subcache',8,'32K Subcache','UNKNOWN') subcache,bh.object_name,bh.blocks
           from x$kcbwds ds,x$kcbwbpd pd,  (select /*+ use_hash(x) */ set_ds,o.name object_name,count(*) BLOCKS
                                                 from obj$ o, x$bh x

                                                   where o.dataobj# = x.obj
                                                     and x.state !=0 and o.owner# !=0
                                                         group by set_ds,o.name) bh
              where ds.set_id >= pd.bp_lo_sid
                and ds.set_id <= pd.bp_hi_sid
                and pd.bp_size != 0
                and ds.addr=bh.set_ds
                and pd.bp_id=1 /* 1=KEEP */)

Best Regards

Kamil Piórek

kamil.piorekk@gmail.com