Table of Contents
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
SQL> column name format A32 truncate heading "LATCH NAME" SQL> column pid heading "HOLDER PID" SQL> column name format A32 truncate heading "LATCH NAME" column pid heading "HOLDER PID" select c.name,a.addr,a.gets,a.misses,a.sleeps, a.immediate_gets,a.immediate_misses,b.pid from v$latch a, v$latchholder b, v$latchname c where a.addr = b.laddr(+) and a.latch# = c.latch# order by a.latch#; LATCH NAME ADDR GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES HOLDER PID -------------------------------- ---------------- ---------- ---------- ---------- -------------- ---------------- ---------- PC and Classifier lists for WLM 0000000060001008 0 0 0 0 0 post/wait queue 000000006000AA50 2928430 11793 6 3766322 66095 hot latch diags 000000006000AAF0 0 0 0 0 0 event stats latch 000000006000AB90 22019 0 0 0 0 test excl. non-parent l0 000000006000AC30 0 0 0 0 0 test excl. parent l0 000000006000ACD0 16 0 0 0 0 test excl. parent2 l0 000000006000AD70 16 0 0 0 0 test shared non-parent l0 000000006000AE10 0 0 0 0 0 test excl. non-parent lmax 000000006000AEB0 0 0 0 0 0 test excl. parent2 lmid cln 000000006000AF50 16 0 0 0 0 -------------------------------------------------------------------------------------------------------------------------------
Let's further examine the most gets/misses/sleeps
Most Gets/Misses/Sleeps
SQL> SET LINESIZE 200
SET VERIFY OFF
SELECT *
FROM (SELECT name,
addr,
gets,
misses,
sleeps
FROM v$latch_children
WHERE name = 'cache buffers chains'
AND misses > 0
ORDER BY gets DESC)
WHERE rownum < 11;SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11
LATCH NAME ADDR GETS MISSES SLEEPS
-------------------------------- ---------------- ---------- ---------- ----------
cache buffers chains 00000015E20F7FD0 80117072 12433 1
cache buffers chains 00000015E28B5410 69023575 32689 1
cache buffers chains 00000015E2240B90 62530840 6372 0
cache buffers chains 00000015C2B81478 44844620 1942 0
cache buffers chains 00000015C267BBB8 39572452 3678 0
cache buffers chains 00000015C3128F78 38197451 307 28
cache buffers chains 00000016024085C0 34526929 5363 0
cache buffers chains 00000015E29B71D0 33984067 4204 0
cache buffers chains 00000015C2C1CFF8 26825192 2296 0
cache buffers chains 00000015C2E1CDF8 24599155 57614 0
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
ACCEPT address PROMPT "Enter ADDR: "
COLUMN owner FORMAT A15
COLUMN object_name FORMAT A30
COLUMN subobject_name FORMAT A20
SELECT *
FROM (SELECT bh.hladdr,
o.owner,
o.object_name,
o.subobject_name,
o.object_type,
bh.tch,
bh.obj,
bh.file#,
bh.dbablk,
DECODE(bh.class,1,'data block',
2,'sort block',
3,'save undo block',
4,'segment header',
5,'save undo header',
6,'free list',
7,'extent map',
8,'1st level bmb',
9,'2nd level bmb',
10,'3rd level bmb',
11,'bitmap block',
12,'bitmap index block',
13,'file header block',
14,'unused',
15,'system undo header',
16,'system undo block',
17,'undo header',
18,'undo block') AS class,
DECODE(bh.state, 0,'free',
1,'xcur',
2,'scur',
3,'cr',
4,'read',
5,'mrec',
6,'irec',
7,'write',
8,'pi',
9,'memory',
10,'mwrite',
11,'donated') AS state
FROM x$bh bh,
dba_objects o
WHERE o.data_object_id = bh.obj
AND hladdr IN (SELECT addr
FROM v$latch_children
WHERE name='cache buffers chains'
and addr='00000015C2E1CDF8')
ORDER BY tch DESC);
HLADDR OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE TCH OBJ FILE# DBABLK CLASS STATE
---------------- --------------- ------------------------------ -------------------- ----------------------- ---------- ---------- ---------- ---------- ------------------ -------
00000015C2E1CDF8 XCREW ITEM_DAILY TABLE 173 172650 18 3209239 data block xcur
00000015C2E1CDF8 XCREW DUTY TABLE 43 173197 22 212524 data block xcur
00000015C2E1CDF8 XCREW CHAIN_ITEM_DAILY TABLE 41 173041 21 2864141 data block xcur
00000015C2E1CDF8 XCREW ROSTER_TAG TABLE 25 447074 22 3222873 data block xcur
00000015C2E1CDF8 XCREW DUTY TABLE 18 173197 21 2546330 data block xcur
00000015C2E1CDF8 XCREW ILLG_MSG TABLE 5 173010 20 2898245 data block xcur
00000015C2E1CDF8 SCHEDOPS ISEGMENT_INFO INDEX 4 132692 12 147397 data block xcur
00000015C2E1CDF8 XCREW DUTY TABLE 1 173197 20 2384016 data block xcur
00000015C2E1CDF8 XCREW AFS_LOG TABLE 1 173534 20 3216056 data block xcur
00000015C2E1CDF8 XCREW XPKDAILY_ACCOUNT INDEX 0 173877 24 1286504 data block xcur
00000015C2E1CDF8 XCREW DUTY TABLE 0 173197 18 2377199 data block xcur
We can also search for the most misses:
Most misses
SQL> SELECT
addr,
sleeps
FROM
v$latch_children c,
v$latchname n
WHERE
n.name='cache buffers chains' and
c.latch#=n.latch# and
sleeps > 100
ORDER BY sleeps 2 3 4 5 6 7 8 9 10 11
12 ;
ADDR SLEEPS
---------------- ----------
00000015C31212F8 128
00000015C32020B8 177
00000015E27150D0 198
0000001602639EC0 225
00000016025F30C0 280
0000001602E696C0 294
0000001602291140 300
00000015E2B82650 548
00000015C2EA0138 1879
And of course we can see what is behind that latch: '00000015C2EA0138'
Most sleepy latch
SQL> COLUMN owner FORMAT A15
COLUMN object_name FORMAT A30
COLUMN subobject_name FORMAT A20
SELECT *
FROM (SELECT bh.hladdr,
o.owner,
o.object_name,
o.subobject_name,
o.object_type,
bh.tch,
bh.obj,
bh.file#,
bh.dbablk,
DECODE(bh.class,1,'data block',
2,'sort block',
3,'save undo block',
4,'segment header',
5,'save undo header',
6,'free list',
7,'extent map',
8,'1st level bmb',
9,'2nd level bmb',
10,'3rd level bmb',
11,'bitmap block',
12,'bitmap index block',
13,'file header block',
14,'unused',
15,'system undo header',
16,'system undo block',
17,'undo header',
18,'undo block') AS class,
DECODE(bh.state, 0,'free',
1,'xcur',
2,'scur',
3,'cr',
4,'read',
5,'mrec',
6,'irec',
7,'write',
8,'pi',
9,'memory',
10,'mwrite',
11,'donated') AS state
FROM x$bh bh,
dba_objects o
WHERE o.data_object_id = bh.obj
AND hladdr IN (SELECT addr
FROM v$latch_children
WHERE name='cache buffers chains'
and addr='00000015C2EA0138')
ORDER BY tch DESC);
SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48
HLADDR OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE TCH OBJ FILE# DBABLK CLASS STATE
---------------- --------------- ------------------------------ -------------------- ----------------------- ---------- ---------- ---------- ---------- ------------------ -------
00000015C2EA0138 XCREW PERIODIC_ACCOUNT TABLE 254 172818 21 84048 data block xcur
00000015C2EA0138 XCREW XPKITEM_DAILY INDEX 237 174076 25 1361263 data block xcur
00000015C2EA0138 SCHEDOPS I_TXQ_STATE INDEX 204 132572 12 891882 data block xcur
00000015C2EA0138 XCREW ITEM_DAILY TABLE 154 172650 18 2803873 data block xcur
00000015C2EA0138 IOCC ACTIVEMQ_ACKS TABLE 83 132226 55 63100 data block xcur
00000015C2EA0138 SCHEDOPS LEG_REMARK TABLE 25 132191 9 805439 data block xcur
00000015C2EA0138 XCREW DAILY_ACCOUNT TABLE 8 173040 18 2607455 data block xcur
00000015C2EA0138 XCREW XPKEVENT_ACCOUNT INDEX 2 174386 27 1939174 data block xcur
00000015C2EA0138 IOCC ACTIVEMQ_ACKS TABLE 1 132226 55 63100 data block cr
00000015C2EA0138 IOCC ACTIVEMQ_ACKS TABLE 1 132226 55 63100 data block cr
00000015C2EA0138 SCHEDOPS PUBLISHED_VERSION TABLE 1 132325 9 487628 data block xcur
00000015C2EA0138 IOCC ACTIVEMQ_ACKS TABLE 1 132226 55 63100 data block cr
00000015C2EA0138 IOCC ACTIVEMQ_ACKS TABLE 1 132226 55 63100 data block cr
00000015C2EA0138 IOCC ACTIVEMQ_ACKS TABLE 1 132226 55 63100 data block cr
00000015C2EA0138 IOCC ACTIVEMQ_ACKS TABLE 1 132226 55 63100 data block cr
00000015C2EA0138 SCHEDOPS I_PUB_VERSION_FLT INDEX 0 132567 12 59842 data block xcur
00000015C2EA0138 SCHEDOPS SEGMENT_INFO TABLE 0 132252 10 135713 data block xcur
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
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:
Check the cursor
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:
Mutex Contention
> 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
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