In the last article, latches were discussed; however, there’s another more lightweight process that Oracle uses in a similar way, a mutex. So, what is a mutex? By definition it’s a mutual exclusion object that locks memory structures, so in at least one way a mutex is like a latch. In other ways it isn’t; let’s look into the mutex side of Oracle and see how they differ from latches.
A mutex is smaller than a latch but what does that mean? It’s a reference to the code path each must take to be executed; code for a latch is around 110 bytes in size (which contains anywhere between 150 and 200 instructions) while the code for a mutex is a mere 28 bytes in size (having around 30 to 35 instructions), making a mutex faster to execute. Being smaller and lighter a mutex won’t contain the level of information a latch contains; it won’t have data on who is waiting or about the length of the wait, it won’t have information on requests and misses but there is sleep data available (how many, how long) and a couple of other details that can help monitor them.
Let’s look at what benefits a mutex can provide:
- Less potential for false contention.
- Replace both latches and pins.
- Mutex structure is located in each child cursor.
Let’s look at each one of those benefits. First is the lower potential for false contention; a latch can protect multiple hash buckets so if two users were searching two different hash buckets the possibility exists that those two buckets could be protected by the same latch and, as a result, the two sessions would contend for access to that latch. With mutexes each hash bucket could easily have its own mutex for protection thus eliminating the contention to lock and access the bucket.
Second, mutexes can replace both latches and pins. A mutex can act as a serialization mechanism (just like a latch, restricting access to a single session) and also as a pin (to prevent an object from ‘aging out’ of the relevant cache). A mutex has both an exclusive and a shared mode, and in shared mode multiple sessions can access (using the term ‘reference’) the same mutex and the number of sessions currently referencing a given mutex is known as the ‘reference count’. That information is stored in the mutex. And, like a latch, a mutex can also be held in exclusive mode and the reference count of 1 could indicate an exclusive hold on that mutex. Additionally, an object cannot be aged out of a cache until the mutex reference count is 0.
Third, the mutex structure is located in each child cursor handle with the mutex itself acting as the pin structure for the cursor. Changing the pin status is now simply a matter of changing the mutex reference count; this eliminates the need to get the library cache latch making the mutex even more efficient than the latch/pin combination.
So, what information is available about mutexes and where can it be found? Oracle provides the V$MUTEX_SLEEP and V$MUTEX_SLEEP_HISTORY views that can be queried to get the following information:
SQL> desc v$mutex_sleep
Name Null? Type
----------------------------------------- -------- ----------------------------
MUTEX_TYPE VARCHAR2(32)
LOCATION VARCHAR2(40)
SLEEPS NUMBER
WAIT_TIME NUMBER
CON_ID NUMBER
SQL> desc v$mutex_sleep_history
Name Null? Type
----------------------------------------- -------- ----------------------------
MUTEX_IDENTIFIER NUMBER
SLEEP_TIMESTAMP TIMESTAMP(6)
MUTEX_TYPE VARCHAR2(32)
GETS NUMBER
SLEEPS NUMBER
REQUESTING_SESSION NUMBER
BLOCKING_SESSION NUMBER
LOCATION VARCHAR2(40)
MUTEX_VALUE RAW(8)
P1 NUMBER
P1RAW RAW(8)
P2 NUMBER
P3 NUMBER
P4 NUMBER
P5 VARCHAR2(64)
CON_ID NUMBER
SQL>
Querying V$MUTEX_SLEEP we see:
MUTEX_TYPE LOCATION SLEEPS WAIT_TIME CON_ID
-------------------------------- ---------------------------------------- ---------- ---------- ----------
Library Cache kglllal3 111 5 21885 0
Library Cache kglllal1 109 1 78 0
Library Cache kglhdgn2 106 39 136235 0
Library Cache kglpnal1 90 5 46642 0
Library Cache kglhdgn1 62 5 35087 0
Library Cache kglscn1 18 3 9972 0
Library Cache kglpin1 4 1 25173 0
Library Cache kglget2 2 2 90986 0
Cursor Parent kkscsPruneChild [KKSPRTLOC35] 4 8852 0
Cursor Parent kksfbc [KKSPRTLOC2] 2 45124 0
Cursor Pin kkslce [KKSCHLPIN2] 296 432125 0
Cursor Pin kksfbc [KKSCHLFSP2] 2 1795 0
12 rows selected.
SQL>
MUTEX_TYPE indicates the action or object the mutex protects, and in the above example there are mutexes protecting library cache objects and acting as cursor pins. LOCATION is the code location where waiters slept for the indicated mutex. SLEEPS is the number of times a requestor had to sleep before getting the mutex and WAIT_TIME is, oddly enough, the wait time in microseconds. CON_ID is the container ID, which is only relevant in 12.1 and later releases; in 11.2 and earlier the CON_ID column does not exist.
V$MUTEX_SLEEP_HISTORY provides more information:
MUTEX_IDENTIFIER SLEEP_TIMESTAMP MUTEX_TYPE GETS SLEEPS RE
QUESTING_SESSION BLOCKING_SESSION LOCATION MUTEX_VALUE P1 P1RAW P2 P3 P4
P5 CON_ID
---------------- --------------------------------------------------------------------------- -------------------------------- ---------- ---------- --
---------------- ---------------- ---------------------------------------- ---------------- ---------- ---------------- ---------- ---------- --------
-- ---------------------------------------------------------------- ----------
71628033 10-JUL-17 07.00.07.021000 AM Cursor Parent 10 4
6 133 kkscsPruneChild [KKSPRTLOC35] 0000008500000000 22 00 0 0 0
0
3495269867 10-JUL-17 07.00.08.673000 AM Cursor Pin 1 101
368 16 kkslce [KKSCHLPIN2] 0000001000000000 2 00 0 0 0
0
2252038564 10-JUL-17 07.00.07.390000 AM Library Cache 90 1
135 368 kglhdgn2 106 0000017000000000 0 000007FF8810FDC0 0 0 0
0
2252038564 10-JUL-17 07.00.07.390000 AM Library Cache 90 1
133 368 kglhdgn2 106 0000017000000000 0 000007FF8810FDC0 0 0 0
0
4087692675 10-JUL-17 07.00.07.101000 AM Library Cache 7 4
6 251 kglllal3 111 000000FB00000000 0 000007FF86347838 0 0 0
0
4039937844 10-JUL-17 07.00.08.581000 AM Library Cache 22 2
251 135 kglhdgn2 106 0000008700000000 0 000007FF86F0C1C0 0 0 0
0
4039937844 10-JUL-17 07.00.08.581000 AM Library Cache 22 4
249 135 kglhdgn2 106 0000008700000000 0 000007FF86F0C1C0 0 0 0
0
2912853027 10-JUL-17 07.00.07.277000 AM Cursor Pin 1 60
135 368 kkslce [KKSCHLPIN2] 0000017000000000 2 00 0 0 0
0
2912853027 10-JUL-17 07.00.07.277000 AM Cursor Pin 1 59
133 368 kkslce [KKSCHLPIN2] 0000017000000000 2 00 0 0 0
0
2034504591 10-JUL-17 07.00.01.555000 AM Library Cache 3 1
123 6 kglhdgn2 106 0000000600000000 0 000007FF86F64210 0 0 0
0
3323840122 10-JUL-17 07.00.01.548000 AM Library Cache 3 1
123 6 kglhdgn2 106 0000000600000000 0 000007FF86F8B300 0 0 0
0
71628033 10-JUL-17 07.00.06.980000 AM Cursor Parent 9 2
133 364 kksfbc [KKSPRTLOC2] 0000016C00000000 22 00 0 0 0
0
1264706735 10-JUL-17 07.00.01.554000 AM Library Cache 3 1
6 123 kglhdgn2 106 0000007B00000000 0 000007FF86F68DD0 0 0 0
0
746657336 10-JUL-17 07.00.06.893000 AM Library Cache 38 1
364 0 kglpnal1 90 00 0 000007FF864AF268 0 0 0
0
...
Some of the same information in V$MUTEX_SLEEP is also present in v$MUTEX_SLEEP_HISTORY but WAIT_TIME is essentially replaced with SLEEP_TIMESTAMP and the number of gets is reported along with additional information on the requesting session and any blocking session for the mutex. ‘Internal use’ information will be found in the P1, P1RAW, P2, P3, P4 and P5 columns; P1, P2 and P3 can contain the hash value of the library cache objects under contention as well as other information. The PARAMETER1-3 columns in V$EVENT_NAME and the P1TEXT, P2TEXT and P3TEXT columns in V$SESSION_WAIT can shed light on what values those columns can hold and their meaning. Since such information is release-specific the following query can help identify what additional information may be available in the V$MUTEX_SLEEP_HISTORY view:
select name, parameter1, parameter2, parameter3
from v$event_name
where name in (select event from v$session_wait where (p1,p2,p3) in (select p1, p2, p3 from v$mutex_sleep_history));
The above query is provided to help identify possible additional data in the V$MUTEX_SLEEP_HISTORY view; test it on your own systems to see what results are provided.
Since it may not be obvious, latches and mutexes are independent mechanisms; a process can hold
As of release 11.2.0.x the following latches have been replaced by mutexes:
- library cache pin allocation
- library cache lock allocation
- library cache hash chains
- library cache lock
- library cache
- library cache pin
The library cache waits listed below no longer exist in releases 11.2.0.x and later:
- latch: library cache
- latch: library cache lock
- latch: library cache pin
As good as mutexes are there are still conditions, like excessive hard parsing and poor application cursor management (where cursors are closed after every execution and no cursor caching is in effect), that can cause hash collisions or continuous traversing of library cache hash chains and, thus, contention. On the whole, however, mutexes have improved concurrency and considerably reduced latch and pin contention.
