René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

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.

Purpose

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.

Segments

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

Buffer

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

MRU and LRU blocks

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

Different pools within the cache

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

Keep pool

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

Recycle pool

The recycle pool is for larger objects.

Default pool

The default pool is for everything else.
See also x$kcbwbpd

Cold and hot area

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.

Touch count

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.

Flushing the cache

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

Optimal Size

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.

v$bh

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