一. 官网说明
http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/memory.htm#i10221
The database buffer cache is the portion of the SGA that holds copies of data blocks read from datafiles. All users concurrently connected to the instance share access to the database buffer cache.
This section includes the following topics:
(1)Organization of the Database Buffer Cache
The buffers in the cache are organized in two lists: the write list and the least recently used (LRU) list. The write list holds dirty buffers, which contain data that has been modified but has not yet been written to disk. The LRU list holds free buffers, pinned buffers, and dirty buffers that have not yet been moved to the write list. Free buffers do not contain any useful data and are available for use. Pinned buffers are currently being accessed.
When an Oracle Database process accesses a buffer, the process moves the buffer to the most recently used (MRU) end of the LRU list. As more buffers are continually moved to the MRU end of the LRU list, dirty buffers age toward the LRU end of the LRU list.
The first time an Oracle Database user process requires a particular piece of data, it searches for the data in the database buffer cache. If the process finds the data already in the cache (a cache hit), it can read the data directly from memory. If the process cannot find the data in the cache (a cache miss), it must copy the data block from a datafile on disk into a buffer in the cache before accessing the data. Accessing data through a cache hit is faster than data access through a cache miss.
Before reading a data block into the cache, the process must first find a free buffer. The process searches the LRU list, starting at the least recently used end of the list. The process searches either until it finds a free buffer or until it has searched the threshold limit of buffers.
If the user process finds a dirty buffer as it searches the LRU list, it moves that buffer to the write list and continues to search. When the process finds a free buffer, it reads the data block from disk into the buffer and moves the buffer to the MRU end of the LRU list.
If an Oracle Database user process searches the threshold limit of buffers without finding a free buffer, the process stops searching the LRU list and signals the DBW0 background process to write some of the dirty buffers to disk.
(2)The LRU Algorithm and Full Table Scans
When the user process is performing a full table scan, it reads the blocks of the table into buffers and puts them on the LRU end (instead of the MRU end) of the LRU list. This is because a fully scanned table usually is needed only briefly, so the blocks should be moved out quickly to leave more frequently used blocks in the cache.
You can control this default behavior of blocks involved in table scans on a table-by-table basis. To specify that blocks of the table are to be placed at the MRU end of the list during a full table scan, use the CACHE clause when creating or altering a table or cluster. You can specify this behavior for small lookup tables or large static historical tables to avoid I/O on subsequent accesses of the table.
二. Buffer Cache 说明
Oracle 内存 架构 详解
http://blog.csdn.net/tianlesoftware/archive/2010/05/15/5594080.aspx
Oracle Shared pool 详解
http://blog.csdn.net/tianlesoftware/archive/2011/06/22/6560956.aspx
根据如下blog,整理了一下:
http://blog.csdn.net/robinson1988/archive/2010/11/02/5982996.aspx
http://www.adp-gmbh.ch/ora/misc/dynamic_performance_views.html#bh
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.
2.1 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 v$bh dynamic view has an entry for each block in the buffer cache. The time a block has been touched most recently is recorded in tim of x$bh
2.2 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.
2.3 x$bh
Information on buffer headers. Contains a record (the buffer header) for each block in the buffer cache.
This select statement lists how many blocks are Available, Free and Being Used.
/* Formatted on 2011/6/28 14:34:08 (QP5 v5.163.1008.3004) */
SELECT COUNT (*), State
FROM (SELECT DECODE (state,
0, 'Free',
1, DECODE (lrba_seq, 0, 'Available', 'Being Used'),
3, 'Being Used',
state)
State
FROM x$bh)
GROUP BY state
有关x$bh 的几个字段说明
(1)state:
0 |
FREE |
no valid block image |
1 |
XCUR |
a current mode block, exclusive to this instance |
2 |
SCUR |
a current mode block, shared with other instances |
3 |
CR |
a consistent read (stale) block image |
4 |
READ |
buffer is reserved for a block being read from disk |
5 |
MREC |
a block in media recovery mode |
6 |
IREC |
a block in instance (crash) recovery mode |
(2)tch:
tch is the touch count. A high touch count indicates that the buffer is used often. Therefore, it will probably be at the head of the MRU list.
(3)tim: touch time.
(4)class: represents a value designated for the use of the block.
(5)lru_flag
(6)set_ds : maps to addr on x$kcbwds.
(7)le_addr: can be outer joined on x$le.le_addr.
(8)flag :is a bit array.
Bit |
if set |
|
0 |
Block is dirty |
|
4 |
temporary block |
|
9 or 10 |
ping |
|
14 |
stale |
|
16 |
direct |
|
524288 (=0x80000) |
Block was read in a full table scan |
See this link |
2.4 Different pools within the cache
The cache consists actually of three buffer pools for different purposes.
2.4.1 Keep pool
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.
2.4.2 Recycle pool
The recycle pool is for larger objects. See db_recycle_cache_size.
2.4.3 Default pool
The default pool is for everything else. See also x$kcbwbpd
2.4.4 x$kcbwbpd