What is and how to configure keep cache, recycle cache, and default cache in Oracle’s SGA?

In a world where data agility and availability are the backbone of any medium or large-sized business, a poorly optimized database is a ticking time bomb. Especially when we talk about Oracle Database, the heart of mission-critical systems, memory management becomes both an art and a science. As a DBA, DevOps, or IT manager, you know that every millisecond of latency and every incident of downtime represents a significant cost to the company.

HTI Tecnologia understands this pain. With years of experience in consulting and 24/7 support for databases like Oracle, we are specialists in transforming data into strategic value, ensuring that performance, security, and availability are never a problem. One of the most crucial areas for this optimization is the SGA (System Global Area), Oracle’s most important memory component, and the management of its cache buffers.

In this technical article, we’ll dive into the details of the keep cache, recycle cache, and default cache of the Oracle SGA. You’ll discover why incorrect configuration of these pools can lead to operational disasters and, more importantly, how a professional and strategic outsourcing approach can shield your infrastructure from these risks.

The Heart of Oracle: Understanding the SGA and the Buffer Cache

The Oracle SGA is a shared memory area where the database stores information and data for processing. Its performance is directly impacted by how you manage the Database Buffer Cache, the part of the SGA responsible for storing recently accessed data blocks.

This “waiting area” is vital. When a user or an application requests data, Oracle first checks if these blocks are already in the buffer cache. If they are (a cache hit), the read is extremely fast, without the need to access the disk, which is a performance bottleneck. If they are not (a cache miss), Oracle needs to go to the disk, which increases latency and resource consumption.

The good news is that Oracle offers mechanisms to refine this management: the default buffer pool, the keep buffer pool, and the recycle buffer pool. Each has a specific purpose, and their misuse is a common mistake that can compromise the stability of your environment.

Default, Keep, and Recycle Cache: What They Are and What They’re For

Memory management in Oracle is a game of intelligent allocation. Let’s understand the role of each of these pools and how they complement each other.

1. Default Buffer Pool: The Standard Option

The default buffer pool is the main cache area and is enabled by default. All data blocks read from tables and indexes that are not explicitly associated with another pool are stored here. It’s your main work area.

Behavior: Oracle uses an LRU (Least Recently Used) algorithm to manage blocks within this pool. When the cache is full and a new block needs to be read, the least recently used block is removed to make way for the new one.
Configuration: Its size is controlled by the DB_CACHE_SIZE parameter.

Checking the size of the Default Buffer Pool:

SHOW PARAMETER DB_CACHE_SIZE;

Example Output:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 800M

2. Keep Buffer Pool: For High-Priority Data

Imagine you have small tables that are accessed with extremely high frequency, such as lookup or parameter tables. If they are stored in the default pool, they can be easily “ejected” by larger and less critical blocks, resulting in cache misses and performance degradation.

The keep buffer pool solves this problem. It is a dedicated pool for objects that you want to keep in memory indefinitely, regardless of access frequency.

Behavior: Data blocks from objects associated with this pool are not removed by the LRU algorithm, ensuring the data remains in the cache.
Configuration: It is enabled and sized by the DB_KEEP_CACHE_SIZE parameter. You associate tables with it using the STORAGE clause (ALTER TABLE ... STORAGE (BUFFER_POOL KEEP)).

Configuring the Keep Buffer Pool and associating a table:

ALTER SYSTEM SET DB_KEEP_CACHE_SIZE = 100M SCOPE=BOTH;

CREATE TABLE HTI_LOOKUP (
    id NUMBER PRIMARY KEY,
    descricao VARCHAR2(100)
);

INSERT INTO HTI_LOOKUP VALUES (1, 'Status Ativo');
INSERT INTO HTI_LOOKUP VALUES (2, 'Status Inativo');
COMMIT;

ALTER TABLE HTI_LOOKUP STORAGE (BUFFER_POOL KEEP);

SHOW PARAMETER DB_KEEP_CACHE_SIZE;

SELECT SEGMENT_NAME, SEGMENT_TYPE, BUFFER_POOL
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = 'HTI_LOOKUP' AND OWNER = 'YOUR_SCHEMA';

3. Recycle Buffer Pool: For Temporary Junk

The opposite of the keep cache is the recycle buffer pool. It is ideal for tables or indexes that you know are accessed sporadically or that contain data that will not be reused quickly after the first access. A classic example is a batch processing log table that is read only once.

Behavior: This pool has a more aggressive LRU behavior, removing blocks quickly after use. This prevents these blocks from “polluting” the default cache, pushing out data that would be more useful for the system’s overall performance.
Configuration: It is enabled and sized by the DB_RECYCLE_CACHE_SIZE parameter. Object association is done in the same way as the keep cache.

Configuring the Recycle Buffer Pool and associating a table:

ALTER SYSTEM SET DB_RECYCLE_CACHE_SIZE = 50M SCOPE=BOTH;

CREATE TABLE HTI_LOG_PROCESSAMENTO (
    log_id NUMBER PRIMARY KEY,
    data_hora TIMESTAMP,
    mensagem VARCHAR2(4000)
);

INSERT INTO HTI_LOG_PROCESSAMENTO VALUES (1, SYSDATE, 'Processo iniciado.');
INSERT INTO HTI_LOG_PROCESSAMENTO VALUES (2, SYSDATE, 'Etapa 1 concluída.');
COMMIT;

ALTER TABLE HTI_LOG_PROCESSAMENTO STORAGE (BUFFER_POOL RECYCLE);

SHOW PARAMETER DB_RECYCLE_CACHE_SIZE;

How to Configure and Optimize Keep, Recycle, and Default Cache

Correctly configuring these pools isn’t a guess; it’s a detailed analysis of your workload. Follow this practical guide:

1. Analysis and Monitoring

Before any changes, you need to understand your environment. HTI Tecnologia uses a consulting approach that begins with an in-depth diagnosis, identifying bottlenecks and optimization opportunities.

Monitor the AWR (Automatic Workload Repository): Check AWR reports to identify objects (tables and indexes) with a high number of logical and physical reads. These are prime candidates for the keep cache.
Analyze access frequency: Use the V$BH view to understand the frequency of data block access.

Monitoring the Buffer Cache with V$BH:

SELECT
    o.OBJECT_NAME,
    o.OBJECT_TYPE,
    COUNT(b.OBJ#) AS BLOCKS_IN_CACHE,
    b.BUFFER_POOL
FROM
    V$BH b
JOIN
    ALL_OBJECTS o ON b.OBJ# = o.DATA_OBJECT_ID
WHERE
    o.OWNER = 'YOUR_SCHEMA'
GROUP BY
    o.OBJECT_NAME, o.OBJECT_TYPE, b.BUFFER_POOL
ORDER BY
    BLOCKS_IN_CACHE DESC;

SELECT BUFFER_POOL, COUNT(*) AS TOTAL_BLOCKS
FROM V$BH
GROUP BY BUFFER_POOL;

2. Step-by-Step for Configuration

Size the keep cache: Start with a modest value and, based on analysis, increase or decrease as needed. The size should be sufficient to hold the most frequently accessed objects.
Associate the objects: Use the ALTER TABLE ... STORAGE (BUFFER_POOL KEEP) clause for tables and ALTER INDEX ... STORAGE (BUFFER_POOL KEEP) for indexes.
Size the recycle cache: Allocate a small size, just enough for objects you’ve already identified as “junk.”
Associate the objects: Use the ALTER TABLE ... STORAGE (BUFFER_POOL RECYCLE) clause for objects that need to be quickly ejected.
Adjust the default cache: After sizing the keep and recycle pools, the largest portion of the SGA will still be the default cache. Monitor its performance and adjust DB_CACHE_SIZE as needed.

Adjusting DB_CACHE_SIZE:

ALTER SYSTEM SET DB_CACHE_SIZE = 900M SCOPE=BOTH;

SHOW PARAMETER DB_CACHE_SIZE;

3. The Danger of Incorrect Configuration

Configuring these pools without a thorough technical analysis is like driving in the dark.

Keep Cache That’s Too Large: Can seize a portion of memory that would be better used by the default pool for other important objects, leading to a general cache miss.
Unnecessary Recycle Cache: If used for objects that are frequently accessed, it can force Oracle to make repeated disk reads, degrading performance.

DBA Outsourcing: Why Your Company Can’t Afford to Get It Wrong

Managing an Oracle environment, especially in medium and large companies, is a complex responsibility. Cache optimization, as much as it may seem like a detail, is just a small part of a puzzle that includes security, high availability, disaster recovery, and performance tuning.

Trying to manage everything internally can lead to:

Lack of Technical Focus: Your internal team is already overloaded with countless tasks. Database management, which requires an extremely high level of specialization, can fall by the wayside.
Elevated Operational Risk: A configuration error, like incorrect sizing of the cache pools, might not be noticed immediately but could lead to a system failure during peak access times.
Discontinuity: Dependence on a single professional (or a few) for database expertise creates a business continuity risk.

HTI Tecnologia positions itself as a strategic partner, not just a vendor. We offer a team of expert DBAs who are 24/7 monitoring and optimizing your databases. It’s the guarantee that your company will have the best of technology and the best of human expertise, without the costs and risks of an undersized internal team.

Imagine the peace of mind of knowing your data operation is in the hands of specialists who have already faced and solved the most complex challenges. One of our most recent case studies involved a client in the financial sector who was suffering from slow transaction processing. After a meticulous analysis, our consulting team identified bottlenecks in Oracle’s memory management, reconfiguring the cache pools and applying other optimization techniques, resulting in a 40% reduction in transaction response time.

If you want to go deeper into database optimization, also check out our article on how to perform a good database backup. And to understand the importance of proactive monitoring, read our page on 24/7 Support and Maintenance Services.

Don’t Leave the Future of Your Business to Chance

The performance of your Oracle Database is a critical factor for your company’s success. Configuring the cache pools is a fundamental step to ensure your most important data is always available and the system operates with maximum efficiency.

However, the complexity and criticality of the environment demand more than basic knowledge. They demand the experience and dedication of specialists.

Don’t let configuration errors, lack of time, or limited expertise jeopardize the continuity and competitiveness of your business. HTI Tecnologia is ready to be your strategic extension in database management, ensuring you and your team can focus on what really matters: innovation and company growth.

Want to secure your database operation and ensure uninterrupted performance and availability?

Schedule a conversation with one of our Oracle specialists and discover how HTI Tecnologia can transform the management of your data infrastructure.

Schedule a meeting here