3 Buffers

3 Buffers

JL Computer Consultancy

In version 7 of Oracle, we were given limited support for keeping specificdata objects in the Oracle buffer (see my notes on Cachingin Oracle 7) and protecting them from the normal LRU (least recently used)algorithm that Oracle applies to keep recently used blocks in memory at thecost of flushing other blocks.

Oracle 8 has introduced the idea of 3 separate buffer pools which, ineffect, offer 3 separate LRU chains so that objects with differentcharacteristic use can age out of the buffer in different ways.

The three buffer pools are named the Default pool, the Keep pool, and theRecycle pool. At their names are really fairly meaningless and are intended (Ibelieve) as an indication of how the concept of multiple buffer pools can beused - it is possible that at some future version (perhaps when the horrid bug - see below - is removed) they will befunctionally different.

To use the three buffer pools, you have to take the following steps.

Use multiple db_block_lru_latchesAssign a number of blocks and latches to the two 'non-default' poolsNominate some objects as users of the two 'non-default' pools.

For example, in the init.ora you might have:

db_block_buffers = 1000db_block_lru_latches = 5buffer_pool_keep = (200:1)      # 200 blocks, 1 latchbuffer_pool_recycle = (50:1)    # 50 blocks 1 latch# the default buffer pool would then have 750 blocks and 3 latches

There appear to be two different valid ways of using the buffer_poolparameters: as above (which I discovered by accident when I forgot to read themanual) and the self-documenting method shown in the Oracle 8 performanceTuning Guide of:

(Corrected Jan 2006 after a note from HansWijte)

buffer_pool_keep = (buffers:200, lru_latches:1)             # 200 blocks, 1 latch buffer_pool_recycle = (buffers:50, lru_latches:1)           # 50 blocks 1 latch

After restarting the database part of your application must executestatements like:

alter table little_scanner storage (buffer_pool keep);alter table big_random storage (buffer_pool recycle);alter index big_random_pk storage (buffer_pool keep);

The selection of table and index names indicatespossible strategic uses for the buffer pools. (Note especially that we now havesome control in Oracle 8 over preferential buffering of indexes. In version 7we 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 itto the KEEP pool so that it is not flushed out by activity on the rest of thebuffer; similarly I have an index on a big lookup table that I would like toprotect 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 objectswill fit with a small spare capacity).

On the other hand my big, randomly accessed table is so big, and accessed sorandomly that I hardly ever expect to benefit from buffering it since theprobability of wanting the same block twice in a short period of time is verylow. To make sure that the random I/Os to this large table do not have adetrimental 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 youhave a strict memory limit you may be able to gain quite a significant benefitfrom 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 yourapplication.

For the future, the use of the 3 buffer pools will be the desired mechanismfor 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 Oracle8.1. Oracle Corp. still have a little work to do on the KEEP buffer, though, asit is not possible to load a smallish table into the buffer by scanning itunless you use the CACHE option, or rig the small table threshold - since mycopy of 8.1.5 for HP-UX has just arrived I may soon be able to tell you whatthey have done.

In fact, at this point I offer a BUGWARNING: My copy of 8.0.4.0 on NT seems to have a bug - if youdeclare a table to be a CACHEd table, then scan it,the scan is completely unlimited - even if the table is largerthan the db_block_buffers Oracle pulls every blockinto MRU end of the relevant buffer with the result that every other block(including dirty blocks and rollback blocks) gets flushed. Be very cautiousabout using CACHEd tables in 8.0.4.0 - switch to the3 buffers.

I will be extending this article at a later date to describe how to use someof the v$ and x$ objects to monitor the effects of using the multiple buffers.