Database Buffer Cache

🧠 Introduction


When your Oracle database needs to read or write data, it doesn’t always hit the disk directly. Instead, it uses a special memory area called the Database Buffer Cache to speed things up. Let’s break down what this means.

🧩 What is the Database Buffer Cache?


The Database Buffer Cache is part of the System Global Area (SGA) — a shared memory pool in the Oracle instance. It stores copies of data blocks recently read from data files, allowing fast access by multiple users connected concurrently.

🎯 Why Use the Buffer Cache?

  • To reduce expensive physical disk I/O by caching frequently accessed data in memory
  • To serve data faster on repeated requests (called a cache hit)
  • To only read from disk when data is not found in memory (called a cache miss)

⚙️ How It Works

  1. A user process requests a data block.
  2. Oracle checks if the block is in the buffer cache.
    • If found (cache hit) → Data is served directly from memory (fast).
    • If not found (cache miss) → Data is read from disk into the cache before use (slower).
  3. The buffer cache uses an algorithm combining Least Recently Used (LRU) lists and touch counts to keep frequently used blocks in memory.

🧠 Components of the Buffer Cache

  • Default Pool
    ➤ Main area where data blocks are cached (default block size 8 KB)
    ➤ It’s the only pool unless other pools are configured
  • Keep Pool (Optional)
    ➤ For frequently accessed blocks you want to “keep” in memory
    ➤ Objects must be explicitly assigned to this pool
  • Recycle Pool (Optional)
    ➤ For blocks that are accessed rarely
    ➤ Helps prevent such blocks from using space in the default pool
  • Non-Default Buffer Pools (Optional)
    ➤ For tablespaces using non-standard block sizes (2 KB, 4 KB, 16 KB, 32 KB)
    ➤ Each block size has its own buffer pool

🧩 Other Important Concepts

  • Least Recently Used (LRU) List
    ➤ Tracks buffer usage
    ➤ “Hot” buffers (recently used) stay in memory; “cold” ones get aged out
  • Checkpoint Queue
    ➤ List of dirty buffers to be written to disk, ordered by redo block address
  • Flash Cache (Database Smart Flash Cache) (Optional)
    ➤ Extends the buffer cache using flash storage
    ➤ Only clean buffers are written here
    ➤ Faster than disk but slower than memory
    ➤ Separate LRU chains for DEFAULT and KEEP

📌 Note: Dirty buffers are never written to flash cache — only clean buffers go there.

🛠️ DBA Tip of the Day

Smart configuration of the buffer cache can dramatically reduce physical disk reads. Use the Keep Pool for frequently accessed tables, the Recycle Pool for transient data, and consider Flash Cache if you’re working with large datasets and flash storage.

ADVERTISEMENT