Describe how the database buffer cache manages data blocks.
Oracle 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. The following diagram describes how buffers are being used.
Oracle database buffer cache
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
Database Buffer Cache
The database buffer cache is an area in the SGA used to hold the data blocks that are read from the data segments in the database, such as tables and indexes. The size of the database buffer cache is determined by the DB_CACHE_SIZE parameter (expressed in terms of number of bytes) in the initialization parameter file for the database. The default size for the database blocks is set via the DB_BLOCK_SIZE parameter specified in the parameter file during database creation. Managing the size of the database buffer cache is an important part of managing and tuning the database. The database has a default block size, but you can establish cache areas for different database block sizes and then create tablespaces to use those caches. For example, you can create a 4KB block size database with some tablespaces set to 8KB. The size of the 8KB cache would be set via the DB_8K_CACHE_SIZE parameter. To create tablespaces to use that cache, specify blocksize 8K as part of the create tablespace command. If the default block size for the database is 4KB, you would not set a value for DB_4K_CACHE_SIZE; the size specified for DB_CACHE_SIZE would be used for the 4KB cache. Note: The cache for the block size must exist before you create a tablespace that uses that block size.
The different cache areas can be resized while the database is running. Caches can only be made larger if unallocated memory is available.
The caches must be increased or decreased in granules. For a database with an SGA less than 128M, the granule size is 4M,
so DB_8K_CACHE_SIZE can be 4M, 8M, 12M, and so on. If you attempt to use any other setting, Oracle will round it up to the next granule size.
To see if memory is available, query the V$SGA_DYNAMIC_FREE_MEMORY view.
alter system set DB_8K_CACHE_SIZE = 8m;
If you create a tablespace that uses a nondefault database block size, you must be sure that the related cache size parameter (such as DB_8K_CACHE_SIZE) is updated in your database parameter file. If you are using an init.ora file, you must update it with the new value. If you are using a system parameter file (the preferred method), it will be automatically updated when you execute the alter system command with the scope=both clause.
The dirty list[1] 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.
The image illustrates the Database Buffer Cache structure in an Oracle database, highlighting different buffer cache pools and block sizes.
Title:
"Database Buffer Cache" is prominently displayed at the top, indicating that the diagram explains how Oracle manages its buffer cache.
Default Buffer Pool:
A large grid labeled "Default" represents the primary buffer cache.
Some blocks (cells) are shaded, suggesting that they contain data currently in use or pinned.
Additional Buffer Pools:
Smaller grids represent specialized buffer caches, each labeled accordingly:
2K: A dedicated buffer cache for 2K-sized blocks.
4K: A dedicated buffer cache for 4K-sized blocks.
16K: A dedicated buffer cache for 16K-sized blocks.
Keep: A buffer cache that retains frequently accessed data longer.
Recycle: A buffer cache for data that should be quickly discarded.
Shading in Blocks:
Some blocks in each buffer pool are shaded, indicating they are occupied by data.
The shading likely represents cached data blocks, emphasizing the selective allocation of memory resources.
Purpose:
It showcases Oracle’s multiple buffer pools and how memory can be managed using different block sizes and caching strategies.
Significance of Pools:
The default pool handles most operations.
The Keep pool is optimized for frequently used data, ensuring retention.
The Recycle pool is optimized for transient data to minimize cache pollution.
The block size caches (2K, 4K, 16K) enable efficient caching for tablespaces with varying block sizes.
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.
[1]dirty list: The "dirty list" within the context of the Oracle database buffer cache is not a physically separate list but rather a conceptual distinction within the Least Recently Used (LRU) list. It's crucial to understand that the LRU list itself holds all buffers in the cache, both "clean" and "dirty."
