Pin object in Buffer Cache Memory of SGA in Oracle

Buffer Cache is a part of the SGA memory. It is used to keep the recently used blocks of data into memory to reduce the IO of hard disk.

Buffer cache configure with three ways:
Default: It is used for storage data blocks in memory that have been retrieved from data files. Parameter db_cache_size defines the default buffer cache.
Keep: Cache is that it will hold frequently accessed blocks without ageing them out. It is managed by user for which object need to put in keep blocks. Parameter db_keep_cache_size is used to define.
Recycle: Those objects that you do not want to keep in memory. keep segments that are scanned rarely or are not useded frequently. Parameter defines as db_recycle_cache_size

In Oracle, Automatic memory management, the following view let you know the size of Memory utilization by different components:

select component ,current_size from v$memory_dynamic_components where current_size != 0;

select component, current_size from v$memory_dynamic_components where component = 'DEFAULT buffer cache';


For db_keep_cache_size you can allocate it minimum value for use. ( it is the Keep buffer cache parameter)

alter system set db_keep_cache_size=100m scope=spfile;


1. Pin the object in Keep buffer pool:

-- Creation time define the buffer pool parameter
create table ic.test1 (id number) tablespace users storage (buffer_pool keep);

alter index ic.test1 storage ( buffer_pool keep);


2. Check the buffer pool is keep or recycle bin

select BUFFER_POOL from dba_tables where OWNER='IC' AND TABLE_NAME='TEST1';
BUFFER_POOL
-----------
keep


3. To bring it back to default from keep pool:

alter table ic.test1 storage(buffer_pool default);


4. Check the buffer pool is keep or recycle bin

select BUFFER_POOL from dba_tables where OWNER='IC' AND TABLE_NAME='TEST1';
BUFFER_POOL
-----------
default