Lesson 11
Tuning the Oracle Data Buffer Cache Conclusion
We have covered a huge amount of material in this module and now that you have completed this module, you should be able to:
- Describe the components of the Oracle data buffer
- Calculate the buffer hit ratio
- Predict the amount of spare memory
- Define multiple data buffers
- Configure multiple buffer pools
- Run a script to display data buffer hit ratio
- Issue the cache directive on a table
Tuning Oracle Instance Glossary Terms
Here are some terms from this module that may have been new to you:
- buffer hit ratio: The ratio of logical to physical disk reads
- logical disk read: A logical read is a request from a program for a record.
- physical disk reads: A request from a database for a record.
How buffer cache is organized
The number of hashed chain lists is given by the _DB_BLOCK_HASH_BUCKETS init.ora parameter.
NOTE: This is an internal Oracle parameter. Do NOT use it unless instructed to do so by Oracle Support. Playing with this parameter may be harmful.
Each element of the LRU and LRUW lists point to any one buffer header within the chain list. A buffer can be in the 1)LRU list or 2) LRUW list[1] but not in both. When a process is looking for a free buffer it starts scanning from the LRU list tail (also called least recently used end). If a free buffer is found then the block is read into it and the buffer is placed at the LRU list head . The LRU list head is also called the most recently used end, or MRU.
There is a particular case when new used buffers are placed at the LRU end of the list; this is when new buffer comes from blocks read through a full table scan and the table is not considered to be a small table. The init.ora parameter _SMALL_TABLE_THRESHOLD is used by Oracle to determine whether or not a table is handled as a small table. By default this parameter is initialized to max(4, DB_BLOCK_BUFFERS/50); in other words, a table is considered a small table if the number of blocks that it has is less that the 2% of the buffer cache size.
For example, if the buffer cache has 1000 blocks (DB_BLOCK_BUFFERS = 1000) then a small table must have 20 or less blocks. Hence new used buffers that come from a table larger that 20 blocks are placed at the LRU end of the list to force this buffer to be moved out quickly from the cache, letting other more frequently used buffers to stay in memory. Figure 4-11 depicts how a buffer cache is organized.
NOTE: This is an internal Oracle parameter. Do NOT use it unless instructed to do so by Oracle Support. Playing with this parameter may be harmful.
Each element of the LRU and LRUW lists point to any one buffer header within the chain list. A buffer can be in the 1)LRU list or 2) LRUW list[1] but not in both. When a process is looking for a free buffer it starts scanning from the LRU list tail (also called least recently used end). If a free buffer is found then the block is read into it and the buffer is placed at the LRU list head . The LRU list head is also called the most recently used end, or MRU.
There is a particular case when new used buffers are placed at the LRU end of the list; this is when new buffer comes from blocks read through a full table scan and the table is not considered to be a small table. The init.ora parameter _SMALL_TABLE_THRESHOLD is used by Oracle to determine whether or not a table is handled as a small table. By default this parameter is initialized to max(4, DB_BLOCK_BUFFERS/50); in other words, a table is considered a small table if the number of blocks that it has is less that the 2% of the buffer cache size.
For example, if the buffer cache has 1000 blocks (DB_BLOCK_BUFFERS = 1000) then a small table must have 20 or less blocks. Hence new used buffers that come from a table larger that 20 blocks are placed at the LRU end of the list to force this buffer to be moved out quickly from the cache, letting other more frequently used buffers to stay in memory. Figure 4-11 depicts how a buffer cache is organized.
The way information behaves in memory can be changed by using the CACHE clause during segment
CREATION (ALTER TABLE / CREATE TABLE CACHE option) or with a CACHE hint in a query block. For example:
Blocks of a table having the CACHE attribute on are stored on the MRU end of the LRU list during full table scans. Therefore, this option should be used only for small tables to prevent all other buffers from being moved out from the cache. The CACHE clause was introduced as a first attempt to better manage segments depending on their replacement policy and based on object(table) level control. However, it is still possible that a buffer for a CACHED segment be aged out from the buffer cache when a large (say twice the size of buffer cache) table is updated and blocks goes through the buffer cache. In other words, the CACHE option on the table does not guarantee that a table will be cached in the buffer pool. A process needs to acquire a latch whenever it needs to access any of the lists described before. A latch is a low level serialization mechanism used to protect a region of code from concurrent execution. These are often used when the code needs to access shared data in the SGA but is performing an operation which needs to be done atomically. Both LRU and LRUW are protected by the same latch called the LRU LATCH.
CREATION (ALTER TABLE / CREATE TABLE CACHE option) or with a CACHE hint in a query block. For example:
SQL> ALTER TABLE dept CACHE; SQL> SELECT /*+ CACHE(a) */ ename, deptno FROM emp a;
Blocks of a table having the CACHE attribute on are stored on the MRU end of the LRU list during full table scans. Therefore, this option should be used only for small tables to prevent all other buffers from being moved out from the cache. The CACHE clause was introduced as a first attempt to better manage segments depending on their replacement policy and based on object(table) level control. However, it is still possible that a buffer for a CACHED segment be aged out from the buffer cache when a large (say twice the size of buffer cache) table is updated and blocks goes through the buffer cache. In other words, the CACHE option on the table does not guarantee that a table will be cached in the buffer pool. A process needs to acquire a latch whenever it needs to access any of the lists described before. A latch is a low level serialization mechanism used to protect a region of code from concurrent execution. These are often used when the code needs to access shared data in the SGA but is performing an operation which needs to be done atomically. Both LRU and LRUW are protected by the same latch called the LRU LATCH.
General Buffer Concepts - Quiz
Before moving on to the next module, click the Quiz link below to assess your understanding of the Oracle data buffer cache.
General Buffer Concepts - Quiz
General Buffer Concepts - Quiz
[1]LRUW: LRUW is an acronym for "Least Recently Used Write". LRUW list:
The LRUW list consists of dirty buffers which need to be written to disk. The dirty list is also called the write list or LRUW.