Oracle Memory Architecture detailed (ii) Oracle management buffer Cache

Buffer Cache is an area of the SGA area that is specifically used to store copies of blocks of data that are read from the data file. If the Oracle process finds that a block of data that needs to be accessed is already in the buffer cache, it reads directly to the appropriate area in memory without having to read the data file, which greatly improves performance (memory reads more than 14,000 times times the efficiency of the disk reads). The Buffer cache is shared by all Oracle processes and is accessible to all Oracle processes.

Like shared pool, buffer cache is divided into multiple sets, which can greatly reduce contention issues in multiple-CPU systems.

1, Buffer Cache management

Oracle's management of buffer cache is achieved through two important linked lists: The Write list and the most recently used list (the least recently Used LRU). The Write list refers to all dirty block caches (that is, a block of data that has been modified by the process but has not been written back to the data file, in which case the data in the buffer is inconsistent with the data in the data file). The LRU list points to all idle caches, pin-live caches, and dirty caches that have not yet come and moved into the write list. There is no useful data available at any time in the idle cache. and the pin-live cache is the cache that is currently being accessed. The ends of the LRU list are called the most recent use (The Most recently Used MRU) and the most recent least used end (LRU).

(1) Buffer cache data block access

When an Oracle process accesses a cache, the process moves the cache to the MRU in the LRU list. As more and more buffer blocks are moved to the MRU side, the outdated dirty buffers (i.e., data changes that have been written to the data file, where the data in the buffer and in the data file are consistent) are moved to the LRU end of the LRU list.

When an Oracle user process accesses a block of data for the first time, it first looks for a copy of the data block in the buffer cache. If the data block is found to exist in the buffer cache (that is, hit cache hit), it is read directly from memory to fetch the data block. If the data block is not found in the buffer cache (that is, the cache miss is not hit), it needs to read the block of data from the data file to the buffer cache before accessing the data block. The ratio of hit times to process reads is one of our key metrics for measuring database performance: Buffer hit ratio (buffer hit Ratio), which can be obtained from the instance of the buffer hit by the following statement:

Sql>select (SUM (decode (name, ' physical reads ', value,0))/(SUM (Decode (name, ' db block gets ', value,0))

+sum (Decode (name, ' consistent gets ', value,0)))) "Hit Ratio" from V$sysstat;

Hit Ratio

----------

98.3471481

A good performance of the system, the hit rate is generally maintained at about 95%.

For the hit ratio, refer to my blog:oracle to check the hit ratio sql

Http://blog.csdn.net/tianlesoftware/archive/2009/10/16/4674153.aspx

As mentioned above, if you miss a hit (missed), you need to read the block to the cache first. At this point, the Oracle process needs to find a suitable size free cache from the free list species. If there is no free buffer in the free list for size, it will start looking for LRU lists from the LRU end until a reusable cache block is found or the maximum number of lookup blocks is reached. During the lookup process, if the process finds a dirty cache block, it moves the cache block to the Write list, and then continues searching. When it finds an idle block, it reads the block of data from the disk into the cache block and moves the cache block to the MRU end of the LRU list.

When a new object requires a request to allocate buffer, the allocated free or reusable buffer is requested through the memory management module. "Free buffer requested" is the number of times this request was generated;

When the request is allocated buffer, there is no suitable size of free buffer, you need to obtain from the LRU list of reusable buffer. However, the buffer on the LRU list is not all immediately reusable, and there are some blocks being read or written or already waiting for another user. According to the LRU algorithm, find reusable buffer from the LRU end of the list to find, if this paragraph in front of this can not understand the reused buffer, you need to skip to find the next buffer in the list. "Free buffer inspected" is the number of buffer that was skipped.

If the Oracle user process reaches the limit of the lookup block and does not find an idle cache, it stops looking for the LRU list and writes the dirty cache to disk through the signal gay DBW0 process.

(2) Full table scan

When a full table scan is occurring, the user process reads the data blocks of the table and places them on the LRU side of the LRU list (unlike the above, not on the MRU side). Scan The purpose of this is to get the entire table scanned data removed as quickly as possible. Because all-table scans generally occur at a low frequency, and most of the data blocks scanned in the entire table are not used frequently in the future.

And if you want the entire table to be scanned for data that can be placed on the MRU side of the scan, you can specify the cache parameter when creating or modifying a table (or cluster).

(3) Flush Buffer

Review the previous user process access to a block of data, if the access data block is not in buffer cache, you need to scan the LRU linked list, when the number of scanning block limit has not found a free buffer, you need to notify DBW0 to write dirty cache back to disk. If there is a large amount of dirty buffering in a system, then the user process can be degraded to access data.

We can write all the dirty buffers back to disk via human intervention, which is flush buffer.

In 9i, you can use the following statement:

Alter system SET events = ' Immediate trace name Flush_cache '; --9i

In the 10g, can be in the following way (9i of the way in 10g still valid):

alter system flush Buffer_cache; --10g

In addition, the 9i setting event can be done either for the system as a whole or for the session (the dirty buffer that is causing the session to be written back).

2, Buffer cache of important parameters configuration

(1) Buffer Cache size Configuration

Because the buffer cache is a copy of a block of data from a data file, its size is calculated based on the size of the block. The size of the data block is specified by the parameter db_block_size. After 9i, the size of the block defaults to 8K, and its value is generally set to the same size as the operating system block or its multiples.

The parameter db_block_buffers specifies the number of cache blocks in the buffer cache. Therefore, the buffer cache size is equal to Db_block_buffers * db_block_size.

After 9i, Oracle introduced a new parameter: Db_cache_size. This parameter can directly specify the size of the buffer cache without having to be computed by the above method. Its default value of 48M, which is generally not enough for a system.

Automatic memory management is provided in 10G to automatically allocate memory between multiple components by using Sga_target to ensure the most efficient memory usage. As Shared pool Javapool largepool buffer cache does not need to explicitly set the size of these components, The default is 0, which allows memory transfers to be requested through an internal automatic adjustment mechanism when a component requires memory.

Note: Db_cache_size and db_block_buffers cannot be set at the same time, otherwise an error occurs when the instance is started.

Sql> alter system set db_block_buffers=16384 Scope=spfile;

System altered.

Sql> alter system set db_cache_size=20m scope=memory;

System altered.

sql> Startup force

Ora-00381:cannot use both the new and old parameters for buffer cache size specification