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.
Blue buffers 1,3,4,8: Dirty buffer
Blue buffers 1,3,4,8: Dirty buffer
Blue buffer 6: Will be moved to the dirty list
Green buffer 5: The least recently used buffer
Green buffer 7: The most recently used buffer
Green buffer 2: The third most recently used buffer
Dirty List 1,3,4,8: Pointers to the dirty buffers
LRU List 5: Pointer to the least recently used buffer
LRU List 7: Pointer to the most recently used buffer
LRU List 2: Pointer to the third most recently used buffer
LRU List 6: Pointer to the second most recently used buffer
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.