Gouranga's Tech Blog: Latches and Locks in Oracle

Latches in Oracle

There are lots of concepts about latches and locks in oracle. Few documents I read and and investigated in many production environments.

About Latch in Oracle:

Latches are serialization mechanisms that protect areas of Oracle’s shared memory (the SGA). In simple terms latches prevent two processes from simultaneously updating - and possibly corrupting - the same area of the SGA.. It is low-level serialization mechanism.

For example, the data buffer latches (sometimes called LRU latches) ensure that Oracle processes are 'serialized', such that only one process may alter the data buffer address chain. This twiddling of RAM addresses happens very fast (RAM speed is expressed in nanoseconds), yet busy Oracle databases may experience waits on these events.

In-other way we can say, Latches are like locks for RAM memory structures to prevent concurrent access and ensure serial execution of kernel code. The LRU (least recently used) latches are used when seeking, adding, or removing a buffer from the buffer cache, an action that can only be done by one process at a time.

Contention on an LRU latch usually means that there is a RAM data block that is in high demand. If a latch is not available a 'latch free miss' statistics is recorded.

When Latch will occur:

Oracle sessions need to update or read from the SGA for almost all database operations. For instance:

When a session reads a block from disk, it must modify a free block in the buffer cache and adjust the buffer cache LRU (Least Recently Used) chain.
When a session reads a block from the SGA, it will modify the LRU chain.
When a new SQL statement is parsed, it will be added to the library cache within the SGA.
As modifications are made to blocks, entries are placed in the redo buffer.
The database writer periodically writes buffers from the cache to disk (and must update their status from “dirty” to “clean”).
The redo log writer writes entries from the redo buffer to the redo logs.
Latches prevent any of these operations from colliding and possibly corrupting the SGA.

How Latches will work:

Because the duration of operations against memory is very small (typically in the order of nanoseconds) and the frequency of latch requests very high, the latching mechanism needs to be very light-weight. On most systems, a single machine instruction called “test and set” is used to see if the latch is taken (by looking at a specific memory address) and if not, acquire it (by changing the value in the memory address).

If the latch is already in use, Oracle can assume that it will not be in use for long, so rather than go into a passive wait (e.g., relinquish the CPU and go to sleep) Oracle will retry the operation a number of times before giving up. This algorithm is called acquiring a spin lock and the number of “spins” before sleeping is controlled by the Oracle initialization parameter “_spin_count”.

The first time the session fails to acquire the latch by spinning it will attempt to awaken after a millisecond or so. Subsequent waits will increase in duration and in extreme circumstances may reach 100s of milliseconds. In a system suffering from intense contention for latches, these waits will have a severe impact on response time and throughput.

Root causes of Latch contention:

The latches that most frequently affect performance are those protecting the buffer cache, areas of the shared pool and the redo buffer.

Library cache and shared pool latches: These latches protect the library cache in which sharable SQL is stored. In a well defined application there should be little or no contention for these latches, but in an application that uses literals instead of bind variables (for instance “WHERE surname=’HARRISON’” rather that “WHERE surname=:surname”, library cache contention is common.
Cache buffers chain latches: These latches are held when sessions read or write to buffers in the buffer cache. There are typically a very large number of these latches each of which protects only a handful of blocks. Contention on these latches is typically caused by concurrent access to a very “hot” block and the most common type of such a hot block is an index root or branch block (since any index based query must access the root block).
Redo copy/redo allocation latches: These latches protect the redo log buffer, which buffers entries made to the redo log. These latches were a significant problem in earlier versions of Oracle, but are rarely encountered today.

Detecting/ Finding latch Contention:

Oracle’s wait interface makes it relatively easy to detect latch contention and – from 10g onwards – to accurately identify the specific latch involved. In 10 and 11g, each latch has it’s own wait category if waits on the specific latch become significant then we can deduce a latch contention problem.

See more : Click here & here

Latch and Concurrency:

An increase in latching means a decrease in concurrency. For example, excessive hard parse operations create contention for the library cache latch. Latches are a type of lightweight lock. Locks are serialization devices. Serialization devices inhibit concurrency. To build applications that have the potential to scale, ones that can service 1 user as well as 1,000 or 10,000 users, the less latching we incur in our approaches, the better off will be.

· You have to choose always an approach that takes longer to run on the wall clock but that uses 10 percent of the latches. We know that the approach that uses fewer latches will scale substantially better than the approach that uses more latches. Latch contention increases statement execution time and decreases concurrency.

Latch and Queuing:

Unlike enqueue latches such as row locks, latches do not permit sessions to queue. When a latch becomes available, the first session to request the latch obtains exclusive access to it.

Latch spinning occurs when a process repeatedly requests a latch in a loop, whereas

Latch sleeping occurs when a process releases the CPU before renewing the latch request.

Typically, an Oracle process acquires a latch for an extremely short time while manipulating or looking at a data structure. For example, while processing a salary update of a single employee, the database may obtain and release thousands of latches.

Data Dictionary:

The V$LATCH view contains detailed latch usage statistics for each latch, including the number of times each latch was requested and waited for.

See more: Click here & here also

User Action:

Determine which latch is causing the highest amount of contention.

To find the problem latches since database startup, run the following query:

SELECT n.name, l.sleeps

FROM v$latch l, v$latchname n

WHERE n.latch#=l.latch# and l.sleeps > 0 order by l.sleeps;

To see latches that are currently a problem on the database run:

SELECT n.name, SUM(w.p3) Sleeps

FROM V$SESSION_WAIT w, V$LATCHNAME n

WHERE w.event = `latch free'

AND w.p2 = n.latch#

GROUP BY n.name;

Take action based on the latch with the highest number of sleeps.

Q&A:

How are latches different from locks, and how does a DBA learn about Oracle latch management?

Ans: Latches are like locks for RAM memory structures to prevent concurrent access and ensure serial execution of kernel code. The LRU (least recently used) latches are used when seeking, adding, or removing a buffer from the buffer cache, an action that can only be done by one process at a time.

Difference between Latch and Lock?

Ans: Latches occur and removed automatically internally and fully managed by Oracle database. i.e., A latch is a low-level internal lock used by Oracle to protect memory structures. It may tell degrade of performance. Lock may created due to various reason. One of the major root cause is bad application code and user’s mistake. Read this document to clear the idea more.