Buffer cache Experiment 5-latch:cache buffers chain

The principle of 1.CBC latch production:

The process of acquiring and releasing a CBC latch lock and a buffer pin lock at one logical reading is as follows:

1. Add Latch X

2. Enter the hash chain, add the buffer pin S (0-->1) on the corresponding BH

3. Release latch X

4. Logical reading-that is, through the buffer Adderss in BH to find the real location of the data block in memory---if 1MS read

5. Add Latch X

6. Release buffer pin S (1-->0) 0: No lock 1: Shared lock 2: Exclusive lock

7. Release latch X

Physical reading requires the data block buffer header mount to the hash chain, but also need to obtain CBC Latch,buffer header has a hash chain information, BH Mount to the hash chain, should be in BH memory structure will have information changes , BH Modification will need to have a buffer pin, the lock acquisition and release should be similar to the logical reading, but in the buffer pin S on the number 2nd exclusive lock. Of course this is my bold speculation haha.

CBC LATCH is generally used in exclusive locks, the use of shared locks is: After 9I in the index of the root, foliage reading using a shared CBC LATCH, no buffer pin access on

BH Upper Buffer pin lock Status:

0 not locked

1 shared lock, read buffer block---SELECT

2 exclusive lock, write buffer block---DML statement

2. The principle of CBC latch can be found, the emergence of CBC latch contention will have the following:

1.CBC latch protects different lists, different BH: When multiple BH on multiple hash chain is accessed concurrently under the same CBC latch,

2.CBC latch protects the same BH under the same list: When the same BH is accessed concurrently on the same hash chain

3. Physical reading to mount the data block on the hash chain, multiple physical block hash conflicts are mounted to the same hash chain or to be mounted on the hash chain on the BH is logically read

The visits here may occur in logical or physical reading, i.e.: The scenario used to CBC latch is:

The server process needs to scan the data block on the hash chain--logical reading

The server process needs to mount the data block onto the hash chain-physical read

About hash chain and hash bucket, see this series first: Click on the Open link

3. There are two ways of thinking about the CBC latch competition:

One is the hot chain-this is not very good simulation, I did not succeed.

The second is hot block-this good simulation, but also there will be buffer busy waits, the specific experiment see: http://blog.csdn.net/haibusuanyun/article/details/17252767

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/storage/

4.latch:cache buffers Chains Solution idea:

1, Hot chain: adjust _db_block_hash_latches increase the number of latch, the role is to reduce the same latch multiple barrels are simultaneous access to the situation. That is, the corresponding block of multiple tables in buffer cache corresponding to different BH, different BH and corresponding in different hash buckets, but these multiple hash buckets belong to the same latch.

Alter system set "_db_block_hash_latches" =10240 scope=spfile;

2, hot block: Adjust the buffer _cache, parameters: Db_cache_size,big integer 100M

Hot block is: the same table in the buffer cache block (a block corresponding to a bh,bh corresponding to a hash BUCKET) by multiple sessions at the same time read,-full table scan is easy to appear. You can use multiple sessions to simultaneously read the same row of the same table to simulate the production of CBC Latch, the query using ROWID to do the conditions, query speed, more likely to cause CBC latch.

3, modify the application, reduce the full table scan, that is, optimize the SQL statement

For example: CBC latch's generation of queries:

sys@ Bys3>col name for A20

sys@ Bys3>select Name,gets, Misses, sleeps, immediate_gets, immediate_misses from V$latch where NAME like '%cache Buffe RS chains% ';

NAME GETS misses sleeps immediate_gets immediate_misses

-------------------- ---------- ---------- ---------- -------------- ----------------

Cache buffers Chains 2006932 0 0 78095 0