Oracle's buffer cache

The buffer cache is part of the SGA. It holds copies of data blocks so as they can be accessed quicker by oracle than by reading them off disk.

The purpose of the buffer cache is to minimize physical io. When a block is read by Oracle, it places this block into the buffer cache, because there is a chance that this block is needed again. Reading a block from the buffer cache is less costly (in terms of time) than reading it from the disk.

The database buffer cache (as well as the shared sql cache are logically segmented into multiple sets. This organization reduces contention on multiprocessor systems.

The buffer cache consists of... buffers. A buffer is a database block that happens to be in memory.,

Blocks within the buffer cache are ordered from MRU (most recently used) blocks to LRU (least recently used) blocks. Whenever a block is accessed, the block goes to the MRU end of the list, thereby shifting the other blocks down towards the LRU end. When a block is read from disk and when there is no buffer available in the db buffer cache, one block in the buffer cache has to "leave". It will be the block on the LRU end in the list.

However, blocks read during a full table (multi block reads are placed on the LRU side of the list instead of on the MRU side.

The time a block has been touched most recently is recorded in tim of x$bh

The cache consists actually of three buffer pools for different purposes.

The keep pool's purpose is to take small objects that should always be cached, for example Look Up Tables.

See db_keep_cache_size.

The recycle pool is for larger objects.

See db_recycle_cache_size.

The default pool is for everything else.

See also x$kcbwbpd

Each pool's LRU is divided into a hot area and a cold area. Accordingly, buffers with in the hot area are hot buffers (and buffers in the cold are are called cold buffers).

By default, 50% of the buffers belong to the cold area and the other 50% belong to the hot area. This behaviour can be changed with _db_percent_hot_default (for the default pool) _db_percent_hot_recycle (for the recycle pool) and _db_percent_hot_keep (for the keep pool).

A newly read db block will be inserted between the cold and the hot area such that it belongs to the hot area. This is called midpoint insertion. However, this is only true for single block reads, multi block reads will be placed at the LRU end.

Each buffer has an associated touch count. This touch count might be increased if a buffer is accessed (although it needs not always be). It is valid to claim that the higher the touch count, the more important (more used) the buffer. Therefore, buffers with a high touch count should stay in the buffer cache while buffers with a low touch count should age out in order to make room for other buffers. A touch time can only be increased once within a time period controlled by the parameter _db_aging_touch_time (default: 3 seconds).

The touch count is recorded in the tch column of x$bh.

By the way, it looks like Oracle doesn't protect manipulations of the touch count in a buffer with a latch. This is interesting because all other manipulations on the LRU list are protected by latches. A side effect of the lack of latch-protection is that the touch count is not incremented if another process updates the buffer header.

With Oracle 10g it is possible to flush the buffer cache with alter system flush buffer_cache.

Some common wisdom says that the larger the buffer cache is, the better the performance of the database becomes. However, this claim is not always true.

To begin with, the cache needs to be managed. The bigger the cache, the larger the LRU and dirty list becomes. That results in longer search times for a free buffer (buffer busy waits.

Also, the bigger the cache, the greater the burden on the DBWn process.

The v$bh dynamic view has an entry for each block in the buffer cache.