Buffer busy waits my opinion

Address: http://www.itpub.net/thread-1801066-1-1.html

I recently saw several posts in the forum discussing buffer busy waits. Here I will talk about my views.
Let's first talk about how this wait came about.
The origin of buffer busy waits.
When n processes want to hold the buffer pin on the memory block in incompatible mode, buffer busy waits will be generated.
What?
Buffer pin on the memory block?
Doesn't it mean that all the memory block locks are implemented by latch? When will a buffer pin still pop up? I have never heard of it !!
Well, since you asked this question, we can first assume that there is no buffer pin to see how to access/modify a data block. (The following process is simplified as much as possible)
1) Calculate the bucket where the data block is located based on the address of the data block
2) obtain the cbc latch to protect this bucket
3) Search for the data blocks we need on this linked list.
4) Read/modify the data block content
5) Release cbc latch
We know that latch's acquisition and release time are generally very short (cpu atomic operations). In the above five steps, we can consider the time 1, 2, and 5 as extremely fast operations.
However, the time consumed in Step 4 is much larger than the time consumed in these steps. I drew a rough picture to exhibit.

This leads to a problem. In a large concurrency environment, because the cbc latch takes too long to hold, it will lead to a large number of latch contention, A large number of latch competitions can easily lead to bottlenecks in the system's cpu resources. It should be particularly noted that even if all your operations are not modified, it will lead to a large number of cbc latch contention: it takes too long to hold the cbc latch and release it to the cbc latch.
How can we solve this problem? Let's talk about ORACLE's practices.
ORACLE can solve this problem by getting the cbc latch twice for each access to the buffer block session, and then adding the buffer pin to the memory block.

Take the following steps.
1) Calculate the bucket where the data block is located based on the address of the data block
2) obtain the cbc latch to protect this bucket
3) Search for the required data blocks on the linked list. After finding the required data blocks, pin the buffer (read s and modify x)
4) Release cbc latch
5) Read/modify the data block content
6) Obtain cbc latch
7) unpin this buffer
8) Release cbc latch
Through this implementation, we can see that the cbc latch holding time is greatly reduced, because the cbc latch holding has only a few things, which greatly reduces the competition for cbc latch.
You may be challenged to say that, although the cbc latch competition will be greatly reduced, ORACLE has only shifted the competition point, and now it has become the competition between buffer locks.
You are right, but not right !!
If your database reads a lot and writes a little, the buffer pin between each read is compatible and is in the s mode, so there will be no contention.
If you write a lot in your database and read a little, buffer busy waits will be generated. However, the cost of such waiting is much lower than that of cbc latch, latch's spin mechanism consumes a lot of cpu, while bufferpin management is essentially similar to the enq lock mechanism. Without the spin mechanism, it does not need to spin a lot of cpu.
If your database is a mixed read/write scenario, writing will block reading and generate buffer busy waits, but reading will not block writing and will not generate this wait. This will be discussed later.

Let's take a simple look at several scenarios that generate buffer busy waits. The following code finds two records on the same block.

Createtable wxh_tbd as select * from dba_objects;
Create index t on wxh_tbd (object_id );
Select dbms_rowid.ROWID_RELATIVE_FNO (rowid) fn, dbms_rowid.rowid_block_number (rowid) bl, wxh_tbd.object_id, rowid from wxh_tbdwhere rownum <3;

Fn bl OBJECT_ID ROWID
------------------------------------------------
8 404107 20 AAAF04AAIAABiqLAAA
8 404107 46 AAAF04AAIAABiqLAAB

1) scenario 1: Read. In order not to affect the integrity of the experiment, we should simply test and read the scenario. Although you may already know that there is no waiting for buffer busy waits in this scenario.
SESSION 1 run:
Declare
C number;
Begin
For I in 1 .. 6000000 loop
Select count (*) into c from wxh_tbd where rowid = 'aaaf04aaiaabiqlaab ';
End loop;
End;
/
Session 2 run:
Declare
C number;
Begin
For I in 1 .. 6000000 loop
Select count (*) into c from wxh_tbd where rowid = 'aaaf04aaiaabiqlaaa ';
End loop;
End;
/

Check the background wait, without any buffer busy waits waiting. This result is expected.

2) Scenario 2: write:
Session 1, run:
Begin
For I in 1 .. 40000000 loop
UPDATE wxh_tbd SET object_name = 20 where rowid = 'aaaf04aaiaabiqlaaa ';
Commit;
End loop;
End;
/

Session 2, run:
Begin
For I in 1 .. 40000000 loop
UPDATE wxh_tbd SET object_name = 46 where rowid = 'aaaf04aaiaabiqlaab ';
Commit;
End loop;
End;
/
In the waiting of the two sessions, we have observed a large number of bufferbusy waits. Because session 1 and 2 will add x exclusive bufferpin to the buffer, the incompatibility of the two lock modes leads to Contention.

3) read/write hybrid testing:
Session 1:
Begin
For I in 1 .. 40000000 loop
UPDATE wxh_tbdSET object_name = 20 where rowid = 'aaaf04aaiaabiqlaaa ';
Commit;
End loop;
End;
/

Session 2:
Declare
C number;
Begin
For I in 1 .. 6000000 loop
Select count (*) into c from wxh_tbd where rowid = 'aaaf04aaiaabiqlaab ';
End loop;
End;
/

Waiting for session 1:
1825, WAIT, latch: cache buffers chains, 3531,882.75 us
Session 2 wait:
1768, WAIT, buffer busy waits, 145246, 36.31 ms

We can see that the write session 1 does not have any buffer busy waits waiting, while the read session 2 generates a large number of buffer busy waits waiting.
The online debate on this part is fierce.
The truth is actually very simple
1) when the read process finds that the memory block is being modified (if there is a buffer pin in the x mode, it indicates it is being modified), it can only wait, it cannot clone the block, this is because the memory block is changing and the clone is insecure. Many people say that reading and writing in oracle is not blocked. oracle can clone memory blocks to separate the competition for reading and writing. In fact, it depends on the situation that the memory block is being written during reading and cannot be cloned because it is not safe. At this time, the read process can only wait for buffer busy waits.
2) When the write process finds that the memory block is being read, the read will not block the write because ORACLE can easily clone an xcur data block, write on the cloned block. The clone is safe at this time, because the process that reads the memory block does not modify the data block, ensuring the clone security.

Speaking of this, it is basically a simple summary, but before the summary, it is necessary to give you a brief introduction to the two lists on the buffer header.

 

Bufferheader has two lists: users list and waiter list.

Vage posts are well written.

QQ:252803295

DSI & Core Search group II: 177089463 (1000 technical groups: Not full)
DSI & Core Search group III: 284596437 (500 technical groups: Not full)
DSI & Core Search Group IV: 192136702 (500 technical groups: Not full)
DSI & Core Search group V: 285030382 (500 chat group: Not full)

MAIL:

BLOG:

WEIBO:

ITPUB:

OCM:Http://education.oracle.com/education/otn/YGuo.HTM