Conflicts between Oracle Latch and latch

Oracle Rdbms uses various types of locking mechanisms in actual operations. Oracle latch is a frequently used locking mechanism. The following articles mainly introduce the concept of latch, understand latch implementation methods and explain the causes of latch conflicts.

What is Oracle latch?

Latch is a serial locking mechanism used to protect the shared data structure in the SGA region. The implementation of Latch is related to the operating system, especially whether a process needs to wait for a latch and how long it will take.

Latch is a lock that can be quickly acquired and released. It is usually used to protect the data structure of blocks in the buffer cache. There is also a cleanup process associated with each latch. When the process holding the latch becomes a dead process, the cleanup process will be called. Latch also has a correlation level to prevent deadlocks. Once a process obtains an Oracle latch at a certain level, it cannot obtain latch equal to or lower than this level.

Latch and Enqueue (Queue)

Enqueue is another locking mechanism used by Oracle. It is more complex and allows several concurrent processes to share certain resources to varying degrees. Any objects that can be used concurrently can be protected using enqueue. A typical example is table locking. We allow different levels of sharing on a table. Unlike latch, enqueue uses an operating system-specific locking mechanism. An enqueue allows users to store a tag in the lock to indicate the request lock mode.

The operating system lock manager tracks all locked resources. If a process cannot obtain the lock requested by it, the operating system places the request process in a waiting queue, the queue is scheduled according to the FIFO principle, but in latches, there is no waiting queue like in enqueue. The latch waiting process either uses the timer to wake up and retry, either spin (only for multi-processor cases ).

When latch is required

When a process is ready to access the data structure in SGA, it needs to obtain an lOracle atch. After a process obtains the latch, it will hold the latch until it no longer uses the data structure, then the latch will be released. The latch name can be used to differentiate the different data structures it protects.

Oracle uses meta commands to operate latch. When the required latch is held by another process, the execution process stops executing some commands until the latch is released. Basically, latch prevents concurrent access to shared data structures. Since the instructions for setting and releasing latch are inseparable, the operating system can ensure that only one process obtains latch, this is just a single command, so the execution speed is very fast.

Latch is held for a short period of time and provides a clearing mechanism when the owner is not interrupted normally. The cleanup is completed by the Oracle background process PMON.

What causes latch conflicts?

Latch protects the data structure in SGA from being accessed by multiple users at the same time. If a process cannot obtain the required latch immediately, it must wait, this causes additional CPU burden and reduced system speed. The additional CPU usage is caused by the 'spining 'process. 'spining' means that the process repeatedly attempts to obtain the Oracle latch at regular intervals. The process is in sleep state for two consecutive times, before obtaining latch, The spining process repeats.

How to identify internal latch conflicts

Server manager monitor is a useful tool for monitoring latch waits, requests, and conflicts. You can also query related data dictionary tables: v $ latch, v $ latchholder, v $ latchname.

Each row of the v $ latch table includes statistics on latch of different types. Each column reflects the activity of latch requests of different types. The difference between different types of latch requests lies in whether the request process continues when latch cannot be obtained immediately. According to this classification, latch requests can be divided into two types: willing-to-wait and immediate.

Willing-to-wait: if the requested latch cannot be obtained immediately, the request process will wait for a short period of time before sending the request again. The process repeats until latch is obtained.

Immediate: if the requested latch cannot be obtained immediately, the request process will not wait, but will continue to execute.

The following field in v $ latch reflects the Willing-to-wait request:

GETS --- number of successful requests to an Oracle latch using the Willing-to-wait request type.

 

MISSES --- the number of initial latch failures requested by Willing-to-wait.

SLEEPS --- the number of times the process waits to obtain latch when a latch request is unsuccessful Based on the Willing-to-wait request type.

The following field in v $ latch reflects the Immediate request:

IMMEDIATE_GETS --- the number of times a latch is successfully obtained in the Immediate request type.

IMMEDIATE_MISSES --- number of times that an Oracle latch fails to be requested as an Immediate request.