Below you find a description on selected enqueue types:
BL |
Buffer Cache Managment |
CF |
Control file schema global enqueue |
CI |
Cross Instance call invocation |
CU |
Cursor Bind |
DF |
Datafile |
DL |
Direct Loader index creation |
DR |
Distributed Recovery |
DX |
Distributed Transactions |
IR |
Instance Recovery |
HW |
Space Management operations on a specific
segment. This enqueue is used to serialize the allocation of space
above the high water mark of a segment:
V$SESSION_WAIT.P2 / V$LOCK.ID1 is the tablespace number
V$SESSION_WAIT.P2 / V$LOCK.ID2 is the relative dba segment
header of the object for which space is being allocated
If this is a point of contention for an object, then manual allocation
of extents solves the problem. |
LA..LP |
Library Cache Lock |
MD |
Materialized Views: enqueue for change data
capture materialized view log (gotten internally for DDL on a
snapshot log); id1=object# of the snapshot log. |
NA..NZ |
Library Cache Pin |
RT |
Redo Log |
SQ |
SeQuences not being cached, having a to small
cache size or being aged out of the shared pool. Consider pinning
sequences or increasing the shared_pool_size. |
ST |
Space management locks could be caused by using
permanent tablespaces for sorting (rather than temporary), or by
dynamic allocation resulting from inadequate storage clauses (only
with Dictionary Managed TableSpaces). In the latter case, using
locally-managed tablespaces may help avoiding this problem. If this
is not an option for some reason, you may at least change the next
extent sizes of the growing objects to be large enough to avoid
constant space allocation. To determine which segments are growing
constantly, monitor the EXTENTS column of the
DBA_SEGMENTS view for all SEGMENT_NAME s
over time to identify which segments are growing and how quickly.
Also, you may pre-allocate space in the regarding segment.
For the first case, the solution is quite obvious: check whether
the temporary tablespace uses TEMPFILES and whether
the temporary tablespace for the users is set correctly (at least
up to Oracle 8i, if you didn't specify it explicitly it was set
to SYSTEM!). |
TA |
Transaction Recovery |
TM |
Table locks point to the possibility of e.g.
foreign key constraints not being indexed |
TS |
Temporary Segment |
TX |
Transaction locks indicate multiple users try
modifying the same row of a table (row-level-lock) or a row that is
covered by the same bitmap index fragment, or a session is waiting
for an ITL (interested transaction list) slot in a block, but one or
more sessions have rows locked in the same block, and there is no
free ITL slot in the block. In the first case, the first user has to
COMMIT or ROLLBACK to solve the problem. In
the second case, increasing the number of ITLs available is the
answer - which can be done by changing either the
INITRANS or MAXTRANS
for the table in question. |
US |
Undo Segment, serialization |
For more information on enqueue waits, see Enqueues.
|