Memory Architecture   «Prev  Next»

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

Oracle Database Buffer Cache

The database buffer cache holds data blocks

The database buffer cache is usually the largest structure within the SGA. Its purpose is to hold as many data blocks in memory as possible in order to minimize the number of reads that an instance needs to perform. The larger the buffer cache, the better the chance that any given data block will already be in memory when it is needed.

Dirty list and LRU list

The buffer size always matches the database block size, which is specified in the database's initialization file. The buffers are organized into two lists: the dirty list, and the LRU List. LRU is an acronym that stands for Least Recently Used. Here's a diagram that shows how all this might look after an instance has been running for awhile. Move your mouse over the diagram to learn how the buffers are being used.

  1. Blue buffers 1,3,4,8: Dirty buffer
  2. Blue buffers 1,3,4,8: Dirty buffer
  3. Blue buffer 6: Will be moved to the dirty list
  4. Green buffer 5: The least recently used buffer
  5. Green buffer 7: The most recently used buffer
  6. Green buffer 2: The third most recently used buffer
  7. Dirty List 1,3,4,8: Pointers to the dirty buffers
  8. LRU List 5: Pointer to the least recently used buffer
  9. LRU List 7: Pointer to the most recently used buffer
  10. LRU List 2: Pointer to the third most recently used buffer
  11. LRU List 6: Pointer to the second most recently used buffer
  12. Blue buffers 1,3,4,8: Dirty buffer
  13. Blue buffers 1,3,4,8: Dirty buffer
  14. Blue buffers 1,3,4,8: Dirty buffer

Database Buffer Cache
The dirty list points to all the buffers that have been modified and that need to be written back to disk. Sometimes dirty buffers will be found in the LRU List as well, but eventually they too will make it to the dirty list. All the other buffers are contained in the LRU list.
The LRU list has two ends: the least recently used end and the most recently used end. Every time a buffer is written to or read from, it is moved to the most recently used end of the LRU list. Buffers on the least end of the LRU list are overwritten when new data needs to be read from disk.

A database has a standard block size. You can create a tablespace with a block size that differs from the standard size. Each non-default block size has its own pool. Oracle Database manages the blocks in these pools in the same way as in the default pool. Figure 5-3 shows the structure of the buffer cache when multiple pools are used. The cache contains default, keep, and recycle pools. The default block size is 8 KB. The cache contains separate pools for tablespaces that use the nonstandard block sizes of 2 KB, 4 KB, and 16 KB.

Figure 5-3: Database Buffer Cache

Buffer cache affects performance

You can affect the performance of a database instance by changing the size of the buffer cache. Make it too small, and performance will suffer because of excessive disk I/O. Make it too large, and you will be wasting memory that could be more profitably used.
The ideal is to increase the buffer cache size until you find the point where further increases do not result in any further improvements.