Performance Tuning: db_keep_cache_size

Performance Tuning-db_keep_cache_size

         Data which is frequently accessed should be kept in Keep buffer pool. Keep buffer pool retains data in the memory. So that next request for same data can be entertained from memory. This avoids disk read and increases performance. Usually small objects should be kept in Keep buffer.
According to Oracle documentation, a good candidate for a segment to put into the KEEP pool is a segment that is smaller than 10% of the size of the DEFAULT buffer pool.

To find the size of a particular table
select (x.blocks *8)/(1024) "Size in MB" from user_tables x where table_name = 'TBLMENU';


By selecting data from view v$db_cache_advice we can decide how much cache size should be set to db_keep_cache_size. Estimated Physical Read Factor equal to 1 is represent current value of db_keep_cache_size (2048 MB).

In this example, the output shows that if the cache was 384 MB, instead of the current size of 2048 MB—the estimated number of physical reads would increase by a factor of 2.01, or 101%. Hence, it is not advisable to decrease the cache size to 384MB.
However, increasing the cache size to 3840MB may potentially decrease reads by a factor of .87, or 13%. If an additional 1792MB memory is available on the system and the value of the SGA_MAX_SIZE parameter allows for the increment, it is advisable to increase the keep buffer cache pool (db_keep_cache_size) size to 3840MB.

To load a table tblMenu in KEEP buffer cache, we need to issue command as below:
ALTER TABLE TBLMENU STORAGE(BUFFER_POOL KEEP);

To unload a table tblMenu from buffer, we need to issue the following command:
ALTER TABLE TBLMENU STORAGE(BUFFER_POOL DEFAULT);

To check whether a table is keep in buffer, we need to issue the following command:
select s.segment_name,s.buffer_pool from dba_segments s where s.segment_name='TBLMENU';
SEGMENT_NAME    BUFFER_POOL
TBLMENU                 KEEP