Lesson 3	Oracle Database Buffer Cache
Objective	Describe how the database buffer cache manages data blocks.

Oracle Database Buffer Cache holds Data Blocks

Understand how the Oracle database buffer cache manages data blocks to optimize database performance.

Overview of the Database Buffer Cache

The database buffer cache is one of the largest and most heavily used components of the Oracle System Global Area (SGA). Its job is simple to state and constant work to manage well: store copies of data blocks in memory so the instance can avoid re-reading them from disk every time a session needs them. Every session connected to the instance shares access to the same cache, which is exactly what makes it powerful. If one session reads a block into the cache, every other session that needs the same block benefits from that work already being done.
Keeping frequently accessed blocks in memory rather than on disk is, in practical terms, the single biggest lever most DBAs have over query performance. Memory access is measured in nanoseconds, disk access in milliseconds, a gap of several orders of magnitude that no amount of clever SQL can fully compensate for if the buffer cache is undersized for the working set of data an application actually touches.

Key Features

Purpose: stores copies of data blocks, from tables, indexes, and other segments, in memory to speed up repeated access to the same data.
Size Configuration: controlled by the DB_CACHE_SIZE parameter, specified in bytes (or with a K/M/G suffix) in the database initialization file or SPFILE.
Block Size: matches the database's default block size, set by the DB_BLOCK_SIZE parameter at database creation time and not changeable afterward without recreating the database. Non-default block sizes (for example 2KB or 16KB) can still be used for individual tablespaces, with their own dedicated cache sized via parameters such as DB_2K_CACHE_SIZE or DB_16K_CACHE_SIZE.

How the Buffer Cache Works

The buffer cache manages data blocks using a Least Recently Used (LRU) algorithm. Every buffer lives in a single LRU list that tracks how recently it has been touched:

LRU List: buffers are ordered from the most recently used (MRU) end to the least recently used (LRU) end.
- Whenever a block is read or written, its buffer moves toward the MRU end of the list.
- Clean buffers sitting at the LRU end are the first candidates considered for replacement when a new block needs to be loaded into the cache.
Dirty Buffers: a buffer becomes "dirty" the moment its data is modified and still needs to be written back to disk. A dirty buffer stays in the LRU list, and remains fully readable by other sessions, but carries a dirty flag until the database writer process (DBWn) writes it out and clears that flag.

Oracle 23ai Database

Oracle Database Buffer Cache LRU list and dirty-buffer writeback — Figure 1: Oracle Database Buffer Cache, conceptual LRU management and dirty-buffer writeback.

Explanation:

The LRU list: buffers B1 through B8 are shown ordered along a single list, with the LRU end on the left and the MRU end on the right. Recently accessed buffers continually move toward the MRU end as sessions touch them.
Dirty buffers (B1, B3, B4, B8): each carries a visible "dirty" flag, marking data that has been modified in memory but not yet written to the datafiles.
Clean buffer at the LRU end (B5): marked as a replacement candidate. Because it is clean, not dirty, it can be reused immediately for a new block without first waiting on a disk write.
DBWn: the database writer process pulls dirty buffers and writes them out to the datafiles in the background, clearing their dirty flag once the write completes. This is why a COMMIT does not wait on data file writes, only on the much smaller, much faster redo log write.

Managing Multiple Buffer Pools

Oracle supports multiple buffer pools so you can tune caching separately for workloads that behave differently:

Default Pool: handles data blocks for tablespaces using the database's default block size, and is where most objects live unless explicitly assigned elsewhere.
Keep Pool: retains frequently accessed segments in memory, protecting them from being aged out by unrelated, one-time activity elsewhere in the cache.
Recycle Pool: holds segments with a large footprint and infrequent reuse, so a single large scan does not flush the rest of the cache.
Non-Default Block Size Pools: dedicated caches for tablespaces created with a non-standard block size (for example 2KB, 4KB, or 16KB), each configured through its own DB_nK_CACHE_SIZE parameter.

Figure 2: Database Buffer Cache with Multiple Pools.

Explanation:

Default Pool: manages most operations at the standard block size and is typically the largest single pool in the cache.
2K, 4K, and 16K Pools: handle tablespaces created with non-standard block sizes, each isolated from the Default Pool entirely.
Keep and Recycle Pools: optimize caching for specific access patterns rather than block size, and both draw from the same standard block size as the Default Pool.
Shaded cells represent buffers currently holding cached data within each pool.

Configuring and Tuning the Buffer Cache

Proper sizing of the buffer cache is one of the highest-leverage tuning decisions you will make on an Oracle instance:

Too Small: leads to excessive physical I/O, since blocks are aged out and re-read from disk more often than necessary, slowing every query that touches them.
Too Large: wastes memory that could otherwise go to the shared pool, PGA work areas, or other instances on the same host.
Tuning Goal: adjust DB_CACHE_SIZE, and the relevant DB_nK_CACHE_SIZE parameters for any non-default block sizes, to balance memory usage against measured performance rather than guessing at a round number.

Before changing anything, Oracle can actually predict how a different cache size would perform. Enable the advisor and query its projections with:

ALTER SYSTEM SET DB_CACHE_ADVICE = ON;
SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor
FROM V$DB_CACHE_ADVICE
WHERE block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size');

estd_physical_read_factor is the number to watch: it estimates how physical reads would change relative to the cache's current size, letting you see whether doubling the cache would meaningfully help before you actually commit the memory.

Dynamic Resizing

Buffer cache sizes can be adjusted while the database is running, without a restart, using the ALTER SYSTEM command:

ALTER SYSTEM SET DB_16K_CACHE_SIZE = 16M SCOPE=BOTH;

Note: SCOPE=BOTH applies the change immediately in memory and writes it to the server parameter file (SPFILE) so it persists across the next restart, in a single step.

Granularity: cache sizes must align with Oracle's granule size, which itself depends on the total SGA size, so a requested value is rounded up to the nearest granule boundary rather than applied exactly as typed.
Checking Free Memory: query the V$SGA_DYNAMIC_FREE_MEMORY view first to confirm enough unallocated SGA memory is actually available before requesting a resize.
Tablespace Creation: when creating a tablespace with a non-default block size, the matching cache size parameter must already be set, or the CREATE TABLESPACE statement fails:
```
CREATE TABLESPACE my_tablespace DATAFILE 'my_datafile.dbf' SIZE 100M BLOCKSIZE 16K;
```

Note: the cache for a non-default block size (for example DB_16K_CACHE_SIZE) must be configured before creating any tablespace with that block size, not after.

Performance Impact

The buffer cache directly affects database performance in a few concrete ways:

Efficient Cache: a well-sized cache reduces physical I/O, speeding up data access for every session sharing it, not just the one that first read a given block.
Tuning Strategy: gradually increase DB_CACHE_SIZE, checking V$DB_CACHE_ADVICE along the way, until further increases stop meaningfully improving the estimated physical read factor.
Specialized Pools: use Keep and Recycle pools to isolate specific access patterns, such as small frequently joined lookup tables versus large infrequently scanned archive tables, rather than letting them compete for the same LRU list.

Key Takeaways

The database buffer cache stores data blocks in memory to minimize disk I/O, shared across every session connected to the instance.
All buffers are managed in a single LRU list, moving toward the MRU end on access, with dirty buffers flagged for DBWn to write out in the background.
Multiple buffer pools, Default, Keep, Recycle, and non-default block sizes, allow memory management tailored to how different objects are actually accessed.
Proper sizing and tuning, guided by V$DB_CACHE_ADVICE rather than guesswork, are essential for optimal database performance.

Notes:

For more details on tuning parameters, refer to Oracle's official documentation at https://www.oracle.com/database/.
SCOPE=BOTH only works when the instance was started from an SPFILE, since it writes the change there directly. A database still running on a legacy text-based PFILE (init.ora) cannot use SCOPE=BOTH at all, it would need SCOPE=MEMORY plus a manual edit to init.ora for the change to survive a restart. Migrating to an SPFILE with CREATE SPFILE FROM PFILE removes this limitation entirely.