The buffer busy waits that Oracle Dba must understand

How is Buffer Busy Waits generated?

As an Oracle Dba, if you have never met Buffer Busy Waits, you cannot count as a real Oracle Dba. Buffer Busy Waits is a common wait for Oracle databases, especially in environments with frequent concurrent writes. To explain why this wait occurs, first describe the process of reading and writing data blocks from Oracle:

1) Calculate the hash bucket of the data block based on the data block address (HASH algorithm.

2) Calculate the cbc latch to protect the bucket based on the bucket number, and then apply for the cbc latch to find that the data block is not in the bucket (in memory). Here we assume it is in the memory.

3) read/modify data blocks.

4) release the cbc latch.

The above description seems to be very smooth, but there is a problem. The cbc latch is exclusive (we do not consider the complicated situation for the moment: Sharing LATCH Holdings ), if the data block is read by the exclusive cbc latch, the LATCH will be held for a long time, because compared with the acquisition and release of LATCH, reading data blocks is very time-consuming. Therefore, when you hold a cbc latch, reading data blocks will inevitably result in competition for cbc latch. To solve this problem, Oracle introduced the buffer pin (buffer lock) function.

It is necessary to re-describe the content of the data block. The general steps are as follows:

1) first, you must determine the hash bucket where the data block is located.

2) apply for cbc latch and locate the data block.

3) obtain the buffer pin/lock of the data block in S/X mode. (Read to get the s mode, modify to get the x mode, S and S modes are compatible, S and X, X and X modes are not compatible ).

4) Release CBC LATCH

5) Read/modify data blocks under PIN protection.

6) obtain the cbc latch.

7) unpin buffer pin (buffer lock ).

8) release the cbc latch.

It seems that the steps are complicated. The cbc latch has been acquired/released twice, but the concurrency is greatly improved. In the above steps, the purpose of holding the cbc latch becomes simple, just to modify the buffer pin mode, and then to protect the data block by the compatibility of the PIN mode, for example: the PIN in S and S modes is compatible and can be read concurrently. The PIN in S and X modes are incompatible, and subsequent sessions need to wait.

Although LATCH is exclusive, this time is extreme and it is unlikely to cause contention. If everyone is reading data blocks, the S Modes of buffer lock are shared, there will be no competition. However, at the same time point, if a process holds the buffer lock of the data block in S mode and another process wants to hold it in X mode, competition will occur because the principle is simple, the buffer lock in S mode is incompatible with the buffer lock in X mode. Likewise, two processes that want to modify the same data block at the same time will also encounter a buffer lock conflict. this conflict is represented by the ORACLE wait event as Buffer Busy Waits. That is to say, the essence of Buffer Busy Waits waiting is caused by the contention of buffer lock.

We often say that reading is not blocking writing, and writing is not blocking reading. It is at the physical data block level. In the memory, reading, writing, and writing are blocked at the same time. Only reading is not blocked.

In order to facilitate understanding, the above steps are simplified and some points can be added below:

1) Once your PIN contains a data block, you do not need to remove it from the UNPIN immediately. ORACLE thinks that you may still access this data block after this call, so the PIN is retained until the end of this call and then UNPIN.

2) Oracle designs the unique index, undo block, unique index, return table, index root, and branch block. when accessing (reading, the shared cbc latch is obtained. You do not need to go to the PIN data block and read the data block when you hold the shared cbc latch. The possible cause is that these blocks are less likely to be modified, so Oracle uses this mechanism separately. Therefore, the cbc lacth must be obtained twice for reading common data blocks. For such special data blocks, it is okay to obtain the shared cbc latch only once.

3) The above situation is that the data block already exists in the memory. If the data block is not in the memory, the read by other session may be generated for contention. If you have time, let's look at the reason for this wait.

4) the above description only conforms to the version after 10 Gb. Buffer busy waits will also be generated after reading 10 Gb. After 10 Gb, buffer busy waits will be added to read by other session waiting.

5) the above description basically uses the word data block, and the more accurate expression should be the buffer block.