Skip to content

Oracle Enqueues

August 4, 2010

A Latch as used in Oracle is a low-level mechanism for serializing concurrent access to memory structures such as cache buffer headers. A latch is a simple memory structure that averages 100 to 200 bytes in size (actual size is dependent upong the version of Oracle, Operating System and hardware platform). Latches are contained in a state object structure and reside in either fixed SGA (parent latches) or the shared pool. They are set via the hardware-atomic compare-and-swap (CAS) instructions – LOCK CMPXCHG for Intel. Latches can be shared and are used for some AQ operations.

Latch Contention occurs when a process attempts to get a latch but fails because another process already has control of it. If the attempt (get) was issue with in no-wait mode, then return to the calling process with a failure result. If the get was issued in a willing-to-wait mode then continue attempting to obtain the latch until x number of tries is reached.

The term to describe a get that is executed in a willing-to-wait mode that tries repeatedly without acquiring the latch is spinning – the process issues the get, if the latch is already held then the process will sleep, wake up after x amount of time expires and attempts to acquire the latch again.

The process attempting to acquire a latch may be waken by the process that releases the latch but this method isn’t used for all latches.

The following views are used to troubleshoot contention issues:

v$session_event
v$session_wait
v$latchholder
Troubleshooting latch contention consists of first identifying the session(s) that are currently experiencing problems and then identify the latch the session is waiting for and determine if the wait time is significant enough to warrant attention. Identifying the child latch that is involved will help determine if the contention is concentrated on a particular child latch or are many child latches involved.

As of Oracle 10.2 KGX Mutexes replace latches (Kernel Generic MuteX – KGX). Mutexes resemble latches (a physical allocation of memory) only they are lighter weight and consume less memory space. They can also be embedded inside other structures, they have flexible spin/yield/wait strategies defined by the client process and they do not factor into the accounting for GETS,SPINS or YIELDS, only WAITS.

Keep in mind that KGX mutexes are not OS mutexes.

In 11g+ mutexes are used instead of library cache latches. The default for pinning library cache cursors and parent examination for 10.2.0.2+ is mutexes – _kks_use_mutex_pin=true.

Some known mutex types in 11g:

Cursor Parent
Cursor Pin
Cursor Stat
Library Cache
hash table
Mutex Troubleshooting – involves querying the views: v$session_wait, v$mutex_sleep and v$mutex_sleep_history among others. The mutex sleeps are well instrumented in the wait interface and the P1, P2, P3 values contain the hash value of the library cache objects experiencing contention, the session holding the mutex, etc. The views v$event_name and v$session_wait “text” columns document the meaning of P1, P2 and P3. The v$mutex_sleep view shows the wait time and the number of sleeps for each combination of mutex type and location while the v$mutex_sleep_history view shows the last individual occurences of mutex sleeps based on a circular buffer and this view contains the highest level of detail.

Enqueues – Terminology and Views

Locks are called enqueues because the locking mechanism implements an ordered queuing structure for lock waiters. Latches and mutexes do not have sophisticated queuing mechanisms.

Enqueue Resources – v$resource(X$KSQRS)

Enqueue Locks – v$lock(X$KSQEQ)

Enqueue resource types – v$lock_type has two meanings – ID1 and ID2

Slots in a hash table identify a resource. Resources are not locks, they are placeholders that reference the object that can be locked. The unique identifier (primarykey) for a resource will consists of the following:

Resource type (TM, TX, MR, …)
Resource identifier 1 (ID1)
Resource identified 2 (ID2)
Example:
TM-XXXXXXXX-XXXXXXXX
The resource identifiers are used to break down a resource class into individual objects. An example of this would be two tables that both have a TM resource allocated to them in memory. The ID1 specifies the object_id of the table (v$lock_type which contains the meaning of ID1/2 for each resource type).

Locks – provide the link between the resource locked and the holder of that resource. If there are many lock holders on a single resource (assuming compatible mode) then it follows that there will be multiple lock structures pointing to that one resource.

Advertisements

From → Oracle DBA

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: