Oracle provides a feature to keep objects in the Buffer pool. This feature allows you to keep the frequently accessed table in memory itself which will provide the faster access of the table. The object will be kept under the KEEP pool and the purpose of the keep pool is to accommodate / cache the small objects like look up tables.
In general the server process has to bring data blocks for the table from the disk to the memory (buffer pool) which requires an overhead. Moreover the object gets removed from the memory whenever the object is not getting used or oracle needs room for another table in the buffer pool. If you keep the frequently accessed objects in the memory the above mentioned overhead can be reduced.
Keep Buffer is also working in First In First Out mode. If Oracle has more objects to keep in the KEEP buffer, automatically it removes the older objects from the keep buffer. DB_KEEP_CACHE_SIZE is the parameter which determines the size of the Keep Buffer Pool. If this parameter is 0, Oracle adjust automatically size.
Keep Table in Buffer Pool
SQL> alter table testtable storage (buffer_pool keep);
Table altered.
Remove Table From Buffer Pool
SQL> alter table testtable storage (buffer_pool default);
Table altered.
Also, you can use cache statement for temporary caching. ALTER TABLE testtable CACHE puts the table in the general buffer cache, but tries to keep the table in the Most Recently Used end of the LRU list. Hopefully, this keeps the table in the cache longer. But nothing will prevent the table from being aged out the cache should system demands require it.
ALTER TABLE testtable STORAGE (buffer_pool keep) command places the table in a special section of the cache called the KEEP buffer pool. Objects in the KEEP cache hopefully won’t get aged out since you’ve ideally sized this cache to hold your objects.
These two commands are different because they place the table in different portions of the buffer cache.
Great explanation. Overall we can use the below straightforward steps to keep table into cache.
To check table size before putting into cache.
select bytes/1024/1024 from user_segments where segment_name=’SEGMENT_NAME’;
once we get the size of the two tables we can combined and adding some 2GB extra to that and allocate the same size to keep_cache using below query.
alter system set db_keep_cache_size=SIZE scope=both;
once we set the size for db_keep_cache_size and then we can move the tables using below query.
alter TABLE USERNAME.TABLE_NAME storage (buffer_pool keep);
we can check the table is part of the keep pool using below query
select table_name,buffer_pool from dba_tables where table_name=’TABLE_NAME’;
detailed information at : https://orahow.com/caching-a-table-in-keep-cache-buffer-pool/