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 a key component of the Oracle System Global Area (SGA), designed to store data blocks in memory to reduce disk I/O. By keeping frequently accessed data blocks in memory, the buffer cache improves query performance and minimizes the need to read data from disk.

Key Features

Purpose: Stores copies of data blocks (from tables, indexes, etc.) in memory to speed up data access.
Size Configuration: Controlled by the DB_CACHE_SIZE parameter, specified in bytes in the database initialization file.
Block Size: Matches the database’s default block size, set by the DB_BLOCK_SIZE parameter during database creation (e.g., 4KB or 8KB). Non-default block sizes (e.g., 2KB, 16KB) can be used for specific tablespaces, with their cache sizes set via parameters like DB_2K_CACHE_SIZE or DB_16K_CACHE_SIZE.

How the Buffer Cache Works

The buffer cache uses a Least Recently Used (LRU) algorithm to manage data blocks efficiently. All buffers reside in a single LRU list, which tracks their usage:

LRU List: Buffers are ordered from most recently used (MRU) to least recently used (LRU).
- When a block is read from or written to, its buffer moves to the MRU end of the list.
- Buffers at the LRU end are candidates for replacement when new data blocks need to be loaded.
Dirty Buffers: A buffer becomes "dirty" when its data is modified and needs to be written back to disk. Dirty buffers remain in the LRU list but are flagged for writing by the database writer process (DBWn).

Explanation:

Blue Buffers (1, 3, 4, 8): Dirty buffers containing modified data awaiting disk write.
Green Buffers (2, 5, 7): Clean buffers, ordered by recency of use.
- Buffer 7: Most recently used (MRU).
- Buffer 2: Third most recently used.
- Buffer 5: Least recently used (LRU).
Buffer 6: Recently modified, will be flagged as dirty but remains in the LRU list.

Managing Multiple Buffer Pools

Oracle supports multiple buffer pools to optimize performance for different workloads:

Default Pool: Handles data blocks for tablespaces using the default block size (e.g., 8KB).
Keep Pool: Retains frequently accessed data to reduce cache eviction.
Recycle Pool: Stores transient data to minimize cache pollution.
Non-Default Block Size Pools: Dedicated caches for tablespaces with non-standard block sizes (e.g., 2KB, 4KB, 16KB), configured via parameters like DB_2K_CACHE_SIZE.

Figure 2: Database Buffer Cache with Multiple Pools

Explanation:

Default Pool: Manages most operations for the standard block size.
2K, 4K, 16K Pools: Handle tablespaces with non-standard block sizes.
Keep and Recycle Pools: Optimize caching for specific access patterns.
Shaded blocks indicate data currently cached in each pool.

Configuring and Tuning the Buffer Cache

Proper sizing of the buffer cache is critical for performance:

Too Small: Leads to excessive disk I/O, slowing queries.
Too Large: Wastes memory that could be allocated elsewhere.
Tuning Goal: Adjust DB_CACHE_SIZE (and related parameters for non-default block sizes) to balance memory usage and performance. Monitor performance metrics to find the optimal size.

Dynamic Resizing

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

ALTER SYSTEM SET DB_8K_CACHE_SIZE = 8M SCOPE=BOTH;

Note: SCOPE=BOTH applies the change immediately and saves it to the server parameter file (SPFILE) for persistence.

Granularity: Cache sizes must align with Oracle’s granule size, which depends on the SGA size.
Checking Free Memory: Query the V$SGA_DYNAMIC_FREE_MEMORY view to ensure sufficient unallocated memory is available.
Tablespace Creation: When creating a tablespace with a non-default block size, ensure the corresponding cache size parameter is set:
```
CREATE TABLESPACE my_tablespace DATAFILE 'my_datafile.dbf' SIZE 100M BLOCKSIZE 8K;
            
```

Note: The cache for a non-default block size (e.g., DB_8K_CACHE_SIZE) must be configured before creating a tablespace with that block size.

Performance Impact

The buffer cache directly affects database performance:

Efficient Cache: A well-sized cache reduces disk I/O, speeding up data access.
Tuning Strategy: Gradually increase DB_CACHE_SIZE until additional increases yield no performance improvement.
Specialized Pools: Use Keep and Recycle pools to optimize caching for specific workloads, such as frequently accessed tables or temporary data.

Key Takeaways

The database buffer cache stores data blocks in memory to minimize disk I/O.
All buffers are managed in a single LRU list, with dirty buffers flagged for disk writes.
Multiple buffer pools (Default, Keep, Recycle, and non-default block sizes) allow tailored memory management.
Proper sizing and tuning of the buffer cache 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/.
Always update the initialization file (init.ora) or system parameter file (SPFILE) when changing cache sizes for persistence across database restarts.