Basics: Oracle Enqueue Waits & Causes

1. What are Oracle enqueues?
Oracle enqueues are locks at database level that coordinate parallel access to Oracle resources such as objects or data records. For example, enqueues are responsible for several transactions not being able to change the same data record at the same time.Enqueue requests are divided into queues whose requests are then processed in the chronological sequence of their arrival. This is an important difference to Oracle latches (Note 767414), for which there are no queues.Oracle enqueues are often also referred to as exclusive lockwaits.

2. What happens if a requested enqueue has already been allocated?
There are no more timeouts in connection with enqueues. Either an error occurs immediately, or the session waits as long as it has to:

Enqueue requests with NOWAIT
If an enqueue with NOWAIT is requested (for example, as part of a DDL statement without an ONLINE option) and the enqueue is already being held by another session, the following error message is issued:
ORA-00054: resource busy and acquire with NOWAIT specified

Enqueue requests without NOWAIT
If an enqueue without NOWAIT is requested (for example, by SAP transactions), the session waits as long as required if another session is holding the requested enqueue.

3. How can I determine whether I have problems with Oracle enqueues?
Carry out a wait event analysis in accordance with Note 619188 to determine whether the general database performance is impaired by enqueues or whether long-running transactions must wait for enqueues.While there was only one generic wait event "enqueue" up until Oracle 9i, Oracle 10g or higher differentiates between the different enqueue types and causes for the wait event. For example, "enq: TX - allocate ITL entry" denotes an enqueue in the Interested Transaction List in the block header.

4. What are the different types of Oracle enqueues?
Oracle enqueues are always specified in the form of a 2-digit ID. User enqueues and system enqueues are differentiated as follows:

User enqueues:

• TX (transaction enqueue): This enqueue type occurs if you want to change a data record but you cannot do this because a transaction is running in parallel (for example, because the transaction changed the same data record because a unique or primary constraint cannot be guaranteed or because a free ITL slot is no longer available in the block header).
• TM (DML enqueue): This enqueue type occurs if a complete object has to be protected against changes (for example, as part of an index rebuild or a consistency check using VALIDATE STRUCTURE). Whenever a TX enqueue blocks table entries, a TM enqueue is also set so that parallel activities such as index rebuilds or consistency checks are not possible. ONE TM enqueue is set for each transaction and changed object.
• UL (user-defined enqueue): A transaction has set an enqueue using DBMS_LOCK.REQUEST

System enqueues:

• ST (space transaction enqueue): This enqueue is held in dictionary-managed tablespaces within extent allocations and releases.
• CI (Cross instance call invocation enqueue)
• TT (Temporary table enqueue)
• US (Undo segment enqueue)
• CF (Control file enqueue)
• TC (Thread checkpoint enqueue)
• RO (Reuse object enqueue)
• ... and so on However, there are numerous other system enqueues which are generally negligible because system enqueues are only held for a very short time.

As of Oracle 10g, the table V$LOCK_TYPE contains an overview of all enqueues that exist.

5. Which Oracle parameters play a role in the enqueue environment?
DML_LOCKS
The DML_LOCKS parameter specifies the maximum number of requests of TM enqueues that may be active simultaneously. If the limit is reached, ORA-00055 occurs (see Note 398927).

ENQUEUE_RESOURCES
The ENQUEUE_RESOURCES parameter specifies the maximum total number of enqueue requests that may be active simultaneously. If the limit is reached, ORA-00052 occurs.
To determine which limits are defined, what the previous highest level was and how many requests are currently active, you can execute the following SELECT on V$RESOURCE_LIMIT:

 

SQL> SELECT * FROM V$RESOURCE_LIMIT WHERE RESOURCE_NAME IN ('dml_locks', 'enqueue_resources');

 

6. In which modes can I hold or request an enqueue?

• 0: Enqueue is not held or requested
• 1: null (NULL)
• 2: row-S (SS)
• 3: row-X (SX)
• 4: share (S)
• 5: S/Row-X (SSX)
• 6: exclusive (X)

7. How do I find out which sessions are currently holding enqueues or waiting for enqueues?
The V$SESSION_WAIT view or the Oracle session overview  displays the wait event "enqueue" (Oracle 9i or lower) or "enq: <type> - <description>" (Oracle 10g or higher) if a session is waiting for an enqueue (see Note 619188).

The V$LOCK Oracle view contains all information about sessions that hold or wait for an enqueue:

SID: SID of the Oracle session that holds the enqueue or waits for it

Associated Type: Enqueue type

ID1, ID2: Enqueue-dependent lock IDs

TM enqueue -> ID1 = Object ID of the locked object

LMODE: Mode of the held enqueues

REQUEST: Mode of the requested enqueues

CTIME: Time in current mode (in seconds), that is, hold or queue time

BLOCK: 0 -> no session waits for the enqueue; 1 -> at least one session waits for the enqueue

For sessions that hold an enqueue, REQUEST is 0 and LMODE is higher than 0. For sessions that wait for an enqueue, REQUEST is greater than 0 and LMODE is 0.  In the Oracle session overview, you can recognize the sessions that wait for an enqueue (REQUEST > 0) by the fact that they wait for an "enqueue" wait event.

You can identify sessions that want to access the same enqueue and are thus locked by identical entries for TYPE, ID1 and ID2.

9. How I can determine the enqueue type for which a session is currently waiting?

As of Oracle 10g, the enqueue type is taken directly from the name of the wait event.Up to Oracle 9i, you can identify the enqueue type using V$LOCK.If only the current information from V$SESSION_WAIT is available , you can determine the enqueue type using the first P1 wait event parameter:

SQL> SELECT CHR(BITAND(<type>, -16777216) / 16777215) ||
       CHR(BITAND(<type>, 16711680) / 65535)
FROM DUAL;

10. How I can determine how long a session has been waiting for certain enqueues since the database was started?
As of Oracle 10g, the information can also be obtained from V$SYSTEM_EVENT due to the differentiated wait event. With the following SELECT, you get the ten enqueue types that are responsible for the highest total queue time:

SELECT * FROM
(SELECT SUBSTR(EVENT, 1, 50) EVENT, TIME_WAITED
FROM V$SYSTEM_EVENT
WHERE EVENT LIKE 'enq: %'
ORDER BY TIME_WAITED DESC)
WHERE ROWNUM <=10;

11. How I can determine which tables are most responsible for TX enqueue waits?
As of Oracle 9I, V$SEGMENT_STATISTICS contains information about TX enqueues in the application ("row lock waits") and ITL TX enqueues ("ITL waits"). You can determine the top ten of the segments in terms of the following two categories as follows:

SELECT * FROM
   (SELECT OBJECT_NAME, SUBSTR(STATISTIC_NAME, 1, 30), VALUE
   FROM V$SEGMENT_STATISTICS
   WHERE STATISTIC_NAME = 'ITL waits' OR
         STATISTIC_NAME = 'row lock waits'
   ORDER BY VALUE DESC )
WHERE ROWNUM <=10;

Note that only the NUMBER, but not the DURATION of enqueue wait situations is recorded. Therefore, the returned objects may not actually be responsible for the longest enqueue wait situations.

As of Oracle 9i, a further indication of enqueue problems is the time it takes to execute an SQL statement. , you can use a database query such as the following one to determine the SQL statements that take the longest to be executed (times are in microseconds):

column SQLTEXT word_wrap format A43
column READS wrapped format A8
column ELAPEREXEC wrapped format A8
column ELATIME wrapped format A8
column CPUTIME wrapped format A8
SELECT * FROM
(SELECT TO_CHAR(ELAPSED_TIME) ELATIME,
         TO_CHAR(DECODE(EXECUTIONS, 0, 0,
           TRUNC(ELAPSED_TIME / EXECUTIONS))) ELAPEREXEC,
         TO_CHAR(CPU_TIME) CPUTIME,
         TO_CHAR(DISK_READS) READS,
         MODULE || SQL_TEXT SQLTEXT
FROM V$SQL
ORDER BY DECODE(EXECUTIONS, 0, 0,
   TRUNC(ELAPSED_TIME / EXECUTIONS)) DESC)
WHERE ROWNUM <=10;

As of Oracle 10g, it is possible to determine which objects are mainly responsible for enqueue waits by using the V$ACTIVE_SESSION_HISTORY view for the recent past. See Note 619188 for more information.

Furthermore, as of Oracle 10g, you can determine those SQL statements that waited the most for wait events of type "Application". In many cases, this corresponds to the TX enqueue waits:

SELECT * FROM
(SELECT
     ROUND(APPLICATION_WAIT_TIME / 1000000)
       "APPLICATION WAIT TIME (S)",
     SQL_ID,
     SQL_TEXT
   FROM
     V$SQLSTATS
   ORDER BY APPLICATION_WAIT_TIME DESC )
WHERE ROWNUM <=10;

12. How do I find the table and the data record that is responsible for a TX enqueue?
If you identified a session that holds a TX enqueue over a long period of time, you can use the ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK# and ROW_WAIT_ROW# columns of V$SESSION to determine the relevant object and the relevant entry.

The following statement generates an additional SQL statement that returns the data record on which the session <sid> must wait due to a TX enqueue:

 
SELECT 'SELECT * FROM "' || O.OWNER || '"."' || O.OBJECT_NAME || '"
WHERE ROWID = DBMS_ROWID.ROWID_CREATE(1, ' || S.ROW_WAIT_OBJ# || ', ' ||
   S.ROW_WAIT_FILE# || ', ' || ROW_WAIT_BLOCK# || ', ' ||
   ROW_WAIT_ROW# || ');'
FROM DBA_OBJECTS O, V$SESSION S
WHERE S.ROW_WAIT_OBJ# = O.OBJECT_ID AND S.SID = <sid>;

13. How can I trace the enqueue requests of a session?
In individual cases it may make sense to analyze which enqueues are requested by a certain activity in the process. You can obtain this information (for example, during the reproduction on SQLPLUS level) by activating the Oracle event 10704:

ALTER SESSION SET EVENTS = '10704 trace name context forever, level 10';

14. What are user Locks?

Two types,

TX - Transaction Lock

Mode 6: Modifying same row

Mode 4: several reasons

TM - Table Modification

Mode 4: Unindexed Foreign Key

UL - User Lock

15. Explain more about TX Locks for lock mode 4 & 6,

TX = Transaction = Wait on UNDO (means waiting for undo), has two modes

Mode 6 (exclusive)

• modification of a row lock

Mode 4 (share)

• Index block spilt
• Unique Index Key enforcement
• Foreign key enforcement
• ITL space waits
• Bitmap chunk conflicts
• Alter tablespace … read only;
• Free Lists slot waits
• Possible with two phase commit

TX mode 6 is straight forward, just two sessions trying to change the same data

TX mode 4 has many reasons and historically has been hard to diagnose, but now in 10g Oracle had broken down TX locks in to these sub types:

enq: TX - row lock contention

• pk or unique index violation
• fk violation
• bitmap chunk wait
• Others ?

Wait on an ITL slot

Index block split

enq: TX - contention

• Wait for a data file extension
• Alter tbs read only
• Others?

enq: TM - contention

TX locks have a corresponding TM lock

TM locks the structure from change

LOCK Parmeter1 Parmeter2(ID1) Parameter3(ID2)

------- --------- ------------- ---------------

enq: TM name|mode object # table/partition

(1) To many truncate requests
(2) Unindexed FKs
(3) Bitmap indexes on the table (low possibility; but possibility nonetheless) - Anup Nanda

(4) direct mode insert, or insert /*+ append */ which will take TM enqueue in exclusive mode. If the transaction doesn't commit, you could see a whole bunch of sessions stack up behind it. - Mark Bobak

enq: TX - contention

enq: TX - contention seems to be the trash bucket for all other TX contention issues and seems to be used when sessions are waiting on other sessions who are blocked some issues:

Example

• Data File Extension – waiter waiting for another session to extend file
• Index Block Split – waiter waiting for another session to split the block

Possibilities

Setting Table space read only

• Session 1 – start transaction, don’t commit
• Session 2 – alter tablespace read only
  Free Lists
• NON ASSM
• Sessions freeing block
• If no txs free lists available (min 16, grow up depending on block size) , pick one and wait TX 4
  2PC – two phase commit
• First does a prepare the commit
• Any read or write access in the intervening time waits TX 4

Troubleshooting & Further more reading on enqueues: http://db.geeksinsight.com/wp-content/uploads//2013/01/enqueues_locks_resolutionscauses.doc

Thanks

Geek DBA

References:-

Enqueue Waits - Locks - PerfVision.com

http://www.saptechies.com/faq-oracle-enqueues/

concurrency-issues-when-do.html