Latch/Mutex/Enqueue Contention, Hot Blocks, Sequences, ITL, and Concurrency Hotspots

Learning outcomes

ServiceHub adds more application workers but throughput stops scaling. Some sessions block on rows, library-cache mutexes rise during a parse storm, and a high-rate insert table develops a hot right-hand index leaf. “Increase processes” cannot solve any of these mechanisms. Oracle concurrency diagnosis first distinguishes latches/mutexes protecting in-memory structures, enqueues coordinating transactional/resources, and—in RAC—global-cache/enqueue coordination across instances.

1. Three coordination layers

A wait name is the starting point. The fix depends on the protected object and application behavior.

2. Reproduce a TX row-lock enqueue

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE sh23_contend PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE sh23_contend (  id NUMBER PRIMARY KEY,  status_code VARCHAR2(12) NOT NULL,  amount NUMBER(10,2) NOT NULL)INITRANS 2;INSERT INTO sh23_contend VALUES(1,'OPEN',100);COMMIT;

BEGIN  DBMS_APPLICATION_INFO.SET_MODULE('SH23_CONTEND','HOLDER');END;/UPDATE sh23_contendSET amount=amount+1WHERE id=1;-- Leave transaction open.

BEGIN  DBMS_APPLICATION_INFO.SET_MODULE('SH23_CONTEND','WAITER');END;/UPDATE sh23_contendSET status_code='HOLD'WHERE id=1;-- Waits on Session A.

SELECT  sid,  serial#,  module,  event,  wait_class,  seconds_in_wait,  blocking_session,  final_blocking_sessionFROM v$sessionWHERE module='SH23_CONTEND'ORDER BY sid;

This is not an ITL shortage and not a latch problem: two transactions want the same row. Increasing INITRANS does nothing for a logical row conflict.

3. Repair transaction scope rather than instance parameters

ROLLBACK;

ROLLBACK;BEGIN DBMS_APPLICATION_INFO.SET_MODULE(NULL,NULL); END;/

In production, shorten the business transaction, remove think time while locks are held, access rows in a consistent order, and use retry/queue semantics appropriate to correctness. Killing blockers is an incident action, not a concurrency design.

4. Latch and mutex evidence

SELECT  name,  gets,  misses,  sleeps,  wait_timeFROM v$latchWHERE gets > 0ORDER BY wait_time DESCFETCH FIRST 20 ROWS ONLY;

SELECT  event,  total_waits,  time_waited_microFROM v$system_eventWHERE event LIKE '%mutex%'ORDER BY time_waited_micro DESC;

Lifetime misses/sleeps are not proof of a current bottleneck. Snapshot them across the affected interval and correlate with hard parse/cursor evidence from Chapter 10. Do not tune undocumented spin counts or hidden mutex/latch parameters.

5. Sequence and index hot spots

A conventional monotonically increasing sequence feeds adjacent keys to the right-most B-tree leaf. Under high insert concurrency, that leaf can become a serialization point. In RAC it can additionally move between instances through Cache Fusion. Oracle recommends CACHE for sequences in RAC; NOORDER avoids global ordering work when business semantics do not require request order.

CREATE SEQUENCE sh23_event_seq  START WITH 1  INCREMENT BY 1  CACHE 1000  NOORDER;

The cache value is illustrative. Larger caches reduce sequence metadata refill frequency but increase potential gaps after crash/restart. Sequence gaps are normal and must not encode legal/accounting continuity.

6. Scalable sequence awareness

CREATE SEQUENCE sh23_event_scale_seq  SCALE  CACHE 1000  NOORDER;

A scalable sequence adds an instance/session-derived offset so generated values spread across index key space and reduce sequence/index block contention. Values become less compact/chronological; test key width, locality and application assumptions. Do not combine SCALE with ORDER as a casual recipe.

7. Segment statistics point at hot objects

SELECT  owner,  object_name,  subobject_name,  object_type,  statistic_name,  valueFROM v$segment_statisticsWHERE statistic_name IN (  'buffer busy waits',  'ITL waits')  AND value > 0ORDER BY value DESC;

For a right-growing index, also correlate SQL, insert rate and block-level waits. In RAC add gc current... segment statistics from Chapter 18. A reverse-key index can spread sequential inserts but degrades ordinary range scans on the original key order; use it only when access-pattern evidence fits.

8. ITL pressure is different from row locking

Each data/index block has Interested Transaction List (ITL) slots tracking concurrent block modifications. If many transactions need the same block and cannot obtain/grow an ITL slot, ITL waits can rise. Only then should storage/block design such as INITRANS be evaluated.

ALTER TABLE servicehub_owner.some_hot_table  INITRANS 8;-- Example only. Existing blocks may require move/rebuild/reorganization-- before all blocks reflect the new space reservation behavior.

8 is not a universal setting. Higher INITRANS reserves more block header space and can reduce row capacity per block. Fixing hot-row logic or spreading inserts may be better.

9. Deliberately wrong: raise every concurrency parameter

Increasing PROCESSES, session counts, undocumented spin settings or random cache sizes can create more contenders and memory pressure without changing the hot row/block/cursor. The repair is to map wait → protected resource → SQL/object/application pattern → targeted change → same-load retest.

10. Cleanup

DROP TABLE sh23_contend PURGE;DROP SEQUENCE sh23_event_seq;DROP SEQUENCE sh23_event_scale_seq;

11. Production judgment

Use interval wait/latch/mutex evidence and object/session attribution. TX row conflicts require transaction/application repair; parse/mutex contention requires cursor/bind/shared-pool diagnosis; ITL waits require block-level concurrency analysis; sequential insert hot spots can justify cached/scalable sequences or index/data-layout changes. In RAC, service/data affinity may reduce cross-instance hot-block movement.

The mandatory lab is Free/single-instance and requires no pack, restart or COMPATIBLE change. RAC global waits require a licensed RAC topology and are not simulated. Lesson 4 follows another concurrency amplifier: many simultaneous sort/hash workareas can exhaust PGA and spill into TEMP.

Authoritative references

• V$LATCH — latch counters
• Wait Events — mutex/enqueue wait meanings
• V$SEGMENT_STATISTICS — object-level ITL/buffer-busy statistics
• CREATE SEQUENCE — CACHE/NOORDER/SCALE semantics
• Managing Sequences — scalable-sequence contention rationale