Frequently accessed tables should be kept in Oracle Keep cache buffer pool. Keep buffer pool is the part of SGA which retains data into the memory so that next request for same data can be accessed from memory. This avoids disk read and increases performance.
The part of the SGA called the Buffer Cache holds copies of the data blocks that have been read from the data files. Oracle will keep frequently accessed blocks in memory to avoid the much slower physical I/O to access the blocks. Those data blocks that are not frequently used will be replaced over time with other database blocks. Therefore you should always put table into cache in Oracle for faster access. The default buffer pool size is determined by the DB_CACHE_SIZE initialization parameter.
When configuring a new database instance, it is impossible to know the correct size for the buffer cache. Typically, a database administrator makes a first estimate for the cache size, then runs a representative workload on the instance and examines the relevant statistics to see whether the cache is under-configured or over-configured.
Proper memory tuning is required to avoid repeated disk access for the same data. This means that there should be enough space in buffer cache to hold required data for long time. If same data is required in very short intervals then such data should be permanently pinned into memory.
Usually small objects should be kept in Keep buffer cache. DB_KEEP_CACHE_SIZE initialization parameter is used to create Keep buffer Pool. If DB_KEEP_CACHE_SIZE is not used then no Keep buffer is created.
Steps to configure keep cache in Oracle:
STEP 1: Check the current keep cache size:
SQL> show parameter keep;
STEP 2: Check table size :
If keep cache is not configured or not sufficient to hold the entire table, then increase the size of the cache but before that check the size of the table using below query:
SQL> select bytes/1024/1024 from user_segments where segment_name='&TABLE_NAME';
STEP 3: Configure keep cache:
SQL> alter system set db_keep_cache_size=SIZE scope=both;
Example: alter system set db_keep_cache_size=7G scope=both;
STEP 4: Move table into cache:
once we set the size for db_keep_cache_size and then we can move the tables using below query.
SQL> alter TABLE TABLE_OWNER.TABLE_NAME storage (buffer_pool keep);
Note: Change the table owner and table name.
STEP 5: You can check the table is part of the keep pool using below query:
SQL> SELECT SEGMENT_NAME, BUFFER_POOL from dba_segments where SEGMENT_NAME='&TABLE_NAME' and owner='&TABLE_OWNER';
SQL> select SEGMENT_NAME, SEGMENT_TYPE,BUFFER_POOL from dba_segments where BUFFER_POOL='KEEP' and SEGMENT_TYPE='TABLES';
After moving objects to keep cache you can observer the performance and check the “segment ordered by logical reads” in segment statistics of AWR report.
By pinning objects to appropriate buffer pools, you can:
-> Reduce or eliminate I/Os
-> Isolate or limit an object to a separate cache
-> You can make the response times for specific queries more predictable by storing the segments accessed by the queries in the KEEP cache to ensure that they do not age out.