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

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:

Set register X to point at latch address A

If value at address A is zero set it to 0xff ***

If the value at address A is set to 0xff then you “own” the latch

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 :)

Latch present a contrast with enqueues which always protect structures which has some disk based equivalent such as database objects, data blocks or table rows. Another significant difference between latch and enqueue In latches there is no ordered queue of waiters like in enqueues. Latch waiters may either use timers to wake up and retry or spin (only in multiprocessors). Since all waiters are concurrently retrying (depending on the scheduler), anyone might get the latch and conceivably the first one to try might be the last one to get.

We can check all the latches as follows:

All latches

Let's further examine the most gets/misses/sleeps

Most Gets/Misses/Sleeps

From that we can see that latch child with addr: '00000015C2E1CDF8' has a lot of gets and some misses but 0 sleeps.

Behind a child Latch

We can also search for the most misses:

Most misses

And of course we can see what is behind that latch: '00000015C2EA0138'

Most sleepy latch

KGL Locks & Pins

Mutexes

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:

Cursor Mutex

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:

Check the cursor

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:

Mutex Contention

Enqueues

Enqueues are your standard locks and they come in several tyes:

TX: Transaction lock:

* mode 6: data modification,

* mode 4: setting tablespace to read only, foreign key enforcement, unique index key enforcement, ITL space waits, and more.

TM: Table modification (or locking a referenced table when the foreign key is not indexed)

UL: Lock used by user applications, a lock is requested by DBMS_LOCK.REQUEST

CF: Control file: actions accessing the control file, like: log switch, redo archiving, and backup

ST: Space Transaction: dictionary-managed tablespaces within extent allocations and releases

TT: Temporary tablespace: parallel tablespace operations

CI: Cross-instance: data accessed from multiple instances (db link)

HV: Parallel direct-path insert