Shulou(Shulou.com)06/01 Report--

Which scenarios will generate Buffer Busy Waits

Buffer Busy Waits is a common wait in Oracle databases, especially in environments where concurrent writes are frequent. As an Oracle DBA, if you have never encountered Buffer Busy Waits waiting, then you are not a real Oracle DBA.

The reason for this wait is that at the memory level, there is a contention to read and write to the same memory block at the same time. Here, I take the operations of two processes on data blocks as an example to sum up the scenario where Buffer Busy Waits is generated:

Write, for example, if one process is making changes to the memory block, another process will also modify the memory block.

Write and read, for example, one process is making changes to the memory block, and another process wants to read the memory block at this time. You may ask why oracle doesn't go to copy the modified block to construct the CR block, and then read the constructed CR block, for the simple reason that the memory block is doing copy while writing (changing), which is an unsafe action.

According to the arrangement and combination of characters, there are only two scenarios: "read" and "read and write". Neither of these scenarios will lead to Buffer Busy Waits (the important assumption here is two processes). The reason will be given later.

Why did you design this wait?

Why did Oracle design this wait? this is a good question, and this article is mainly to answer this question.

"Why" is more important than "doing so". The former is about logic and insight, while the latter is about processes and results.

Before answering this question, first design a scenario, "if there is no such wait, how to read and write data blocks", of course, this scenario is designed, but does not exist.

So let's start by assuming that the scenario is like this, and we want to write to a data block and follow these steps:

First, the Hash Bucket of the block is calculated according to the address of the block (Hash algorithm).

According to the number of the bucket, calculate the CBC Latch that protects the bucket, and then apply for CBC Latch to see if the data block is in the bucket, which is assumed to be in memory.

Modify the data block.

Release CBC Latch.

The above description seems to be very smooth, but there is a problem. Because the holding of CBC Latch is exclusive, if you modify the data block in the case of holding CBC Latch, then the holding time of this Latch will be longer, this length is relative to the acquisition and release of Latch, this length is relative to the CPU atomic operation, so modify the data block while holding CBC Latch, for the database / block with frequent read and write (hot block). Then it is bound to cause CBC Latch contention, based on the characteristics of Latch (spin, hibernation, re-spin), it will cause a lot of waste of CPU resources, resulting in poor performance of the database.

To solve this problem, Oracle designed the function of Buffer Pin. In other words, the value of Buffer Pin mechanism is to reduce CBC Latch contention and save CPU resources.

With the introduction of Buffer Pin, the approximate steps for modifying data blocks are as follows:

First, the Hash Bucket of the block is calculated according to the address of the block (Hash algorithm).

Then apply for CBC Latch, find and locate the data block.

Get the Buffer Pin of the data block in X mode.

Release CBC Latch

Under the protection of Pin, modify the data block and continue with the following steps when complete

Get CBC Latch

Release Buffer Pin

Release CBC Latch

The steps are much more complicated, and the CBC Latch fetch / release occurs twice, and the workload seems to be exactly twice as large.

But it greatly reduces the contention of CBC latch. Because the time to hold CBC Latch becomes very short. Holding CBC Latch is only for the purpose of adding buffer pin to buffer header, and the purpose becomes simple and simple.

You might say that the solution to Oracle is to press the gourd, it just shifts the competition, it used to be CBC Latch's competition, now it's Buffer Pin's contention.

This sentence is true.

But Buffer Pin contention does not consume CPU resources. A notification mechanism similar to a queue lock. Instead of spinning like Latch.

Read the data block to increase the S mode Buffer Pin, modify the data block to increase the X mode Buffer Pin. The Pin of S and S modes are compatible and can be read concurrently, while X and S modes are incompatible, and subsequent read sessions need to wait.

At the beginning of the article, we give two examples of two processes operating on the same block of memory. Here, after we have some knowledge, we will summarize and review.

Write and read, at the same time, if one process holds the block Buffer Pin in X mode and another process wants to hold it in S mode, then there will be contention, because the reason is very simple, X mode Buffer Pin and S mode Buffer Pin are not compatible.

Writing, similarly, for two processes that want to modify the same data block at the same time, only one process can get the X-mode Buffer Pin, and the other session produces Buffer Busy Waits wait.

We often say that read does not block write, write does not block read, that is at the physical data block level, in memory, write / write to the same memory block at the same time are blocking each other.

Here goes on to complete the scene above, "read" does not produce Buffer Busy Waits wait, because the Pin mode is compatible. " "read and write" does not produce Buffer Busy Waits waiting, one process is reading the data block, while another process is going to write the data block, the writing process is very smart, it will copy out a current block (the previous block becomes a CR block), and then write on the current block, in this way to avoid competition and avoid Buffer Busy Waits contention.

Because the previous process is reading, not modifying. The copy in this scenario is secure.

In addition, because reading database blocks requires the addition of S-mode Buffer Pin on Buffer header, which is a memory modification operation, even if it is a read operation, the holding of CBC Latch is exclusive, but the time to increase Pin is very short, which is unlikely to cause a large number of CBC Latch contention in a database environment under normal pressure.

Of course, if a large number of processes read the same memory block frequently, it will cause CBC Latch contention.

Some details.

Finally, it is necessary to add some details:

Once the process Pin resides in a data block, there is no need to immediately UNPin (remove Pin). ORACLE believes that it is possible to continue to access this data block after this call, so the UNPin operation will not be done until the end of this call.

Oracle in the unique index / undo block / unique index back to the table / index root, branch block design, when accessing (reading), to obtain the shared CBC Latch, do not need to add Pin to the Buffer, directly read the data block in the case of holding the shared CBC Latch. The reason for this is that these types of blocks are less likely to be modified, so Oracle adopts this mechanism alone. Therefore, for the reading of ordinary data blocks, you need to get the CBC Latch twice, while for this special data block, you only need to get the shared CBC Latch once.

The situation we mentioned above is in the case that the data block already exists in memory. If the data block is not in memory, Read By Other Session race waiting may occur.

The above description only conforms to the version after 10G. Reading before 10G will also generate Buffer Busy Waits,10G and put the Buffer Busy Waits in this area into Read By Other Session waiting.

Summary

This article describes how Buffer Busy Waits is produced and its mechanism, but does not explain how to tune it. The essence of Buffer Busy Waits wait design is to reduce CBC Latch contention. Take two processes operating memory blocks as an example, Buffer Busy Waits will be generated in the "write" and "write" scenarios, and Buffer Busy Waits wait will not occur in the "read" scenario. In the "read / write" scenario, the server process that occurs the write operation will Copy out a current block to continue the write operation instead of waiting for Buffer Busy Waits.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.