Oracle Internal Architecture - Database Buffer Cache

In this article, we will talk about a required component of the SGA called Database Buffer Cache, which stores data in memory.

The Database Buffer Cache is a crucial memory area inside SGA (System Global Area) of Oracle, which stores copies of data blocks read from physical files (datafiles). Think about it as a "temporary storage" in RAM that keeps the most useful information, allowing the database to access it extremely fast without going to the hard drive for each query.

The Buffer Cache works like a shared memory area, where every user connected to the database has simultaneous access. Every buffer inside the cache has the size of a data block (generally 8KB by default), creating a direct correspondence between the physical blocks on disk and their copies.

When you execute a SELECT query or a DML command (INSERT, UPDATE, DELETE), Oracle first checks if the data block is already in the Buffer Cache. If it is (called a "cache hit"), the data is read directly from memory, which is an extremely fast process. If it is not (called a "cache miss"), Oracle needs to find the block on disk, copy it to the buffer, and the process the request.

Management Algorithm

Oracle uses a sophisticated algorithm called LRU (Least Recently Used), combined with an access count, to manage the buffers. This means that the most recently and frequently accessed blocks tend to remain in memory, while the least used blocks are removed to make space for the new data.

Buffer Pool Structure

The Buffer Cache is divided into different pools to optimized the memory usage:

• Default Pool: Where the majority of blocks are stored by default. Unless you configure additional pools, this is the only active pool.
• Keep Pool (Optional): For objects that should remain permanently in memory, such as small tables that are frequently accessed.
• Recycle Pool (Optional): For objects that are rarely accessed and don't need to occupy space in cache for a long time.
• Non-Standard Block Size Pools: Used when tablespaces have block sizes that are different from the standard (2 KB, 4 KB, 16 KB, etc.).

Buffer states

The main objective of the Buffer Cache is to minimize physical I/O (reads/write on disk), which is often much slower than memory operations. A well-dimensioned Buffer Cache can drastically improve the performance of the database, because:

• Reduces query response time.
• Reduce the load on the disk subsystem.
• allows most users to work simultaneously without performance degradation.

When a transaction modifies data, it does not change the blocks directly on disk, but instead modifies the copies in the Buffer Cache. Later, the background process DBWn (Database Writer) writes the changes back to the datafiles.

Diagnostic Queries

1. View Pools Information of the Buffer.

2. Calculated Buffer Cache Hit Radio (Accuracy Rate)

3. Checks objects in the Buffer Cache

4. Analyze size recommendations

5. Identify queries that consume the most Buffer Cache memory