Keep cache, managed via KEEP_BUFFER_CACHE, is a well-documented performance tuning parameter on the Oracle Database. Let’s take a look at ways in which you can retain objects in KEEP Buffer Cache, to help you improve your performance.
What is the KEEP Cache?
The KEEP_BUFFER_CACHE is a segment of the Oracle Database buffer cache, specifically designated to retain frequently accessed data blocks in memory. The primary goal of this parameter is to minimize disk I/O for frequently accessed objects, thereby enhancing the overall performance of the database.
Oracle first checks the buffer cache when a query or DML operation requires data. If the data is found (a buffer cache hit), it is retrieved from memory, significantly faster than reading from disk. If the data is not found (a buffer cache miss), it is read from the disk and then stored in the buffer cache for future access. Oracle uses a Least Recently Used (LRU) to maintain the buffers in the cache as explained in this blog
By assigning specific tables or indexes to the KEEP pool, you ensure that these frequently accessed objects remain in memory, thus reducing the need for disk access and improving response times.
The problem
One of our databases started experiencing a slowdown in performance and it was quite easy to identify the top SQL. The table referenced in the top SQL was a table called “TAB”.
Now we started investigating and saw that the SQL was performing a Full Table scan of a table TAB. Since the report didn’t have any join conditions, indexes were not in consideration. Reviewing the properties of the SQL
select cache, BUFFER_POOL, BLOCKS from dba_tables where table_name ='TAB' ;
CACHE BUFFER BLOCKS
----- ------- -------
Y KEEP 732280
SELECT o.object_name, COUNT(*) number_of_blocks
FROM DBA_OBJECTS O, V$BH bh
WHERE o.data_object_id = bh.OBJD
AND o.owner != 'SYS' and object_Name in ('TAB' )
GROUP BY o.object_Name ;
OBJECT_NAME NUMBER_OF_BLOCKS ------------ ----------------
TAB 732280
db_keep_cache_Size=6GB
Size of table (732280 *8)/1024 = 5720 MB We could see that the table was in the Buffer cache and the keep cache was greater than the size of the table. So it didn’t make any sense. Then we started looking at the history of this SQL. I love to use Tim Gorman’s sqlhistory.sql to look a the historical execution stats
The details from the “Bad Window” where there was a slowdown”
and the below were from a good window. As you can see the difference is in the physical IO per execution, and the avg elapsed has increased from ~8 secs to over ~24 secs..
Now the question was that if the Table was already in the keep cache, then why was the SQL not reading it?
The problem was that the table size had grown by ~ 200 MB and our keep cache was no longer enough to hold the table. This is because along with this table, the keep cache was also storing other objects.
Also, because statistics had not been gathered, the dba_tables did not contain the correct information, resulting in the actual table size being 5925 MB now.
select SEGMENT_NAME, (BLOCKS) from dba_segments where SEGMENT_NAME='TAB' ;
SEGMENT_NAME SUM(BLOCKS)
--------------------- -----------
TAB 758400
# Size of table (758400 *8)/1024 = 5925 MB
COLUMN size_for_estimate FORMAT 999,999,999,999 heading 'Cache Size (MB)'
COLUMN buffers_for_estimate FORMAT 999,999,999 heading 'Buffers'
COLUMN estd_physical_read_factor FORMAT 999,999,999 heading 'Estd Phys|Read Factor'
COLUMN estd_physical_reads FORMAT 999,999,999 heading 'Estd Phys| Reads'
SELECT size_for_estimate,size_factor, buffers_for_estimate, estd_physical_read_factor,
estd_physical_reads
FROM V$DB_CACHE_ADVICE
WHERE name = 'KEEP'
AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')
AND advice_status = 'ON';
SIZE_FOR_ESTIMATE estd_physical_read_factor ESTD_PHYSICAL_READS
----------------- ------------------------- -------------------
768 4444.8269 3268245664
4608 3350.2255 2463394011
5376 2919.9172 2146991736
6144 17.7401 13044166. >>> Size
6512 1 735292
Solution
So looking at the v$DB_CACHE_ADVICE for some advice it was clear that increasing the keep_cache from 6144 to 6512 MB would help. We increased and the Physical I/O dropped and the execution times were back to the ~8 sec mark.
Observations
The interesting observation was that the SQL performed a physical I/O instead of reading the partially cached information from the Buffer pool. The db did not utilize the keep pool despite partially caching the table.
Another thing to note is that it is important to look at your pools wholistically. So always check whether the instance has sufficient space to grow your keep_pool. And refer to the advisory framework (v$$DB_CACHE_ADVICE ) to get feedback about your sizing needs.
The KEEP_BUFFER_CACHE parameter is a powerful tool in the Oracle Database performance tuning arsenal. Use the KEEP pool judiciously to ensure that frequently accessed data remains in memory. This reduces disk I/O and significantly improves query response times. As with any performance tuning technique, careful planning, monitoring, and adjustment are key to reaping the full benefits.