How to simulate the production of CBC latch and buffer busy wait event

Database version: Oracle 11.2.0.4.0

The reason for the CBC latch:--only when the logical reading occurs

1.CBC latch protection of different linked lists, different BH: the same latch under multiple bucket are simultaneously accessed, (one latch corresponds to multiple bucket)

2.CBC latch protects the same BH under the same list: under the same latch, the same BH is accessed simultaneously

Latch:cache Buffers Chains Solution:

1, _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

BH Upper Buffer pin lock Status:

0 not locked

1 shared lock, read buffer block---SELECT

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

Buffer pin lock contention: Buffer busy Waits wait event-hot block

How to solve: Buffer busy waits wait event-hot block

1. Increase Pctfree

2. Using small data blocks -2k-4k

3. Use hash Table partitioning

3. Reverse Indexing

In the meantime the CBC latch buffer busywait S is:

Session 1 has obtained a buffer pin lock and has released CBC latchand is holding a buffer pin lock .

Session 2 gets 1 of the same CBC latch, and wants to get a buffer pin lock for session 1 with the same block, at which time a buffer pin lock is held due to conversation 1, which produces a buffer Busy waits wait for the event .

Session 1 needs to get CBC latchto release the holding Buffer pin lock , and Session 2 is holding CBC latch, so it will also produce CBC latch wait for the event.

The following methods are used to simulate the generation of hot blocks through multiple sessions simultaneously:

1. Detection of table test-related information-in large data table experiments more easily produce buffer busy waits

Select rowID,

Dbms_rowid.rowid_row_number (ROWID) Rowid_rownum,

DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) file_id,

Dbms_rowid.rowid_block_number (ROWID) block_id,test.* from test;

ROWID rowid_rownum file_id block_id object_name STATUS

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

AAAFSJAAEAAAACKAAA 0 4 164 10 15

Aaafsjaaeaaaackaab 1 4 164 10 15

As you can see, the two entries in the test table are recorded in the same block of the same file.

2. Write a loop:

Loop execution of query statements 1 million times:--Query the first row of 164 data blocks

Declare

AA varchar2 (100);

Begin

For I in 1..1000000 loop

Select object_name into a AA from test where rowid= ' aaafsjaaeaaaackaaa ';

End Loop;

End

/

Loop update query Statement 200 times--Query the second row of 164 data blocks

Declare

Begin

For I in 1..200 loop

Update test set status=15 where rowid= ' Aaafsjaaeaaaackaab ';

End Loop;

Commit

End

/