Logo Background

Tuning Oracle Buffer Cache

  • By on August 22, 2008 | No Comments

    Tuning the Buffer Cache Using Multiple Buffer Pools

    When you partition your buffer cache into multiple buffer pools, each buffer pool can be used for blocks from objects that are accessed in different ways. If the blocks of a particular object are likely to be reused, then you should keep that object in the buffer cache so that the next use of the block will not require another disk I/O operation. Conversely, if a block probably will not be reused within a reasonable period of time, there is no reason to keep it in the cache; the block should be discarded to make room for a more popular block.
    By properly allocating objects to appropriate buffer pools, you can:
    •    reduce or eliminate I/Os
    •    isolate an object in the cache
    •    restrict or limit an object to a part of the cache

    Enabling Multiple Buffer Pools

    You can create multiple buffer pools for each database instance. The same set of buffer pools need not be defined for each instance of the database. Between instances a buffer pool may be different sizes or not defined at all. Each instance should be tuned separately.

    Defining New Buffer Pools

    You can define each buffer pool using the BUFFER_POOL_name initialization parameter. You can specify two attributes for each buffer pool: the number of buffers in the buffer pool and the number of LRU latches allocated to the buffer pool.
    The initialization parameters used to define buffer pools are:

    BUFFER_POOL_KEEP              Defines the keep buffer pool.

    BUFFER_POOL_RECYCLE       Defines the RECYCLE buffer pool.

    DB_BLOCK_BUFFERS              Defines the number of buffers for the database instance. Each individual buffer pool is created from this total amount with the remainder allocated to the default buffer pool.

    DB_BLOCK_LRU_LATCHES     Defines the number of LRU latches for the entire database instance.Each buffer pool defined takes from this total in a fashion similar to DB_BLOCK_BUFFERS.

    For example:
    BUFFER_POOL_KEEP=(buffers:400, lru_latches:3″`)
    BUFFER_POOL_RECYCLE=(buffers:50, lru_latches:1″`)

    The size of each buffer pool is subtracted from the total number of buffers defined for the entire buffer cache (that is, the value of the DB_BLOCK_BUFFERS parameter). The aggregate number of buffers in all of the buffer pools cannot, therefore, exceed this value. Likewise, the number of LRU latches allocated to each buffer pool is taken from the total number allocated to the instance by the DB_BLOCK_LRU_LATCHES parameter. If either constraint is violated then an error occurs and the database is not mounted.

    The minimum number of buffers that you must allocate to each buffer pool is 50 times the number of LRU latches. For example, a buffer pool with 3 LRU latches must have at least 150 buffers.

    Oracle8 defines three buffer pools: KEEP, RECYCLE, and DEFAULT. The default buffer pool always exists. It is equivalent to the single buffer cache in Oracle7. You do not explicitly define the size of the default buffer pool and number of working sets assigned to the default buffer pool. Rather, each value is inferred from the total number allocated minus the number allocated to every other buffer pool. There is no requirement that any buffer pool be defined for another buffer pool to be used.

    » Oracle Date To Day Conversion
Leave a Comment