The 3 buffers in Oracle 8
In version 7 of Oracle, we were given limited support for keeping specific data objects in the Oracle buffer (see my notes on Caching in Oracle 7) and protecting them from the normal LRU (least recently used) algorithm that Oracle applies to keep recently used blocks in memory at the cost of flushing other blocks.
Oracle 8 has introduced the idea of 3 separate buffer pools which, in effect, offer 3 separate LRU chains so that objects with different characteristic use can age out of the buffer in different ways.
The three buffer pools are named the Default pool, the Keep pool, and the Recycle pool. At their names are really fairly meaningless and are intended (I believe) as an indication of how the concept of multiple buffer pools can be used - it is possible that at some future version (perhaps when the horrid bug - see below - is removed) they will be functionally different.
To use the three buffer pools, you have to take the following steps.
For example, in the init.ora you might have:
There appear to be two different valid ways of using the buffer_pool parameters: as above (which I discovered by accident when I forgot to read the manual) and the self-documenting method shown in the Oracle 8 performance Tuning Guide of:
(Corrected Jan 2006 after a note from Hans Wijte)
After restarting the database part of your application must execute statements like:
The selection of table and index names indicates possible strategic uses for the buffer pools. (Note especially that we now have some control in Oracle 8 over preferential buffering of indexes. In version 7 we could only affect the way in which tables were buffered)
I have a small table that I want to scan quite frequently, so I allocate it to the KEEP pool so that it is not flushed out by activity on the rest of the buffer; similarly I have an index on a big lookup table that I would like to protect from normal buffer activity so I allocate that to the keep pool as well (for optimum effect I have to size the KEEP pool so that both these objects will fit with a small spare capacity).
On the other hand my big, randomly accessed table is so big, and accessed so randomly that I hardly ever expect to benefit from buffering it since the probability of wanting the same block twice in a short period of time is very low. To make sure that the random I/Os to this large table do not have a detrimental effect on the rest of the buffer I allocate it to the RECYCLE pool, so that only those 50 blocks of buffer are used when I access the big_random table.
The facility is there and in special cases where you have a strict memory limit you may be able to gain quite a significant benefit from it (10 - 20% in extreme cases), but it does require some arithmetic, business knowledge (and trial and error) to find out if it is relevant to your application.
For the future, the use of the 3 buffer pools will be the desired mechanism for gaining extra control over the way that Oracle keeps objects in memory - the 'cache_size_threshold' and '_small_table_threshold' of Oracle 7 are already 'deprecated features' and will be obsolete in Oracle 8.1. Oracle Corp. still have a little work to do on the KEEP buffer, though, as it is not possible to load a smallish table into the buffer by scanning it unless you use the CACHE option, or rig the small table threshold - since my copy of 8.1.5 for HP-UX has just arrived I may soon be able to tell you what they have done.
In fact, at this point I offer a BUG WARNING: My copy of 126.96.36.199 on NT seems to have a bug - if you declare a table to be a CACHEd table, then scan it, the scan is completely unlimited - even if the table is larger than the db_block_buffers Oracle pulls every block into MRU end of the relevant buffer with the result that every other block (including dirty blocks and rollback blocks) gets flushed. Be very cautious about using CACHEd tables in 188.8.131.52 - switch to the 3 buffers.
I will be extending this article at a later date to describe how to use some of the v$ and x$ objects to monitor the effects of using the multiple buffers.