======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.jpg?600 |}} 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: 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 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. 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: 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' 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: 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 =====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