How to check that your KEEP cache is right-sized.

I recently wanted to reduce the KEEP cache on one of the databases I am working on since evidence indicated that increased PGA RAM would be more useful (slow hash joins aplenty and no easy way to avoid them, but that’s a story for a different day).

I ran the following query that I got from Oracle support:

select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
        4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
         7,'16K SUBCACHE',8,'32KSUBCACHE','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 got result looking like this:

KEEP         DIM_AGREEMENT_C                12840                 

DEFAULT      DIM_PERS_DETAIL                31491                 

DEFAULT      TXPKR_PERSON_CONTRACT          19969                 

DEFAULT      TXPKE_CONTRACT                 8181                  

DEFAULT      MA_CLIENT_CONTACT_DETS_UC_C    2860                   

KEEP         DIM_AGREEMENT_C                6854                  

DEFAULT      SQL060602224851480             11333                 

I pasted the results into MS Excel and used the text to columns function in the data menu to format it nicely. Then I used the SUMIF conditional aggregation function to total all the blocks of objects in the KEEP cache and multiplied the result with the db block size to get the size of my actual KEEP cache.

This worked out to 770M with the specified db_keep_cache_size being 800M, so all was good in the land of KEEP.

A way, by the way (so to speak) to observe this is to employ SQL Plus as follows:

SQL> show parameters keep

NAME                                 TYPE        VALUE

------------------------------------ ----------- ----------

buffer_pool_keep                     string

control_file_record_keep_time        integer     32

db_keep_cache_size                   big integer 800M

Herman Scheepers signing  of from Cape Town