The conflict expression between Oracle latch and latch

Oracle An RDBMS is used in practice for various types of related locking mechanisms, andOracle latch is one of the most frequently used, and the following articles are mainly about the concept of latch (latch) and understanding the implementation of latch and explain the causes of latch conflicts.

What is Oracle latch

Latch is a serial locking mechanism used to protect shared data structures in the SGA area. Latch implementations are related to the operating system, especially if a process needs to wait for a latch and how long to wait.

Latch is a lock that can be acquired and released very quickly, and is usually used to protect the data structure that describes the block in buffer cache. There is also a purge process associated with each latch, which is invoked when the process that holds the latch becomes a dead process. Latch also has the relevant level to prevent deadlocks, and once a process obtains an Oracle Latch at a certain level, it is not possible to obtain Latch equal to or below that level.

Latch and Enqueue (queues)

Enqueue is another locking mechanism used by Oracle, which is more complex, allowing several concurrent processes to share some resources to varying degrees. Any object that can be used concurrently can be protected by using Enqueue. A typical example is the locking of a table, where we allow different levels of sharing on a table. Unlike latch, enqueue is the use of operating system-specific locking mechanisms, a enqueue allows users to store a tag in the lock to indicate the mode of the request lock.

The operating system Lock manager tracks all locked resources, and if a process cannot get the kind of lock it requests, the operating system places the request process in a waiting queue, which is scheduled according to the FIFO principle, while in latches there is no wait queue like the one in Enqueue. The latch waits for the process to either wake up and retry with the timer, or spin (only for multiprocessor cases).

When to need latch

When a process is ready to access the data structure in the SGA, it needs to obtain a loracle atch. When the process gets latch, it will hold the latch until it no longer uses the data structure, and latch will be released. You can differentiate the different data structures that it protects by using the latch name.

Oracle uses meta directives to manipulate latch, and when the required latch has been held by another process, the execution instruction process stops executing some of the instructions until the latch is released. Fundamentally, latch prevents concurrent access to shared data structures, and because the instructions for setting and releasing latch are indivisible, the operating system can guarantee that only one process obtains latch, and because it is a single instruction, execution is fast.

The latch is held for a short time and provides a clearing mechanism when the holder is not properly interrupted, which is done by the Oracle background process Pmon.

What causes latch conflict

Latch protects the data structures in the SGA from simultaneous access by multiple users, and if a process cannot get the required Latch immediately, it must wait, which causes the additional burden on the CPU and slows down the system. The extra CPU usage is caused by the process ' spining ', and ' spining ' refers to a process that periodically repeats attempts to get Oracle latch, where the process is dormant for two consecutive times, and the spining process repeats itself before it gets latch.

How to identify an internal latch conflict

Server Manager Monitor is a fairly useful tool for monitoring latch waits, requests, and conflicts. You can also query the relevant data dictionary table: V$latch, V$latchholder, V$latchname.

Each row of the V$latch table includes statistics on different types of latch, each of which reflects the activity of different types of latch requests. The difference between different types of latch requests is whether the request process continues when the latch is not immediately available. By this classification, the types of latch requests can be grouped into two categories: willing-to-wait and Immediate.

Willing-to-wait: means that if the requested latch is not immediately available, the request process waits a short time before making the request again. The process repeats the process until it gets latch.

Immediate: means that if the requested latch is not immediately available, the request process no longer waits, but continues to execute.

The following fields in V$latch reflect the willing-to-wait request:

GETS---The number of times an Oracle latch was successfully requested with a WILLING-TO-WAIT request type.

Misses---Initially requests a latch number of unsuccessful willing-to-wait request types.

Sleeps the number of times the process waits to get latch after the willing-to-wait request type is initially requested for a latch.

The following fields in V$latch reflect the immediate class request:

Immediate_gets---The number of times a latch was successfully obtained with a IMMEDIATE request type.

Immediate_misses---The number of unsuccessful attempts to request an Oracle latch with a IMMEDIATE request type.