Enqueue
Until Oracle9 i Database, the enqueue wait event parameters are name and mode, ID1, and ID2. In Oracle Database 10 g , the first parameter remains the same, but the second and third parameters provide specific information about the enqueue they represent. Depending on the enqueue wait event, it can fall under the Administrative, Application, Configuration, Concurrency, or Other wait class. Keep the following key thoughts in mind when dealing with the enqueue wait event.
-
Enqueues are locks that apply to database objects.
-
Enqueues are transactional, initiated by the application.
-
The Oracle session is waiting to acquire a specific enqueue. The enqueue name and mode is recorded in the P1 parameter. The appropriate action to take depends on the type of enqueue being competed for.
-
Up to Oracle9 i Database, the enqueue wait event represents all enqueue waits; starting in Oracle Database 10 g , all enqueues are broken out and have independent wait events.
What Is an Enqueue?
What an enqueue is depends on the context in which it is used. When used as a verb, it refers to the act of placing a lock request in a queue. When used as a noun, it refers to a specific lock, such as the TX enqueue.
Enqueues are sophisticated locking mechanisms for managing access to shared resources such as schema objects, background jobs, and redo threads. Oracle uses enqueues for two purposes. First, they prevent multiple concurrent sessions from sharing the same resource when their lock modes are incompatible. Second, they allow sessions to share the same resource when their lock modes are compatible. When a session requests a lock on the whole or part of an object, and if the requested lock mode is incompatible with the mode held by another session, the requesting session puts its lock request in a queue (hence enqueue) and waits to be served in order. This event is known as an enqueue wait. Enqueue waits are waits for a variety of local locks, except for buffer locks (discussed in the ‚“Buffer Busy Waits ‚½ section), library cache locks, row cache locks, and PCM (Parallel Cache Management) locks.
What Is an Enqueue Resource?
An enqueue resource is a database resource that is affected by an enqueue lock. Oracle manages the enqueue resources using an internal array structure that can
be seen through the X$KSQRS (kernel service enqueue resource) or V$RESOURCE view as follows :
select * from v$resource;
ADDR TY ID1 ID2
---------------- -- ---------- ----------
C000000047BE4D40 TX 4980825 115058
C00000003FEB4A58 TM 6112 0
C00000004262E758 WL 1 25603
C00000004AD99538 TX 327726 202076
C00000004A5C6CE8 TM 5507 0
C00000003FB7C4F8 TX 5898241 23053
C00000004BB3A968 DX 28 0
. . .
According to the preceding output, the enqueue resource structure consists of a lock type and two numeric identifiers. The lock type is represented by a two-character code such as TX, TM, TS, MR, RT, etc. The two numeric identifiers ID1 and ID2 have different meanings depending on the lock type. Table 6-3 gives a few examples. Prior to Oracle Database 10 g , the meaning of ID1 and ID2 for each lock type was not readily available to the general public. Because all enqueues have independent wait events in Oracle Database 10 g , you can easily find out what ID1 and ID2 means from the PARAMETER2 and PARAMETER3 parameters of the particular enqueue wait event in the V$EVENT_NAME view.
| Lock type | TX |
|---|---|
| ID1 | ID1 indicates the rollback segment and the slot number. On 32-bit machines, you must convert ID1 into hexadecimal and left pad with zeros up to 8 characters . The high order 2 bytes give the rollback segment number, and the low order 2 bytes give the rollback slot number. The values can be seen in the XIDUSN and XIDSLOT columns of the V$TRANSACTION view. |
| ID2 | Rollback segment wrap or sequence number. This value can be seen in the XIDSQN column of V$TRANSACTION view. |
| Lock type | TM |
| ID1 | ID1 indicates the object ID of the table, which can be found in DBA_OBJECTS.OBJECT_ID. |
| ID2 | Always 0. |
| Lock type | TS |
| ID1 | This is the tablespace number, which can be found in the TS$.TS#. |
| ID2 | This is the relative Database Block Address (DBA). |
| Lock type | JQ |
| ID1 | Always 0. |
| ID2 | ID2 indicates the job number. |
| Lock type | MR |
| ID1 | Data file ID. Oracle takes up to one MR enqueue per data file (including temp files). |
| ID2 | Always 0. |
| Lock type | RT |
| ID1 | Redo thread number. |
| ID2 | Always 0. |
The maximum number of enqueue resources that can be concurrently locked by the lock manager is controlled by the ENQUEUE_RESOURCES parameter. The default value is normally sufficient, but you may need to increase it if the application uses parallel DML operations. Parallel DML operations use more locks than serial DML operations. Processes that fail to obtain an enqueue resource will get the ORA-00052: "maximum number of enqueue resources exceeded" error. The V$RESOURCE_LIMIT view provides important utilization statistics, such as current utilization and how far the high watermark (MAX_UTILIZATION) is from the limit (LIMIT_VALUE). Up to Oracle8 i Database, the ENQUEUE_RESOURCES parameter also sets the size of the X$KSQRS array. But starting in Oracle9 i Database, Oracle seems to use a different algorithm for establishing the X$KSQRS array size.
select *
from v$resource_limit
where resource_name in (enqueue_resources,enqueue_locks,
dml_locks,processes,sessions);
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------ ------------------- --------------- ---------- ----------
processes 438 643 2000 2000
sessions 443 664 2205 2205
enqueue_locks 402 465 27101 27101
enqueue_resources 449 584 800 UNLIMITED
dml_locks 68 778 600 UNLIMITED
What Is an Enqueue Lock?
An enqueue lock is the lock itself. Oracle uses a separate array than the enqueue resources array to manage the enqueue locks. This structure can be seen through the X$KSQEQ (kernel service enqueue object) view or the V$ENQUEUE_LOCK view as follows. The size of this structure is influenced by the _ENQUEUE_LOCKS parameter. You can check how close the maximum utilization is to the limit value from the V$RESOURCE_LIMIT view as shown in the preceding code.
select *
from x$ksqeq
where bitand(kssobflg,1)!=0
and (ksqlkmod!=0 or ksqlkreq!=0);
-- or simply query from V$ENQUEUE_LOCK
select * from v$enqueue_lock;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- --- -- ------- ---- ----- ------- ------- -----
243F8368 243F8378 2 MR 51 0 4 0 349520 0
243F831C 243F832C 2 MR 6 0 4 0 349520 0
243F82D0 243F82E0 2 MR 5 0 4 0 349520 0
243F8284 243F8294 2 MR 4 0 4 0 349520 0
243F8238 243F8248 2 MR 3 0 4 0 349520 0
243F81EC 243F81FC 2 MR 2 0 4 0 349520 0
243F81A0 243F81B0 2 MR 1 0 4 0 349520 0
243F80BC 243F80CC 3 RT 1 0 6 0 349521 0
243F7F8C 243F7F9C 4 XR 4 0 1 0 349524 0
243F8108 243F8118 5 TS 2 1 3 0 349519 0
243F7FD8 243F7FE8 9 TX 393241 875 0 6 2651 0
The enqueue lock structure contains information such as the session ID (SID), lock type, database resource (identified by ID1 and ID2), lock mode, request mode, current mode time (CTIME), and blocking flag. This structure is used for managing and servicing lock requests in the order of request, not lock mode. This means if one of the waiters needs a particular lock and the mode is compatible with the current mode, but the waiter is behind another waiter whose lock mode is incompatible with the current mode, the waiter ‚ s request cannot be served.
Don ‚ t be surprised if you don ‚ t see the normal transaction (TX) and DML (TM) locks in V$ENQUEUE_LOCKS. They are not in the X$KSQEQ structure, unless there is an enqueue wait for them. Oracle uses different structures to manage the TX and TM enqueues: the X$KTCXB (kernel transaction control transaction object ‚ the base view for V$TRANSACTION_ENQUEUE) and X$KTADM (kernel transaction access definition dml lock). You can find the entries using the following queries. The structures are sized by the TRANSACTIONS and DML_LOCKS parameters. The V$LOCK is the best view because it shows all the locks.
select *
from x$ktcxb
where KTCXBLKP in (select kaddr from v$lock where type = TX);
select *
from x$ktadm
where KSQLKADR in (select kaddr from v$lock where type = TM);
Enqueue Architecture
Internally, the enqueue architecture is very similar to the cache buffers architecture. The main components are enqueue hash chains latches, enqueue hash table, enqueue hash chains, and enqueue resources. The relationship between the enqueue hash chains latches, enqueue hash table, and enqueue hash chains is as follows:
enqueue hash chains latch ƒ (1:m) ƒ hash bucket ƒ (1:1) ƒ enqueue hash chain
Child enqueue hash chains latches protect the enqueue hash table and hash chains. By default, the number of child enqueue hash chains latches is equal to the CPU_COUNT. This number can be adjusted by the initialization parameter _ENQUEUE_HASH_CHAIN_LATCHES.
Enqueue resources are hashed to the enqueue hash table based on the resource type and identifiers and are placed on the appropriate enqueue hash chains. The default length of the enqueue hash table is derived from the SESSIONS parameter and can be adjusted by the _ENQUEUE_HASH parameter. If you ever need to increase the ENQUEUE_RESOURCES parameter significantly from its default value, you might want to keep an eye on the sleep rate of the enqueue hash chains latches. This is because the enqueue hash table length will remain the same because it is derived from SESSIONS, not from ENQUEUE_RESOURCES. The combination of a high demand for enqueue resources and a small enqueue hash table will result in a higher hash collision rate and potentially lengthy hash chains. This problem manifests itself as latch contentions for the enqueue hash chains latches. In this case, you need to increase the _ENQUEUE_HASH.
enqueue hash table length = ((SESSIONS ‚ 10) * 2) + 55
To learn more about the enqueue hash table, the resources and queues, you can dump the enqueue structure to a trace file with the following command:
alter session set events immediate trace name enqueues level 3;
Decoding Enqueue Type and Mode
Oracle encodes the enqueue type and mode in the P1 column of the enqueue wait event. When deciphered, you will get a two-character enqueue (lock) type and a numeric mode number. The enqueue type can be deciphered by the following query:
select sid, event, p1, p1raw,
chr(bitand(P1,-16777216)/16777215)chr(bitand(P1,16711680)/65535) type,
mod(P1, 16) "MODE"
from v$session_wait
where event = enqueue;
SID EVENT P1 P1RAW TY MODE
---- --------------- ---------- ---------------- -- ----------
405 enqueue 1213661190 0000000048570006 HW 6
132 enqueue 1397817350 0000000053510006 SQ 6
43 enqueue 1413677062 0000000054430006 TC 6
44 enqueue 1415053316 0000000054580004 TX 4
40 enqueue 1415053318 0000000054580006 TX 6
. . .
Alternatively, the enqueue type can also be discovered from the P1RAW column of the enqueue wait event. The values from the above example are from a 64-bit Oracle Database. You can ignore the leading zeros and focus on the last 4 bytes (that is, the last eight numbers ). The high order 2 bytes give the enqueue type. Using 54580006 as an example, the 2 high order bytes are 0x5458Hex. Now, 54Hex is decimal 84 and 58Hex is decimal 88, so the enqueue type can be discovered as follows: (Appendix B has a complete list of enqueue types.)
select chr(84) chr(88) from dual;
CH
--
TX
Decoding enqueue type is a no-brainer in Oracle Database 10 g because the enqueue type is part of the enqueue wait event name itself, since all enqueues are broken out. The following are a few examples:
enq: ST contention
enq: TX - allocate ITL entry
enq: TX contention
enq: TX - index contention
enq: TX - row lock contention
Processes request enqueue locks in one of these six modes: Null (N), Row Share (RS), Row Exclusive (RX), Share (S), Share Row Exclusive (SRX), or Exclusive (X). These modes are represented by a single numeric digit from 0 to 6, as shown in Table 6-4. The lock mode is encoded in the low order 2 bytes of P1RAW, and it is easily spotted. Using the same 54580006 as an example, the 2 low order bytes are 0006Hex, so the lock mode is 6 (exclusive). Alternatively, the lock mode can be deciphered from the P1 column using mod(P1, 16) or to_char(bitand(P1, 65535) .
| Mode | Description |
|---|---|
|
| None |
| 1 | Null (N) |
| 2 | Row-Share (RS), also known as Subshare lock (SS) |
| 3 | Row-Exclusive (RX), also known as Subexclusive lock (SX) |
| 4 | Share (S) |
| 5 | Share Row Exclusive (SRX), also known as Share-Subexclusive lock (SSX) |
| 6 | Exclusive (X) |
Oracle uses the lock mode to determine if a resource can be shared by multiple concurrent processes. Table 6-5 is the compatibility chart.
| Statement | Mode | N | RS | RX | S | SRX | X |
|---|---|---|---|---|---|---|---|
| SELECT | N | Yes | Yes | Yes | Yes | Yes | Yes |
| SELECT ‚ FOR UPDATE | RS | Yes | Yes* | Yes* | Yes* | Yes* | No |
| lock table in row share mode | RS | Yes | Yes | Yes | Yes | Yes | No |
| INSERT | RX | Yes | Yes | Yes | No | No | No |
| UPDATE | RX | Yes | Yes* | Yes* | No | No | No |
| DELETE | RX | Yes | Yes* | Yes* | No | No | No |
| lock table in row exclusive mode | RX | Yes | Yes | Yes | No | No | No |
| lock table in share mode | S | Yes | Yes | No | Yes | No | No |
| lock table in share row exclusive mode | SRX | Yes | Yes | No | No | No | No |
| lock table in exclusive mode | X | Yes | No | No | No | No | No |
| *Yes means sharing is possible if no conflicting row lock is held by another session. | |||||||
Common Causes, Diagnosis, and Actions
Due to the variety of enqueue types, an enqueue wait event can occur for many different reasons. The common causes and the appropriate action to take depend on the enqueue type and mode that the sessions are competing for. For each type of enqueue, Oracle keeps an instance-level statistics on the number of requests and waits in the X$KSQST structure. Oracle9 i Database exposes this structure through the V$ENQUEUE_STAT view. A new column is also added to the X$KSQST structure to keep the cumulative wait time as shown next .
-- Oracle9i Database and above
select *
from v$enqueue_stat
where cum_wait_time > 0
order by inst_id, cum_wait_time;
INST_ID EQ TOTAL_REQ# TOTAL_WAIT# SUCC_REQ# FAILED_REQ# CUM_WAIT_TIME
---------- -- ---------- ----------- ---------- ----------- -------------
1 SQ 66551 437 66551 0 498
1 CU 64353 133 64353 0 1616
1 HW 453067 18683 453067 0 11811
1 CF 119748 76 119605 143 37842
1 TX 22687836 9480 22687758 71 672435
1 TC 3620 724 3620 0 679237
1 TM 89822967 91 89817200 5 4056333
-- Oracle 7.1.6 to 8.1.7
select inst_id,
ksqsttyp "Lock",
ksqstget "Gets",
ksqstwat "Waits"
from x$ksqst
where ksqstwat > 0
order by inst_id, ksqstwat;
You should realize how interactive users experience enqueue waits. They receive no messages and their sessions freeze up. That is what they will tell you when they call. Don ‚ t expect them to tell you that they are waiting on enqueues. You should be thankful they are not that sophisticated.
A detailed discussion on all the enqueues is impractical . However, the most common enqueue waits are discussed next.
Wait for TX Enqueue in Mode 6
A wait for the TX enqueue in mode 6 (P1= 1415053318, P1RAW= 54580006) is the most common enqueue wait. (In Oracle Database 10 g , the wait event name is enq: TX ‚ row lock contention .) This indicates contention for row-level lock. This wait occurs when a transaction tries to update or delete rows that are currently locked by another transaction. This usually is an application issue. The waiting session will wait until the blocking session commits or rolls back its transaction. There is no other way to release the lock. (Killing the blocking session will cause its transaction to be rolled back.)
The following listing shows an example of TX enqueue wait in mode 6 as seen in the V$LOCK view:
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- --- -- ------ ------ ----- ------- ----- -----
A3950688 A395069C 10 TM 188154 0 3 0 3 0
A304E2A0 A304E2B0 10 TX 65585 147836 0 6 3 0
01AD23D4 01AD24A4 20 TX 65585 147836 6 0 10 1
A3950A28 A3950A3C 20 TM 188154 0 3 0 10 0
Whenever you see an enqueue wait event for the TX enqueue, the first step is to find out who the blocker is and if there are multiple waiters for the same resource by using the following query. If the blocking session is an ad-hoc process, then the user may be taking a break. In this case, ask the user to commit or roll back the transaction. If the blocking session is a batch or OLTP application process, then check to see if the session is still ‚“ alive . ‚½ It may be a live Oracle session, but its parent process may be dead or hung. In this case, chances are you will have to kill the session to release the locks. Be sure to confirm with the application before killing a production process.
select /*+ ordered */
a.sid blocker_sid,
a.username blocker_username,
a.serial#,
a.logon_time,
b.type,
b.lmode mode_held,
b.ctime time_held,
c.sid waiter_sid,
c.request request_mode,
c.ctime time_waited
from v$lock b, v$enqueue_lock c, v$session a
where a.sid = b.sid
and b.id1 = c.id1(+)
and b.id2 = c.id2(+)
and c.type(+) = TX
and b.type = TX
and b.block = 1
order by time_held, time_waited;
You can discover the resource that is being competed for. The resource ID is available in the V$LOCK.ID1 column of the DML lock (TM) for that transaction. It is also available in the V$SESSION.ROW_WAIT_OBJ# of the waiting session. The following query retrieves the resource of the TX enqueue wait:
select c.sid waiter_sid, a.object_name, a.object_type
from dba_objects a, v$session b, v$session_wait c
where (a.object_id = b.row_wait_obj# or
a.data_object_id = b.row_wait_obj#)
and b.sid = c.sid
and chr(bitand(c.P1,-16777216)/16777215)
chr(bitand(c.P1,16711680)/65535) = TX
and c.event = enqueue;
Don ‚ t forget to extract the SQL statement that is executed by the waiting session as well as the SQL statement that is executed by the blocking session. These statements will give you an idea of what the waiting session is trying to do and what the blocking session is doing. They are also important points of reference for the application developers so that they can quickly locate the modules. (By the way, the SQL statement that is currently being executed by the blocking session is not necessarily the statement that holds the lock. The statement that holds the lock might have been run a long time ago.)
Wait for TX Enqueue in Mode 4 ‚ ITL Shortage
A wait for the TX enqueue in mode 4 is normally due to one of the following reasons:
-
ITL (interested transaction list) shortage
-
Unique key enforcement
-
Bitmap index entry
Here, we will talk about the ITL, which is a transaction slot in a data block. The initial number of ITL slots is defined by the INITRANS clause and is limited by the MAXTRANS clause. By default, a table has 1 ITL and an index has 2 ITLs. Each ITL takes up 24 bytes and contains the transaction ID in the format of USN.SLOT#.WRAP#. Every DML transaction needs to acquire its own ITL space within a block before data can be manipulated. Contention for ITL occurs when all the available ITLs within a block are currently in use and there is not enough space in the PCTFREE area for Oracle to dynamically allocate a new ITL slot. In this case, the session will wait until one of the transactions is committed or rolled back, and it will reuse that ITL slot. ITL is like a building parking space. Everyone who drives to the building needs a parking space. If the parking lot is full, you have to circle the lot until someone leaves the building.
| Note ‚ | Starting in Oracle9 i Database, each data block has a minimum of two ITL slots by default. Even if you specify one, you still get two. The DBA_TABLES view will show just one, but the block dump will show two. |
The following listing shows an example of the TX enqueue wait in mode 4 that is caused by ITL shortage, as seen in the V$LOCK view.
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- --- -- ------ ------ ----- ------- ----- -----
8A2B6400 8A2B6414 8 TM 3172 0 3 0 248 0
89EF3A0C 89EF3A1C 8 TX 131147 13 0 4 248 0
01A4177C 01A41848 9 TX 131147 13 6 0 376 1
8A2B6388 8A2B639C 9 TM 3172 0 3 0 376 0
Because a TX enqueue wait in mode 4 is not always caused by ITL shortage, your first step should be to validate this premise . (You do not have to do this exercise in Oracle Database 10 g because the enqueue name is enq: TX ‚ allocate ITL entry , and you know it right off the bat.) The V$SESSION row of the waiting session contains the information about the object of the enqueue. The columns in particular are ROW_WAIT_FILE# and ROW_WAIT_BLOCK#. Using these values, you can dump the block and see the number of active ITLs (‚“--U- ‚½) in the block and decide if ITL is the problem. If it is, the fix is to recreate the object with a higher INITRANS value. (Recreating the object with a higher PCTFREE value will also help because Oracle can dynamically allocate new ITL slots in the area.)
alter system dump datafile <file#> block <block#>;
-- The ITL portion of a block dump
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.051.0001fcf2 0x07ca2145.4e11.18 --U- 0 scn 0x070e.03df2f08
0x02 0x0005.049.00022d46 0x090618b7.5967.1c C--- 0 scn 0x070e.03df2f6a
0x03 0x0012.008.0001244b 0x0580a510.26ac.0c --U- 0 scn 0x070e.03df2f7b
0x04 0x0014.00d.00012593 0x090d4f93.28d3.1e C--- 0 scn 0x070e.03e08919
. . .
Starting in Oracle9 i Database, Oracle keeps track of the number of ITL waits by object and publishes the information in the V$SEGMENT_STATISTICS view. Execute the following query to see the magnitude of ITL waits in your database:
select owner,
object_name,
subobject_name,
object_type,
tablespace_name,
value,
statistic_name
from v$segment_statistics
where statistic_name = ITL waits
and value > 0
order by value;
Wait for TX Enqueue in Mode 4 ‚ Unique Key Enforcement
Unique or primary key enforcement is yet another reason you might see contention for the TX enqueue in mode 4. (In Oracle Database 10 g , the wait event name is enq: TX ‚ row lock contention .) This only occurs when multiple concurrent sessions insert the same key value into a table that has one or more unique key constraints. The first session to insert the value succeeds, but the rest freeze and wait until the first session commits or rolls back to see if ‚“ ORA-00001 unique constraint (%s.%s) violated ‚½ should be raised.
The following listing shows an example of a TX enqueue wait in mode 4 as seen in the V$LOCK view that is due to unique key enforcement. What is the difference between this listing and the one caused by ITL shortage? Notice that the waiter (SID=8) has two TX entries in the V$LOCK view. This doesn ‚ t mean that it owns two transactions. In fact, the V$TRANSACTION view shows only two transactions ‚ one for SID 8 and another for SID 9. This shows SID 8 is waiting for the TX lock held by SID 9, and it wants a share lock (mode 4) on the object. SID 8 also holds a TX lock for its own transaction. Another thing you should be aware of is the object ID that is recorded in ID1 of the DML transaction (TM) is always the table ID, not the index ID, although a unique key is enforced through an index.
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- --- -- ------ ----- ----- ------- ----- -----
01AF6120 01AF61EC 8 TX 131099 14 6 0 4051 0
8A2B6388 8A2B639C 8 TM 3176 0 3 0 4051 0
89EF37CC 89EF37DC 8 TX 131094 14 0 4 4051 0
01AF6120 01AF61EC 9 TX 131094 14 6 0 4461 1
8A2B6400 8A2B6414 9 TM 3176 0 3 0 4461 0
Your action items are the same as before ‚ find out who is blocking, the name of the resource being competed for, and the SQL statements executed by the waiting and the blocking session. This is an application issue, and the fix must come from the application.
Wait for TX Enqueue in Mode 4 ‚ Bitmap Index Entry
A wait for the TX enqueue in mode 4 can also occur when multiple sessions try to update or delete different rows that are covered by the same bitmap entry. Of course, this does not apply if the application does not use bitmap indexes.
Unlike the B-tree index entry, which contains a single ROWID, a bitmap entry can potentially cover a range of ROWIDs. So when a bitmap index entry is locked, all the ROWIDs that are covered by the entry are also locked.
The following listing shows an example of a TX enqueue wait in mode 4 as seen in the V$LOCK view due to bitmap entry. What is the difference between this listing and the preceding one in the unique key enforcement? Can you tell if you are dealing with a bitmap index entry or unique key enforcement issue by looking at the V$LOCK view output? No, you can ‚ t. The object ID in the TM lock doesn ‚ t help either, as it is the object ID of the table and not the index. That is why it is very important for you to capture the SQL statement of the waiting and blocking sessions. If the waiting session is attempting an insert, you are dealing with a unique key enforcement issue. If the waiting session is attempting an update or delete, most likely you are dealing with a bitmap entry issue.
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- --- -- ------ ----- ----- ------- ----- -----
01A52DB4 01A52E80 7 TX 131120 14 6 0 31 1
8A2B6310 8A2B6324 7 TM 3181 0 3 0 31 0
01A52DB4 01A52E80 9 TX 131107 14 6 0 9 0
8A2B6388 8A2B639C 9 TM 3181 0 3 0 9 0
89EF3A4C 89EF3A5C 9 TX 131120 14 0 4 9 0
In order to resolve the contention, you have to hunt down the offending user. However, the offending user is not always the user who holds the lock. That user was there first, for crying out loud. If the user has a legitimate reason to hold the lock, the waiters should back out of their transactions.
Wait for ST Enqueue
There is only one ST lock per database. Database actions that modify the UET$ (used extent) and FET$ (free extent) tables require the ST lock, which includes actions such as drop, truncate, and coalesce. Contention for the ST lock indicates there are multiple sessions actively performing dynamic disk space allocation or deallocation in dictionary managed tablespaces. Temporary tablespaces that are not created with the TEMPORARY clause and dictionary managed tablespaces that undergo high extent allocation or deallocation are the principal reasons for ST lock contention. Following are some of the things you can do to minimize ST lock contention in your database. The first two items are critical, and you should implement them in your database. They will significantly reduce the ST lock contentions.
-
Use locally managed tablespaces. In Oracle9 i Database Release 2, all tablespaces including SYSTEM can be locally managed. There is no reason not to use locally managed tablespaces. (If the SYSTEM tablespace is locally managed, you cannot have any dictionary managed tablespaces that are read/write.)
-
Recreate all temporary tablespaces using the CREATE TEMPORARY TABLESPACE TEMPFILE ‚ command.
-
For dictionary managed tablespaces, increase the next extent sizes of segments that experienced high dynamic allocations . Also, preallocate extents for the segments that are frequently extended.
Wait for TM Enqueue in Mode 3
Unindexed foreign key columns are the primary cause of TM lock contention in mode 3. However, this only applies to databases prior to Oracle9 i Database. Depending on the operation, when foreign key columns are not indexed, Oracle either takes up a DML share lock (S ‚ mode 4) or share row exclusive lock (SRX ‚ mode 5) on the child table whenever the parent key or row is modified. (The share row exclusive lock is taken on the child table when the parent row is deleted and the foreign key constraint is created with the ON DELETE CASCADE option. Without this option, Oracle takes the share lock.) The share lock or share row exclusive lock on the child table prohibits other processes from getting a row exclusive lock (RX ‚ mode 3) on the table. The waiting session will wait until the blocking session commits or rolls back its transaction.
Here is a philosophical question for you: Are you going to start building new indexes for all the foreign key columns in your databases? DBAs are divided on this. Our take is that you should hold your horses and don ‚ t get carried away building new indexes just yet. If you do, you will introduce many new indexes to the database, some that are unnecessary. For example, you don ‚ t need to create new indexes on foreign key columns when the parent tables they reference are static. You only need to create indexes on foreign key columns of the child table that is being identified by the enqueue wait event. The object ID for the child table is recorded in the P2 column, which corresponds to the ID1 column of the V$LOCK view. Query the DBA_OBJECTS view using the object ID and you will see the name of the child table. Yes, you will be operating in reactive mode, but it beats creating unnecessary indexes in the database, which not only wastes storage and increases maintenance, but may open up another can of worms for SQL tuning.
Following is an Oracle8 i Database V$LOCK view output of a TM enqueue wait in mode 3 that is caused by an unindexed foreign key column. Notice the blocking session holds two TM locks: one for the parent table (ID1=3185) and the other for the child table (ID=3187). The share row exclusive lock (mode 5) on the child table prevents the row exclusive lock (mode 3) request from the waiting session (SID=9).
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- --- -- ------ ----- ----- ------- ----- -----
01A52DB4 01A52E80 7 TX 131155 14 6 0 603 0
8A2B6388 8A2B639C 7 TM 3187 0 5 0 603 1
8A2B6310 8A2B6324 7 TM 3185 0 3 0 603 0
8A2B6400 8A2B6414 9 TM 3187 0 0 3 758 0
The same steps that are used to produce the preceding TM enqueue contention are repeated in an Oracle9 i Database; following is the output. In this case, the ID of the child table is 128955 and the ID of the parent table is 128953. Notice that there is no blocking lock. Unindexed foreign keys are no longer an issue starting in Oracle9 i Database.
KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- --- -- ------- ------ ----- ------- ----- -----
C00000003B1F7230 295 TM 128953 0 2 0 21 0
C00000003B1F73B0 295 TM 128955 0 3 0 21 0
C000000036F5F2F0 295 TX 4391002 145885 6 0 21 0
C00000003B1F6CF0 465 TM 128953 0 3 0 27 0
C00000003B29B118 465 TX 1310720 143517 6 0 27 0
C00000003B1F6DB0 465 TM 128955 0 3 0 27 0
Finally, TM enqueue contention in mode 3 can also occur when a table is explicitly locked in the share mode or higher and there are concurrent DML activities against the table. This is common with applications that use old third-party vendor codes. You can query the V$SQLAREA view for the LOCK TABLE statement. Following is an example of what this contention will look like in the V$LOCK view:
KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---- -- ------ ---- ----- ------- ------- -----
C00000003B1F6B70 295 TM 128956 0 0 3 12 0
C00000003B1F75F0 465 TM 128956 0 4 0 18 1
EAN: 2147483647
Pages: 114