Keeping objects in KEEP cache

As a part of oracle instance tuning, I was looking for the potential candidates for database KEEP pool. I looked at the statspack report and sort out the most physical read operations and their impacts. I planned to put those very frequently accessed tables to KEEP cache to avoid physical reads.

Now, I had to know the size (physical size of data on disk) of the candicate tables.
I found size related information using the following 2 queries-

--To see all the object size for a given user
SELECT segment_type, SUBSTR (segment_name, 1, 30) segment_name,
SUBSTR (tablespace_name, 1, 30) tablespace_name,
SUM (TRUNC (BYTES / (1024 * 1024 ), 2)) "Size in MB"
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
AND owner ='DEV7'
GROUP BY segment_type,
SUBSTR (segment_name, 1, 30),
SUBSTR (tablespace_name, 1, 30);

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

To see any existing table in keep pool- I issued

SQL> select x.table_name from user_tables x where x.buffer_pool = 'KEEP';
and found nothing.

Then I looked at the system parameter to know the current status of "db_keep_cache_size"

SQL> show parameter db_keep_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_keep_cache_size big integer 0

I set the cache value

SQL> alter system set db_keep_cache_size=100M scope=both;

Now, to put in keep, I issued
SQL> alter table DASH_COUNT_CACHE storage (buffer_pool KEEP);

--To remove from keep
--alter table DASH_COUNT_CACHE storage (buffer_pool DEFAULT);

*** This ends the caching process ***

One more thing, there is another parameter called "buffer_pool_keep" which is very confusing. Basically it is deprecated. Here is a note from Oracle -

BUFFER_POOL_KEEP - This parameter is deprecated in favor of the DB_KEEP_CACHE_SIZE parameter. Oracle recommends that you use DB_KEEP_CACHE_SIZE instead. Also, BUFFER_POOL_KEEP cannot be combined with the new dynamic DB_KEEP_CACHE_SIZE parameter; combining these parameters in the same parameter file will produce an error. BUFFER_POOL_KEEP is retained for backward compatibility only.