Overview

We have all heard that Oracle has a great Locking mechanism, way superior than other databases. While in different databases, readers can block writers and writers can block readers, Oracle doesn't allow that by default. In oracle you can select without a problem (although there is an exception to that rule) and you can write without being afraid that someone is selecting your data.

The hearth of this mechanism is the Multi Versioning. Meaning Oracle keeps several copies of the same data which is consistent with different points of time. For example, one row can be consistent with 12:00 and other row can be consistent with 11:50. Both of these rows are in the buffer cache.

That is why when you start a select you are having a CONSISTENT (with the point of time your query started) GET and when you do DML you are having a CURRENT (the latest version of the data) GET.

In order to protect and achieve that mechanism oracle is using several structures:

• Latches
• Mutexes
• Enqueues (Generally known as Locks)

Let's examine each of the structures now:

Latches are lightweight serialization control structure devices. WoW that was a lot of terms used here :) So let's explain it a little bit:

• lightweight - Meaning they don't cause a lot of overhead to support. They are generally around 100-200 bytes of data in the SGA and they hold between 150-200 instructions)
• Serialization - Meaning they can process only one request at a time
• Control - They control access the shared resources (Library caches objects mostly: cursors and etc.)

Latches also come in 2 flavors:

• Shared read (Common in 9i, although you can also get exclusive shared read….don't ask :D )
• Exclusive

Latches also can be categorized in two ways regarding their activity:

• Willing to wait (Most of them)
• Immediate gets

Latch usually start as immediate and then transform into willing to wait, but that is a story for another time :)

In a nutshell, Latch is a combination of memory location in the SGA (around 100-200 bytes big) and CPU atomic capability of checking and changing the value in that location. They can be held only by one process and everybody else has to wait. It is also import to notice that the latch structure doesn't allow queuing. Meaning, whoever gets it first, it's his :)

The logic of the latch is:

“If I can set the latch memory location to some value N, then I can do something with the structure that the latch is protecting.”

To obtain a latch a session is usually doing the following (depends on the request: Willing to wait or exclusive), but the logic is pretty similar:

1. Set register X to point at latch address A
2. If value at address A is zero set it to 0xff ***
3. If the value at address A is set to 0xff then you “own” the latch
4. If not then go back to the top and try again—for a couple of thousand attempts

We were speaking of what latches are and how to obtain then. But what really they protect. Well Latches protect shared resources, mostly library caches objects, such as cursors. Library objects and objects in general, in Oracle, are chained into linked lists, that is a structure which keeps the location of all members.

Meaning that each member knows the location of the next member. Each linked list has a hash bucket (function) which acts as a quick search option. So if we want to know if a certain cursor is in a certain linked list we check the hash value of our select through the “hash bucket”. If the hash value match, then the cursor has to be in that linked list.

In Oracle ⇐10g and even in early 11g. A lot of hash buckets and their linked lists were protected by a single latch. Of course that create a lot of contention even for a simple selects.

You can observer graphical representation of that structure:

Latches are good control structures but they have their flows. For example if the session fails to get the latch it will spin couple thousand times before going to sleep, That spinning is causing CPU time which can be spend for other more “productive job”.

It is worth mentioning that latches aren't used so much in newer Oracle version: >=11g. They were replaced by other structure called MUTEX, which is way more optimal and efficient :)

Mutexes are very similar to latches, in a such a way that they also are lightweighted serializable control structures. Furthermore, Oracle changed most of the latches and KGL locks/pins with Mutexes in 11.2+.

The mutex usually have around 28 bytes and have around 30-35 instructions. Furthermore, mutexes are way more than latches. If a latch used to protect many hash buckets, a mutex protect the resource itself. For example a parent and a child cursor will be protected by a single mutex. That allows the application to scale better. Furthermore, the mutex eliminates the process spinning since when a mutex is taken, the process will go immedietely to sleep instead of trying couple thousands times to take it again.

Also, it is import to notice that a latch is taken by process, while mutexes is taken by a session. Since a process can have many sessions (shared server), a mutex will not allow a deadlock by a single process.

If you have to compare latches to Mutexes, they are pretty similar, just mutexes are protecting way smaller groups of objects than latches and they are way smaller. They have the same logic: “If I can change the value of the mutex then I can do something with the objects it protects”. Also, they doesn't cause so much CPU Spinning like Latches.

Mutexes usually protect Cursors and there are quite a lot of them. We can check the mutexes for cursors as follows:

SQL> select MUTEX_IDENTIFIER, MUTEX_TYPE, max(gets), max(sleeps), mutex_value from v$mutex_sleep_history where mutex_type='Cursor Pin' group by MUTEX_IDENTIFIER, MUTEX_TYPE, mutex_value order by 3, 4;
 
MUTEX_IDENTIFIER MUTEX_TYPE  MAX(GETS) MAX(SLEEPS) MUTEX_VALUE
---------------- ---------- ---------- ----------- ----------------
      2036683327 Cursor Pin   17253663        8307 000008F700000001
      2257295401 Cursor Pin   17379825       11124 0000060400000001
      1698288247 Cursor Pin   19236392        8685 0000050500000002
      2941537973 Cursor Pin   20117059       10325 0000000000000001
      1154347790 Cursor Pin   23564488       12986 0000012000000000
      1154347790 Cursor Pin   24633208       18732 00
      1184750592 Cursor Pin   32632816       14100 0000060400000002
      1184750592 Cursor Pin   32643585        9503 0000060400000001
       345957031 Cursor Pin   34761467       10249 0000000000000001

This query shows us the amount of gets and misses per mutex. Don't forget that each time a mutex is missed, it will try again or go to sleep. We can check the actual plan as follows:

SQL> select sql_id, sql_text, version_count from v$sqlarea where hash_value=345957031;

SQL_ID        SQL_TEXT                       VERSION_COUNT
------------- ------------------------------ -------------
2ff8mkna9xsp7 select item_daily_uno ,chain_d             1
              aily_uno ,rank ,num_in_rank ,s
              eat_category ,seat_class ,seat
              _no ,rest_begin ,rest_end ,las
              t_update  from inflight_rest w
              here chain_daily_uno=:b0

From the code it is visible, that this query is being executed A LOT of times and this can lead to contentions with that mutex:

> set linesize 800
set pages 20000
col SAMPLE_TIME for a30
col event for a30
col blocking_session for a10
col WAIT_CLASS for a15
col BLOCKING_SESSION_SERIAL# for 999999
col BLOCKING_SESSION_SERIAL# for a20
col SQL_CHILD_NUMBER for 99999
select snap_id,EVENT,count(*)
from dba_hist_active_sess_history
where snap_id between  23910 and 23922
group by snap_id,EVENT
order by count(*);
   23920 cursor: pin S wait on X              1729
     23917 cursor: pin S wait on X              1850
     23922 cursor: pin S wait on X              2135
     23919 cursor: pin S wait on X              2573
     23918 cursor: pin S wait on X              2629
     23916 cursor: pin S wait on X              2918
     23914 cursor: pin S wait on X              2967
     23914 latch free                           3018
     23915 cursor: pin S wait on X              3077