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

I have recently evaluated a few code generators and (Object Relational Model) ORM frameworks in .NET, to my surprise, there were quite a few of them all with there own unique sets of pro’s and con’s and designed for various stages of development.

Why do we need an ORM/Code generator?

The main purpose here is to eliminate writing repetitive and static code; this will lead to a higher quality product, developed in a much shorter time. 

The repetitive/static code I am referring to is CRUD (Create, Read, Update and Delete), CRUD is common and is required by almost all applications, it is repetitive and error prone, Crud requires lots of testing time which can be reduced if code is automatically generated and it can take a lot of time (like weeks) if the database used is huge.

The Evaluation 

As mentioned, there are many ORM/code generators out there, each with there own pro’s and con’s, these are the features I looked for when selecting our ORM.

• Support for hierarchies and relationships,
• Support for grouping and aggregate functions,
• Support for transactions,
• Database independent,
• Concurrency,
• Performance and footprint,
• Learning curve and simplicity with which it can be integrated into code,
• Up to date documentation,
• Online support,
• Reasonable price.

End Result 

 
My evaluation has lead to an ORM and code generator that complement each other well, satisfy all of the above requirements, and has now been implemented as the core foundation of our development framework. 

 
Implementation has been successful and development time and quality of the product has been noticeably improved within the first week of implementation. This has a knock on effect that affects the clients directly, as greatly reduced development time means cheaper pricing and a happier client.

Nadir Kamdar