Your Oracle server operates with a substantial amount of RAM, but the application’s performance is inconsistently slow. You observe in the AWR (Automatic Workload Repository) that the Buffer Cache Hit Ratio is good, above 98%, but even so, the application suffers from I/O waits (db file sequential read, db file scattered read). This scenario is a classic Oracle puzzle: the high-level metrics look healthy, but the end-user experience is poor. The root cause, often, is not the total size of your memory, but the contention within it.
Your Oracle’s memory configuration is not a monolithic block. The main component, the Buffer Cache, operates by default with an LRU (Least Recently Used) algorithm, which can be brutally inefficient for mixed workloads. A single report query that scans a giant table can “pollute” the cache, evicting dozens of small and vital lookup tables crucial for transactional performance. The solution to this problem is intelligent memory segregation using the KEEP, RECYCLE, and DEFAULT pools. Understanding how to configure them is the difference between reactive memory management and a proactive performance architecture.
The Three Buffer Pools: A Segregation Strategy
Within the large memory area of the System Global Area (SGA), DB_CACHE_SIZE defines the size of the Buffer Cache. By default, this is the DEFAULT pool. However, you can subdivide this memory to create specialized pools.
1. The DEFAULT Pool: The “General Public”
This is the standard behavior. All objects (tables, indexes) that are not explicitly assigned to another pool live here. It uses an LRU algorithm to manage which data blocks remain in memory.
- Problem: A large table read by an analytical operation might be considered “recently used” and could therefore force the removal of hundreds of blocks from smaller tables that are accessed much more frequently by multiple transactions.
2. The KEEP Pool: The “VIP Area”
The DB_KEEP_CACHE_SIZE defines a portion of memory reserved for objects that you never want to be removed from the cache.
- Purpose: Ideal for small to medium-sized lookup tables that are constantly accessed by the application. Think of tables like CITIES, ORDER_STATUS, PRODUCT_CATEGORIES, or even sequences.
- Benefit: Keeping these objects in the KEEP pool ensures that access to them is always from RAM, eliminating disk I/O for the most critical and frequent data, and protecting them from “pollution” caused by large table scans.
3. The RECYCLE Pool: The “Express Exit Lane”
The DB_RECYCLE_CACHE_SIZE defines an area for objects that you want to be removed from memory as quickly as possible after use.
- Purpose: Perfect for large tables that are accessed sporadically and often randomly. The goal here is the opposite of KEEP: you do not want the blocks of these tables to remain in memory, consuming valuable space that could be used by more important objects.
- Benefit: It isolates the impact of scans on massive tables, preventing them from “washing out” the useful content of the DEFAULT pool.
Practical Guide: From Analysis to Implementation
The configuration is a three-step process: diagnose, allocate memory, and assign objects.
Step 1: Diagnosis – Identifying the Candidates
How do you know which objects belong in each pool? You need to analyze the access patterns.
Code: Finding Candidates for the KEEP Pool
The following query identifies small objects that are accessed very frequently (many “touches” on their blocks in the buffer cache).
-- Identifies small objects (< 50MB) with high access frequency
-- (many blocks in the buffer cache being "touched")
SELECT
o.owner,
o.object_name,
o.object_type,
COUNT(bh.objd) AS buffer_touches,
ROUND(SUM(s.bytes) / 1024 / 1024, 2) AS size_mb
FROM
v$bh bh
JOIN
dba_objects o ON bh.objd = o.data_object_id
JOIN
dba_segments s ON o.owner = s.owner AND o.object_name = s.segment_name
WHERE
o.owner NOT IN ('SYS', 'SYSTEM')
AND s.bytes < (50 * 1024 * 1024) -- Size limit, e.g., 50MB
GROUP BY
o.owner, o.object_name, o.object_type
HAVING
COUNT(bh.objd) > 1000 -- "Touches" threshold, adjust based on your workload
ORDER BY
buffer_touches DESC;
The objects at the top of this list are strong candidates for the KEEP pool. For the RECYCLE pool, look for the opposite: very large objects with a relatively low number of “touches.”
Step 2: Configuration – Allocating the Memory
You allocate memory for the KEEP and RECYCLE pools with ALTER SYSTEM commands. Important: This memory is subtracted from the DEFAULT pool (DB_CACHE_SIZE).
-- Example: Allocate 1GB for the KEEP pool and 2GB for the RECYCLE pool
ALTER SYSTEM SET DB_KEEP_CACHE_SIZE = 1G SCOPE = BOTH;
ALTER SYSTEM SET DB_RECYCLE_CACHE_SIZE = 2G SCOPE = BOTH;
Step 3: Assignment – Moving the Objects
Finally, you assign each object to its respective pool.
-- Assigns a lookup table to the KEEP pool
ALTER TABLE scott.categorias STORAGE (BUFFER_POOL KEEP);
-- Assigns a giant audit log table to the RECYCLE pool
ALTER TABLE app.logs_auditoria STORAGE (BUFFER_POOL RECYCLE);
-- To return an object to the default pool
ALTER TABLE scott.categorias STORAGE (BUFFER_POOL DEFAULT);
From Manual Configuration to Continuous Optimization with dbsnOOp
Manual analysis is powerful, but it’s a snapshot in time. Your application’s workload evolves, and an object that is “hot” today might become “cold” next month. Keeping this segregation optimized is an ongoing challenge.
dbsnOOp automates this analysis process.
- Continuous Access Analysis: Instead of running one-off queries, dbsnOOp continuously monitors the access patterns for every segment in your database.
- Proactive Recommendations: Based on this historical analysis, the platform can proactively identify and recommend which objects are the best candidates for the KEEP and RECYCLE pools, transforming memory tuning from a reactive task into a continuous data science.
Stop using a “one-size-fits-all” strategy for your database’s most critical resource.
Implement memory segregation and ensure your most important queries always find their data in the RAM expressway. Schedule a meeting with our specialist or watch a live demo!
Schedule a demo here.
Learn more about dbsnOOp!
Learn about database monitoring with advanced tools here.
Visit our YouTube channel to learn about the platform and watch tutorials.
Recommended Reading
- AI Database Tuning: The process of identifying and segregating objects for different cache pools is a complex optimization task. Understand how Artificial Intelligence can automate this analysis and provide continuous recommendations.
- dbsnOOp: The Monitoring and Observability Platform with an Autonomous DBA: Discover the complete vision of the platform and how granular monitoring of memory and object access fits into a larger strategy of autonomous and predictive data management.
- Query History: How We Use Telemetry to Find the Cause of an Intermittent Bug: The most treacherous bug is the one that doesn’t repeat. It appears intermittently, at random times, with unpredictable impact.