Question about the LRU list in the database buffer cache in the SGA:
Does the LRU (Least Recently Used) list has a size (in terms of blocks or whatever)? Or is there a parameter where you actually specify the size of the LRU list? The book I'm reading says "... Consider the LRU list as a queue of blocks, in which the most recently accessed blocks are always in the front (MRU). The least-used blocks are thrown out of the list when new blocks are accessed and added to the list...." Having that in mind, how does Oracle know when it has to throw out a block from the LRU list? How does oracle know when the list is full?
There is no parameter to specify the size of LRU list. Oracle's internal mechanism manages the blocks in buffer cache. There are two lists is the buffer cache, write list and LRU list. Write list contains the dirty buffers that are to be written to disk. LRU list contains free buffers, pinned buffers and dirty buffers that are not moved yet to write list. The block are moved in and out of the LRU list by LRU algorithm.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
But there is something called the touch count algorithm in 8i and above in which you can make modifications to change the LRU performance.
By default Oracle divides the LRU evenly. That is, the hot region(MRU) is composed of 50% of the buffers and the cold region(LRU) is composed of 50% of the buffers. This can be controlled by manipulating the instance parameter, _db_percent_hot_default. Increasing this instance parameter will increase the percentage of buffers in the hot region, that is, buffers above the midpoint.
There are five relevant touch-count related instance parameters that we can use to our advantage.
_db_percent_hot_default. The percentage of block buffers that reside in the hot region. Default 50 (percent).
_db_aging_touch_time. The window of time where a buffer’s touch count can only be incremented by one. Default 3 (seconds).
_db_aging_hot_creiteria. The threshold when a buffer is being considered to be moved to the MRU end of the LRU chain. Default 2 (touch count).
_db_againg_stay_count. Involved with resetting the touch count when a buffer is moved to the MRU end of the LRU chain. Default 0 (touch count).
_db_aging_cool_count. The re-assigned touch count value when a block moves from the hot region into the cold region. Default 1 (touch count).
Not much is available on the above parameters, so u need to experiment urself to find out what each parameter does and how it would affect your performance.
An excellent document on touch count by Craig A. Shallahamer is available on www.orapub.com
Amar "There is a difference between knowing the path and walking the path."