System wide, event based tuning using statspack or utlbstat/estat reports can give a useful overview of any potential bottlenecks in the database. The wait events are good place to start looking. This tip offers advice on what to do if the enqueue wait event is consuming high amounts of resource.

To confirm whether or not enqueue waits are significant, consider whether the TIME_WAITED against the enqueue event in the V$SYSTEM_EVENT dynamic performance view is significant compared to how long the instance has been running. (TIME_WAITED is shown in hundreths of a second).

Next, get a breakdown of which particular enqueue class has been the cause of most of the waits:

select * from x$ksqst
where ksqstwat > 0;

ADDR INDX INST_ID KS KSQSTGET KSQSTWAT
---------------- ---------- ---------- -- ---------- ----------
0000000000C33CD8 69 1 CF 97 2
0000000000C33CF0 72 1 CI 9716 1473
0000000000C33D50 84 1 CU 8687 44
0000000000C34D30 592 1 SQ 11388 3527
0000000000C34D48 595 1 ST 1311 93
0000000000C34E10 620 1 TM 120047 1
0000000000C34E40 626 1 TS 1080 5
0000000000C34E68 631 1 TX 49558 1962

X$ tables representing memory structures change from release to release but this particular view has been around since at least Oracle 7.3 and is still relevant as of 9.2 (although a few more columns have been added). The KSQSTWAT column represents the number of waits for the enqueue statistics class. The example was taken from an 8.1 OLTP database.

Starting with Oracle9i, it is possible to see not only the gets and the waits, but also the wait time and the number of times one failed to get an enqueue (due to interrupt or timeout). A new view to 9i V$ENQUEUE_STAT makes information about enqueue waits more visible. X$KSQST.KSQSTWTIM is exposed as column CUM_WAIT_TIME.

Bizarrely, this metric seems to be reported in milliseconds (1/1000's of a second); most of Oracle9i's other timing metrics are still reported in centiseconds as per earlier releases,
even though they are now collected in microseconds.

SQL> select * from v$enqueue_stat where total_wait# > 0;

INST_ID EQ TOTAL_REQ# TOTAL_WAIT# SUCC_REQ# FAILED_REQ# CUM_WAIT_TIME
--------- -- ---------- ----------- ---------- ----------- -------------
1 CF 292223 2 292223 0 0
1 PS 2160 165 1734 425 51
1 TX 2897177 60 2897135 26 97927030

A full list of all Oracle enqueue names can be found in Appendix B
of the Oracle Database Reference Manual (or see ). Here are examples of enqueue waits that I have come across and how they could be resolved:

ST - SPACE TRANSACTION
. disk sorts in a permanent contents DMT with small extent size
. lots of dynamic extending & shrinking of rollback segments
. poor default storage clauses on segments in DMTs
(waits for the ST enqueue have become less common since the introduction of locally
managed tablespaces)

TM -TABLE LOCK
application specific reasons but check for indexes supporting FK constraints

TX - TRANSACTION LOCK
application specific reasons

CI - CROSS INSTANCE CALL
. V7 - Extent-based checkpoints. DBWR has to find and flush all blocks from extents
used in a parallel query prior to doing direct reads
. V8 - Taken out to checkpoint an object or block range (see 'object flush calls');
used to flush dirty buffers to disk prior to a direct read operation (PQ)
. exacerbated by an overly large buffer cache and parallel query combination

SQ - SEQUENCE NUMBER
. Sequences not being cached or cache size not big enough
. Sequence caches being aged out of the shared pool. Why not pin sequences or increase shared_pool_size?

Classes of enqueue event

BL, Buffer Cache Management
CF, Controlfile Transaction
CI, Cross-instance Call Invocation
CU, Bind Enqueue
DF, Datafile
DL, Direct Loader Index Creation
DM, Database Mount
DR, Distributed Recovery
DX, Distributed TX
FS, File Set
IN, Instance Number
IR, Instance Recovery
IS, Instance State
IV, Library Cache Invalidation
JQ, Job Queue
KK, Redo Log "Kick"
L[A-P], Library Cache Lock
MR, Media Recovery
N[A-Z], Library Cache Pin
PF, Password File
PI, Parallel Slaves
PR, Process Startup
PS, Parallel Slave Synchronization
Q[A-Z], Row Cache
RT, Redo Thread
SC, System Commit Number
SM, SMON
SQ, Sequence Number Enqueue
SR, Synchronized Replication
SS, Sort Segment
ST, Space Management Transaction
SV, Sequence Number Value
TA, Transaction Recovery
TM, DML Enqueue
TS, Temporary Segment (also TableSpace)
TT, Temporary Table
TX, Transaction
UL, User-defined Locks
UN, User Name
US, Undo Segment, Serialization
WL, Being Written Redo Log
XA, Instance Attribute Lock
XI, Instance Registration Lock