Introduction to Latch in Oracle

 

This article will explain to you the Latch mechanism of Oracle. Latch is a plug-in for Kingsoft translation, locking and a specialized term called Latch. When I started to contact Oracle, I didn't quite understand why I didn't write Lock, aren't they all locks? But translation is different. Why? After the study, we know that there is a big difference between the two.

Latch is a lightweight lock resource provided by Oracle. It is used to lock resources quickly and in a short time to prevent multiple concurrent processes from simultaneously modifying and accessing a shared resource. It only works in the memory, it is not accurate to say that the Lock of resources in the memory is latch, and the Lock of database objects (tables, indexes, etc.) is Lock. For example, if a block in the data cache is to be read, we will obtain the latch of the block. This process is called pin. Another process just needs to modify the block and pin the block, at this time, he must wait, and the current process can be pin after latch is released, and then modify it. If multiple processes request at the same time, there will be competition between them. There is no queuing mechanism, once the previous process has been released, the subsequent process will be congested. There is no concept of first-in-first-out. This is essentially different from Lock, and all of this happens very quickly, latch features fast and short-lived. Of course, this is only a general process. The details will be discussed later.

Let's take a look at the difference between Latch and Lock.

1. latch is a mechanism that provides mutex access to the memory data structure, and Lock is used to set shared resource objects in different modes. Each mode is compatible or exclusive. From this point, we can see that, latch access, including queries, is also mutually exclusive. At any time, only one process can pin a part of the memory. Fortunately, this process is quite short, otherwise, the system performance will not be guaranteed. Now, starting from 9I, multiple processes are allowed to query the same memory block at the same time, but the performance is not as good as imagined.

2. Latch only acts on the memory. It can only be accessed by the current instance, while L ock acts on the database object. In the RAC system, the instance can allow Lock detection and access between instances.

3. Latch is instantly occupied. Release and Lock must wait until the transaction ends correctly. The length of time it takes is determined by the transaction size.

4. Latch is not in the queue, while Lock is in the queue.

5. Latch does not have deadlocks, but locks exist (deadlocks are rare in Oracle)

Take a look at the example below and you will feel the existence of Latch.

SQL> CREATE TABLE MYTEST AS SELECT OBJECT_NAME FROM USER_OBJECTS WHERE ROWNUM <= 4;
Table created
SQL> SET TIMING ON
SQL>
DECLARE lv_name VARCHAR2 (25): = '';
BEGIN
FOR I IN 1 .. 100000 LOOP
SELECT OBJECT_NAME INTO lv_name from mytest where rownum = 1;
End loop;
END;
/
PL/SQL procedure successfully completed
Executed in 3.359 seconds

This process constantly accesses the same data block in the Table. First, it physically reads the data block to the data buffer, and then continuously obtains the latch of the block in the memory. Now there is only one process, it took more than three seconds to run 0.1 million times, But when I pulled out four windows and concurrently ran this statement, the problem occurred, multiple processes PIN the same data block, each of which took about 15 seconds, and one of them ended one by one. In the end, only one flash left, because no one grabbed the block, this experiment demonstrates Latch competition and I am questioning whether 9I queries can share Latch.

Now let's take a look at the detailed process of obtaining Latch. At any time, only one process can access a block in the memory (I don't want to consider Latch sharing proposed by 9I ), if another process is occupying blocks and cannot obtain Latch, it will perform a spin (rotation) on the CPU for a very short time. After spin, it will continue to be obtained. If it fails to be obtained, it will still be spin, until the number of spin times reaches the threshold value (this is specified by the implicit parameter _ spin_count), the process will stop spin and perform short-term sleep. After sleep, the previous operation will continue, until the Latch on the block is obtained. There is also an algorithm for the process sleep time. It will increase with the number of spin times, in the unit of seconds, such ,... The sleep threshold is controlled by the implicit parameter _ max_exponential_sleep. The default value is 2 seconds. If the current process has occupied another Latch, the sleep duration is not too long (too long will cause Latch wait for other processes). The maximum sleep duration is determined by the implicit parameter _ max_sleep_holding_latch. The default value is 4 seconds. The sleep of this time limit is also called short-term Wait. Another case is Latch Wait Posting. At this time, Wait for the process to request Latch to fail and enter sleep, he will press a signal into the Latch Wait List to obtain the Latch request. When the occupying process releases the Latch, he will check the Latch Wait List, send a signal to the requesting process to activate the dormant process. Latch Wait List is a List of processes maintained in the SGA area. It also needs Latch to ensure its normal operation. By default, the share pool latch and library cache latch adopt this mechanism, if the implicit parameter _ latch_wait_posting is set to 2, all Latch uses this waiting method, which can accurately wake up a waiting process, however, maintaining the Latch Wait List requires system resources, and the competition for Latch on the Latch Wait List may also lead to bottlenecks.

If it takes a long time for a process to request, rotate, and sleep Latch, it will notify the PMON process to see if the process occupied by Latch has ended or died unexpectedly, if yes, PMON will clear the Latch resources occupied.

Now you can understand the process of obtaining Latch. Request-SPIN-sleep-request-SPIN-sleep... Occupy, some people will ask why SPIN is required, why not wait for sleep directly? Here we need to understand what sleep means. It means to temporarily discard the CPU and perform context switch. In this way, the CPU needs to save some status information during the running of the current process, such as the stack, semaphore and other data structures, and then introduce the status information of subsequent processes. After processing, switch back to the original Process status. If this process frequently occurs in a high transaction, in the processing system of a High-concurrency process, it will be a very expensive resource consumption. Therefore, he chose spin to let the process continue to occupy the CPU, run some empty commands, and then continue to request and continue spin, until the value of _ spin_count is reached, the CPU will be abandoned for a short time to sleep, and then continue the action just now. Oracle software is designed like this. The masterpiece of the masters of the world naturally has his own principle, I will not pay for the text here.

Latch wait in the system is not avoided, because this is the operating mechanism of Oracle. When you see a high Latch get, it does not mean that your system needs to be adjusted, sometimes there is only a short wait time behind a very high get value. The adjusted object should be determined by the consumed time, instead of seeing a very high number of times. Of course, we still need to care about the exception when the value is obtained 100,000 times higher than the other wait time. There are a lot of Latch waits in Oracle, mainly including share pool, library cache, cache buffer chains, buffer busy wait, every adjustment can be written on several pages, and it will be completed later.