oracle mutex

An introduction to Mutexes in 10gR2

By Connie Green, Kumar Rajamani
From meetings with: Kumar Rajamani, Russell Green, Saureen Shah, Cecilia Gervasio and Connie Green

Introduction
This paper is intended as an introduction to mutexes.  It describes new concepts associated with mutexes, when mutexes are used, and related wait events and views.

Overview
A mutex, similarly to a latch, is a low-level serialization mechanism used to control access to a shared data structure in the SGA.  Serialization is required to avoid an object being:
? deallocated while someone is accessing it
? read while someone is modifying it
? modified while someone is modifying it
? modified while someone is reading it

Mutexes can be defined and used in many different ways.  For example:
? Each structure being protected by a mutex can have its own mutex (e.g. a parent cursor has its own mutex, and each child cursor has its own mutex)
? Each structure can be protected by more than one mutex, with each mutex protecting a different part of the structure
? A mutex can protect more than one structure

The mutex can either be stored within the structure it protects, or elsewhere.  Mutexes are usually dynamically created at the same time as the structure they protect.  If a mutex is embedded within the structure it protects, then it will be implicitly destroyed when the owning structure is freed.

Benefits of mutexes
Although mutexes and latches are both serialization mechanisms, mutexes have certain features latches do not.

Smaller and faster
Mutexes were introduced as an alternative to latches, as they are much faster to get, and smaller.  A mutex get is in the region of 30-35 instructions, compared to 150-200 instructions for a latch.  A mutex is around 16 bytes in size, compared to 112 bytes for a latch in 10.2 (in prior releases it was 200 bytes).  Some of the reduction in latch size was achieved by obsoleting unneeded statistics.  The sleeps 1-4 WAITERS_WOKEN, WAITS_HOLDING_LATCH columns have been made obsolete (along with some non-documented columns in x$ksllt).  We may see more latch optimizations in future releases.

Less potential for false contention
Latches typically protect multiple objects.  When a latch protects one or more hot objects, the latch itself can become a serialization point when accessing any of the objects protected by that latch.  This can be a false contention point, where the contention is for the protection mechanism (i.e. latch), rather than the target object you are attempting to access.  Unlike latches, with mutexes it is possible to create a mutex for each structure protected.  This means false contention is much less likely as each structure can be protected by its own mutex.

Mutexes as replacements for Latches, and Pins
A mutex can be concurrently referenced by many sessions, providing all sessions reference the mutex in S (Shared) mode.  The total number of sessions referencing a mutex in S mode is called the reference count (‘ref count’).  The ref count for a mutex is stored within the mutex itself.   A mutex can also be held in X (eXclusive) mode by one session only.

Mutexes have a dual nature; they can act as a serialization mechanism (e.g. latch), and also as a pin (e.g. preventing an object from aging out).  For example, the ref count of a mutex is a replacement for a library cache pin.  Instead of each session creating then deleting a library cache pin when executing a cursor, each session increments and decrements the ref count (so the ref count replaces n distinct pins).  The first time a session parses a cursor, it temporarily creates then removes a library cache pin, but subsequent parses or executes by that session only requires incrementing/decrementing the ref count.  Note that a latch get is not required when incrementing or decrementing the ref count (i.e. when creating a library cache ‘mutex pin’), as the mutex itself acts as a latch would by serializing access to update the ref count.  See ‘Understanding Mutex Waits’ below for a more detailed example.  When a session removes it’s mutex ‘pin’, it decrements the ref count (again, no latch gets are required).

Mutexes provide an efficient examination mode to support special activities such as described above where the first access to a cursor object requires a create and destroy of a pin.  The examination mode allows the client of the mutex to increment the ref count and concurrently examine the structure the mutex protects.  For example, when mutexes are used as replacement for library cache pins, bumping the ref count is performed simultaneously with determining whether this is the first time the session is accessing the heap; if so, it is necessary to pin the heap.  This examination mode is significantly faster and so cheaper than performing the equivalent action using latches.

How do mutexes interact with latches?
Latches and mutexes are independent mechanisms i.e. a process can hold a latch and a mutex at the same time.  In the case of process death, latches are always cleaned up before mutexes.  There is no generic mutex deadlock detection (unlike latches).  There is no mutex/latch hierarchy.

Mutex Usage
A client of the mutex code registers its intention to create and own mutexes.  During registration, each client defines the characteristics of the mutexes they will use:
? the maximum number of ‘where’ locations in the code
? the wait policy (e.g. how a sleep is performed)
? the cleanup policy
? the dump routine
It is the client’s responsibility to code deadlock avoidance and recovery schemes.

In 10.2, the only client of mutexes is kks (Kernel Kompile Shared) i.e. the shared cursor component of the library cache.  In future releases, other clients may also start using mutexes.

kks use of mutexes
Mutexes are used by kks for protecting a subset of operations on parent and child cursors, described below.

Parent-cursor operations
? Operations performed and their related wait events are:
o building a new cursor under a parent (‘cursor: mutex X’)
o examining a parent (‘cursor: mutex S’)

 

 

o bind capture (‘cursor: mutex X’)
? The mutex is in the parent cursor
? Mutex type:  ‘Cursor Parent’ (kgx_kks2).
? All mutex waits events for this type are of the form ‘cursor: mutex *’

Statistics block operations
? Operations performed and their related wait events are:
o build, update of cursor-related statistics blocks (‘cursor: mutex X’)
o examination of cursor-related statistics blocks (‘cursor: mutex S’)
o examination of a statistics block is usually because of access to v$sqlstats
? The mutex can be in parent cursor or the hash table for the statistics block
? Mutex type: Cursor Stat (kgx_kks1)
? All mutex wait events for this type are of the form ‘cursor: mutex *’

Replacement of library cache pin for cursor heaps
? Instead of using a traditional ‘library cache pin’, the pin is replaced by a mutex and a ref count.  This is used for pinning (e.g. for execution), or compiling a child cursor heap.
? In 10.2.0.1, the mutex code path for pin replacement is only activated when _kks_use_mutex_pin is set to true (in 10.2.0.2+, this is the default).  When this code path is in effect, library cache pins are not used, and so the ‘library cache pin*’ related wait events will not be seen
? Operations performed for this type
o Pinning a cursor for execute (‘cursor: pin S wait on X’)
o When pinning a cursor for execute, and the cursor is currently being examined by another S (‘cursor: pin S’)
o When attempting to rebuild a cursor (‘cursor: pin X‘).  This event should not be seen typically, because if a cursor is currently being used, and it needs to be rebuilt, another cursor will be created
? The mutex is in the cursor
? Mutex type: ‘Cursor Pin’ (kgx_kks3)
? All mutex waits for this type are ‘cursor: pin *’

SQL parse/execution benefits of mutex use by kks

1n 10.2, it is expected the majority of benefit using mutexes will be as follows:

? Building a new cursor under a parent
o Although this operation is cheaper, building many cursors under a parent cursor is not recommended.

? Parent examination
o When finding a cursor to execute, the parent must be examined.  The examination of the parent is also performed using a mutex.

? Re-executing SQL statements already loaded in the library cache
o Typically, a cursor must be pinned by each session before execution.
o If a cursor is left open for re-execution, and cursor_space_for_time (CSFT) is true, the cursor can be rebound, and re-executed without a session acquiring a library cache pin.  If a cursor is left open, and CSFT is not set, the session attempting to execute the cursor must get a library cache pin first.

 

from :internet search