Enqueue Wait

Possible Causes:

·         This wait event indicates a wait for a lock that is held by another session (or sessions) in an incompatible mode to the requested mode.

•   TX Transaction Lock

o   Generally due to table or application set up issues.

o   This indicates contention for row-level lock.

o    This wait occurs when a transaction tries to update or delete rows that are currently locked by another transaction.

o   This usually is an application issue.

•    TM DML enqueue lock

o   Generally due to application issues, particularly if foreign key constraints have not been indexed.

 ST lock

o   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.

o   Contention for the ST lock indicates there are multiple sessions actively performing dynamic disk space allocation or deallocation in dictionary managed tablespaces.

Actions:

        Reduce waits and wait times.
        The action to take depends on the lock type which is causing the most problems
        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.
        Waits for TM enqueue in Mode 3 are primarily due to unindexed foreign key columns.
        Create indexes on foreign keys  < 10g 
        Following are some of the things you can do to minimize ST lock contention in your database:
        Use locally managed tablespaces.
        Recreate all temporary tablespaces using the CREATE TEMPORARY TABLESPACE TEMPFILE… command.

Remarks:



        Maximum number of enqueue resources that can be concurrently locked is controlled by the ENQUEUE_RESOURCES parameter.
        Reference Note# 34566.1
        Tracing sessions waiting on an enqueue Note# 102925.1
        Details of V$LOCK view and lock modes Note:29787.1