What is Buffer Cache?
Database Buffer Cache is the largest component of SGA. The first time an Oracle user process requires a particular piece of data, it searches for the data in the database buffer cache. If the process finds the data already in the cache (a cache hit), it can read the data directly from memory. If the process cannot find the data in the cache (a cache miss), it must copy the data block from a datafile on disk into a buffer in the cache before accessing the data. Accessing data through a cache hit is faster than data access through a cache miss. The larger the buffer cache, the fewer the disk reads and writes needed and the better the performance of the database. Therefore, properly sizing the buffer cache is very important for the proper performance of your database.
The buffers in the cache are organized in two lists: the dirty list and the LRU (Least
recently used) list.
The dirty list holds dirty buffers, which contain data that has been modified but has
not yet been written to disk.
recently used) list.
The dirty list holds dirty buffers, which contain data that has been modified but has
not yet been written to disk.
The LRU (Least Recently Used) list holds free buffers, pinned buffers, and dirty buffers that have not yet been moved to the dirty list.
LRU List: It has two end- MRU end and LRU end. This list follows Touch Count Algorithm. The database measures the frequency of access of buffers on the LRU list using a touch count algorithm. This increments a counter when a buffer is pinned instead of constantly shuffling buffers on the LRU list.
When a buffer is pinned, the database determines when its touch count was last incremented. If the count was incremented over three seconds ago, then the count is incremented, otherwise, the count stays the same. The three-second rule prevents a burst of pins on a buffer counting as many touches. For example, a session may insert several rows in a data block, but the database considers these inserts as one touch.
If a buffer is on the cold end of the LRU, but its touch count is high, then the buffer moves to the hot end. If the touch count is low, then the buffer ages out of the cache. When a server process reads a block from disk into the buffer cache, it is placed in the middle of LRU chain that is between hot region and cold region which is called as mid-point insertion.
Frequent access to these block will increase there touch count and block will move towards towards the head of the LRU chain on the hot region.
When a server process reads a block from disk into the buffer cache, it is placed in the middle of LRU chain that is between hot region and cold region which is called as mid-point insertion.
Frequent access to these blocks will increase their touch count and block will move towards the head of the LRU chain on the hot region.
What are types of buffers ?
- Dirty buffers in LRU List: These are modified buffers which have not yet been written to dirty list.
- Dirty buffers in Dirty List: These are modified buffers which have not yet been written to disk.
- Free or unused buffers: These buffers are free and ready to use.
- Pinned Buffers: These buffers are currently being accessed. These are data buffers that are currently in active use by user sessions.
- Clean Buffers: These buffers contain some data but it is sync with block content stored in datafiles, so there is no need to write these buffer to disk.
- Undo buffers: It contains previous image of the changed block.
What are types of Pools ?
- Keep Pool: Small tables with full table scans that are very frequently used or in high demand which are considered as good candidate for KEEP pool so that disk I/O will be minimized and the performance will be maximized Keeping small tables in memory will keep data blocks in memory which will prevent them from being aged out of the database buffer cache.
The thumb rule is that a good candidate for a segment to put into the KEEP pool is a segment that is smaller than 10% of the size of the DEFAULT buffer pool and has incurred at least 1% of the total I/Os in the system.
The parameter used to create Keep buffer Pool is DB_KEEP_CACHE_SIZE.
- Recycle Pool: This pool is used for large tables that are infrequently accessed and not needed to be retain in the buffer cache. Blocks loaded in Recycle Buffer pool are removed immediately when they are not being used. It is useful for those objects which are accessed rarely. As there is no more need of these blocks so memory occupied by such blocks is made available for others data. DB_RECYCLE_CACHE_SIZE initialization parameter is used to create Recycle buffer Pool.
- Default Pool: If an object is not assigned a any buffer pool then the blocks are loaded in default pool. The keep and the recycle buffer pools are purely optional, while the default buffer pool is mandatory. DB_CACHE_SIZE initialization parameter is used to create Default Pool.
What is Buffer Cache Hit Ratio ?
The buffer cache hit ratio is how often a requested block has been found in the buffer cache without requiring disk access and this ratio is computed using data selected from the view V$SYSSTAT. The more the ratio less the I/O requests and better the performance.
The buffer cache hit ratio is how often a requested block has been found in the buffer cache without requiring disk access and this ratio is computed using data selected from the view V$SYSSTAT. The more the ratio less the I/O requests and better the performance.
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME IN ('db block gets from cache', 'consistent gets from cache', 'physical reads cache');
1 - (('physical reads cache') / ('consistent gets from cache' + 'db block gets from cache')
OR
hit rate = (1 – (physical reads)/(logical reads)) * 100
where,
consistent gets from cache: Number of times a consistent read was requested for a block from the buffer cache.
db block gets from cache: Number of times a current block was requested from the buffer cache.
physical reads cache: Total number of data blocks read from disk into buffer cache.
db block gets from cache: Number of times a current block was requested from the buffer cache.
physical reads cache: Total number of data blocks read from disk into buffer cache.
Oracle accesses blocks in one of two modes:
The keep and recycle buffer pools can only be created with the standard block size, but we can use up to five different block sizes to configure the default buffer pool.
- Current - The most up-to-date copy of the data in that block or current or right now. There can only be one current copy of a block in the buffer cache at any time.
- Consistent - It is when Oracle gets the data in a block which is consistent with a given point in time, or SCN. The consistent get is at the heart of Oracle's read consistency mechanism.
Tablespaces with different block sizes:
We can have up to five different database block sizes in your databases i.e. we can create our tablespaces with any one of the five allowable database block sizes. This allows large tables and indexes to have a larger block size than smaller objects. This is especially useful in DSS environment benefits from large block sizes, whilst OLTP operations are best suited for smaller block sizes. Although most databases use only a single standard block size such as 4KB, 8KB, or 16KB, we can choose to use some or all of the four nonstandard block sizes as well.
For example, we may have some data warehouse–type tables that will benefit from a high database block size, such as 32KB. We can specify the nonstandard buffer cache subcaches by using the DB_nK_CACHE_SIZE initialization parameter, where n is the block size in kilobytes—it can take a value of 2, 4, 8, 16, or 32. It also means that tablespaces can be transported between databases with differing block sizes. The database is created with a standard block size and up to 5 non-standard block sizes. The DB_CACHE_SIZE parameter, rather than DB_BLOCK_BUFFERS, is used to define the size of the buffer cache for the standard block size. The DB_CACHE_SIZE must be at least 1 granule in size and defaults to 48M. The DB_BLOCK_SIZE is used to specify the standard block size which is used for the SYSTEM and TEMPORARY tablespaces.
The keep and recycle buffer pools can only be created with the standard block size, but we can use up to five different block sizes to configure the default buffer pool.
Example:
DB_KEEP_CACHE_SIZE = 48MB
DB_RECYCLE_CACHE_SIZE = 24MB
DB_CACHE_SIZE = 128MB /* standard 4KB block size */
DB_2k_CACHE_SIZE =48MB /* 2KB nonstandard block size */
DB_8k_CACHE_SIZE =192MB /* 8KB nonstandard block size */
DB_16k_CACHE_SIZE = 384MB /* 16KB nonstandard block size */
The total buffer cache size in the above example will be the sum of all the subcaches which is 48 + 24 + 128 + 48 + 192 + 384 = 824MB.
DB_nK_CACHE_SIZE (where n = 2, 4, 8, 16, 32) specifies the size of the cache for the nK buffers. We can set this parameter only when DB_BLOCK_SIZE has a value other than nK. For example, if DB_BLOCK_SIZE=4096, then it is illegal to specify the parameter DB_4K_CACHE_SIZE because the size for the 4 KB block cache is already specified by DB_CACHE_SIZE. Do not set this parameter to zero if there are any online tablespaces with an nK block size.
Operating system-specific block size restrictions apply. For example, we cannot set DB_32K_CACHE_SIZE if the operating system's maximum block size is less than 32 KB. Also, you cannot set DB_2K_CACHE_SIZE if the minimum block size is greater than 2 KB.
DB_BLOCK_BUFFERS specifies the number of database buffers in the buffer cache. Don’t set the number of database buffers; rather, you specify the size of the buffer cache itself in the DB_CACHE_SIZE parameter.
The DB_BLOCK_SIZE parameter determines the size of your standard block size in the database and frequently is the only block size for the entire database.
The DB_CACHE_SIZE parameter specifies the size in bytes of the cache of the standard block sized buffers.
Thanks for reading this post ! Please comment if you like this post !
Excellent and detailed explanation.
ReplyDelete