Fundamentals of the Oracle buffer cache

the principle of Buffer cache

One

1) When a server process needs to read the data into the buffer cache, it must first determine whether the data exists in buffer , if it exists and is available, to get the data, and to move it on the LRU list based on the LRU algorithm . Block; If the data does not exist in the buffer, you will need to retrieve it from the data file

2) before reading the data, theserver process needs to scan the LRU list for the free buffer, and during the scan the server process will move all discovered buffer on the checkpoint queue, these dirty buffer can then be written out to the data file.

3) If the checkpoint queue exceeds the threshold, theserver process notifies dbwn to write dirty data, which is also a condition for starting DBWR writing.

Select KVITTAG,KVITVAL,KBVITDSC from x$kvit where kvittag= ' KCBLDQ ';

if the server process scans LRU more than one threshold and still cannot find enough free buffer , it will stop looking and instead notify dbwn to write dirty data, freeing up space.

Select KVITTAG,KVITVAL,KBVITDSC from x$kvit where kvittag= ' KCBFSP ';

At the same time, due to the introduction of incremental checkpoints, theDBWN process will also actively scan the LRU list, dirty buffer found to move to the checkpoint queue, this scan is also subject to an internal constraint,

Select KVITTAG,KVITVAL,KBVITDSC from x$kvit where kvittag= ' kcbdsp ';

4) After finding enough buffer, theserver process can read buffer from the data file into the buffer cache

5) If the read block does not meet the read consistency requirement, the server process needs to be returned to the user through the current block version and the rollback segment before the mirror is constructed.

FromOracle 8i StartLRU List  and dirty List  introduced auxiliary list for improved management efficiency when the database was initialized, buffer first stored in lru auxiliary List, when used to move to lru  the main list, so that when the user process searches free buffer, first from the auxiliary list search, when DBWR search dirty Buffer when   will first lru  's main list for search, improving search efficiency and database performance.

Ii. dumping the contents of the buffer cache (level 1-10)

Alter Session set events ' immediate trace name buffers level 4 ';

Level 1: contains only the information for buffer headers.

Level 2: Contains buffer headers and buffer summary information dump

Level 3: Contains buffer headers and full buffer content dump.

Third,cache buffer LRU chain latch Competition and solution

When a user process needs to read data to buffer cache, or cache buffer is managed according to the LRU algorithm, it is unavoidable to scan the LRU list for available buffer or change Buffer state. The latch must be acquired during the search process , and the latch of the lock LRU is the commonly seen cache buffers LRU chain

ways to solve the latch competition:

1) Add buffer cache appropriately to reduce the chance of reading data to buffer cache and reduce the competition for scanning LRU list

。

2) can appropriately increase the number of LRU latch , modify the _db_block_lru_latches parameters can be achieved,

3) through the multi-buffer pool technology, you can reduce the impact of unwanted data aging and full table scan operations on The default pool, thus reducing competition.

Iv.Cache buffer chain latch Competition and resolution

1. In addition to the two memory structures of the LRU and checkpoint queue, there aretwo other important data structures in the management of the buffer cache:hash buckets and Cache buffer Chain

2, in order to improveOracleIdentify aBlock inThe position in buffer,Oracle has introducedbucket data structure, oracle to manage all buffer is stored in different xml:lang= >hash" en-US "xml after an internal hash  bucket, hash buckets are segmented, many buffer  is distributed to a certain number of bucket, when the user needs to buffer is located in the presence of data, only the same algorithm to obtain hash value, and then to the corresponding bucket find a small amount of buffer can be determined. Each buffer stored bucket by buffer data block address operation decision.

Inside the Bucket , All of the buffer is connected through the buffer header information via the cache buffer chain (a doubly linked list) .

The Buffer header stores a summary of the corresponding data block, including the data file number, block address, state, and so on. To determine if the data block exists in buffer, check the buffer header for confirmation.

3.the number of buckets is affected by an implied parameter:

db_block_hash_budkets

1)         from oracle 8i, bucket the number is significantly greater than before, through increased bucket dilution makes each bucket on buffer is greatly reduced.

2)         oracle 8i, _db_block_hash_laches the number and The number of span lang= "en-us" xml:lang= "en-US" >bucket is consistent, each latch manages a bucket", from oracle 8i start each Latch manages multiple bucket, due to bucket "en-us" buffer number greatly reduced, so latch performance has been improved.

3) There is one cache buffer per bucket chain

4) A pointer to a specific buffer exists on the header of the buffer.

5) after understanding the above algorithm, it can be imagined that if a large number of processes to the same block process operation, then it will inevitably lead to the cache buffer chain competition, which is often called hot competition.

V.X$BH and buffer header

each buffer has a record in the X$BH.

There is an important field in the X$bh TCH ,TCH is the acronym for Touch, indicating the number of accesses to a buffer, the more times thebuffer is accessed, the more sought That is, there may be a problem with hot spot block competition

queries the current database for the busiest buffer

Select *

From (select Addr,ts#,file#,dbarfil,dbablk,tch

From x$bh

Order by tch Desc)

Where rownum<11;

In conjunction with the information in dba_extents, you can query to these hotspot buffer from those objects.

Select E.owner,e.segmet_name,e.segment_type

From Dba_extents E,

(Select *

From (select Addr,ts#,file#,dbarfil,dbablk,tch

From x$bh

Order by tch Desc)

Where rownum<11)

Where E.RELATIVE_FNO=B.DBARFL

and e.block_id<=b.dbablk

and e.block_id+e.blocks>dbablk;

If you need to determine the Hotspot block object, you can query the specific sub-latch information from the V$latch_children .

There is another key field in the X$BH hladdr the hash chain latch address, this field can be associated with v$latch_child.addr, so that you can put the specific Latch competition and data blocks are associated. Combining the dba_extents view with V$sqlarea or v$sqltext to find SQL that frequently operates on these objects, and then optimize them to mitigate or solve hot-spot competition issues.

Fundamentals of the Oracle buffer cache