The number of hashed chain lists
is given by the _DB_BLOCK_HASH_BUCKETS init.ora parameter.
: 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
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.
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.