Databases Reference
In-Depth Information
Table 27-2.
Enqueue Types (Continued)
Enqueue
Type
Name
Description
Reason for
Getting Enqueue
TM
DML
Synchronizes accesses to an object
contention
TS
Temporary
Segment
Serializes accesses to temp segments
contention
TX
Transaction
Lock held on an index during a split to
prevent other operations on it
index contention
TX
Transaction
Lock held by a transaction to allow other
transactions to wait for it
contention
TX
Transaction
Lock held on a particular row by a transac-
tion to prevent other transactions from
modifying it
row lock contention
TX
Transaction
Allocating an ITL entry in order to begin
a transaction
allocate ITL entry
Lock used by user applications
5
UL
User-defined
contention
US
Undo Segment
Lock held to perform DDL on the
undo segment
contention
The table's rows were generated by the following query:
5
SQL> SELECT eq_type, eq_name, req_description, req_reason
FROM v$enqueue_statistics
WHERE eq_type IN ('CF', 'CI', 'DX', 'HV', 'HW', 'JQ', 'JS', 'SQ',
'SS', 'ST', 'TM', 'TS', 'TX', 'UL', 'US')
ORDER BY eq_type;
If you encounter an enqueue that is neither documented nor listed in Table 27-2, then
querying
V$ENQUEUE_STATISTICS
will give you a first impression of what the enqueue is used for
and why the application you are optimizing may have requested the enqueue. Many enqueue
types are identical in Oracle9
i
and Oracle10
g
. So there is a good chance that you may find infor-
mation that also applies to Oracle9
i
.
Statement Level Resource Profiles
The last section of an ESQLTRCPROF report contains the SQL statements encountered, state-
ment level resource profiles, statement level statistics, and execution plans. Each statement is
depicted in a separate subsection, which begins with the statement's hash value and the state-
ment's total elapsed time. The hash value is normally unique and is useful for quickly locating
a statement in a trace file. Furthermore, the hash value may be used to retrieve current and
past execution plans of a particular statement from a Statspack repository (see Chapter 25).
5.
Waits for the enqueue UL occur when an application implements synchronization with the package
DBMS_LOCK
.
