Database Resources
- RDBMS Server
- Database Administration
- Performance Tuning
- Backup and Recovery
- Oracle Utilities
- SQL and PLSQL
- Network Administration
- Client Applications
- Miscellaneous Stuff

Oracle's technology Sites

http://technet.oracle.com

http://metalink.oracle.com

http://asktom.oracle.com/

http://www.oramag.com

Category     : Database Resources -> Database Administration -> Wait Events

DB Version  : Oracle 8i / Oracle 9i

OS Details    : Sun Solaris9

Reference : Oracle Metalink

Wait Event : Enqueue

This wait happens when a session waits for a lock that is held by another session (or sessions) in an incompatible mode to the requested mode. Enqueues are local locks that serialize access to various resources. An enqueue can be requested in different levels/mode: null, row share, row exclusive, share, share row  exclusive or exclusive. If a session holds an enqueue in share mode, other sessions can then also take the enqueue in share mode (for the same resource). If a session holds an enqueue in exclusive mode, other sessions that wants to get it will have to wait. Common types of enqueues include:

(1) JQ - Job Queue. When a job (submitted by DBMS_JOB.SUBMIT) is running, it is protected by a JQ enqueue (which means that only one SNP-process can run the job).

(2) ST - Space management Transaction. The ST enqueue is need to be held every time the session is allocating/deallocating extents

(3) TM - DML (Table) enqueue. Every time a session wants to lock a table, a TM enqueue is requested.

(4) TX - Transaction. As soon as a transaction is started a TX enqueue is needed.

(5) UL - User Lock. A session has taken a lock with the DBMS_LOCK.REQUEST function.

Some of the other enqueue types include:

Get details on the event:

Querying the V$SESSION_WAIT view will give us the following details for this event:

P1 => Lock Type & Mode
P2 => Lock ID1
P3 => Lock ID2

Lock Type & Mode => The lock type and requested mode are encoded into P1.

Following are a list of lock modes for DML enqueues:

Lock ID1 => Represents ID1 of the enqueue name in decimal.

Lock ID2 => Represents ID2 of the enqueue name in decimal.

SECONDS_IN_WAIT => Gives details on how long the enqueue waits occured.

To get details on locks/ enqueues:

-- Based on Information from V$SESSION_WAIT view:

SQL> SELECT sid, chr(to_char(bitand(p1,-16777216))/16777215) || chr(to_char(bitand(p1, 16711680))/65535) "Lock", to_char( bitand(p1, 65535) ) "Mode" FROM v$session_wait
WHERE event = 'enqueue'

-- Confirm that the session's indeed waiting and get the object information

SQL> select sid,lockwait,row_wait_obj#, row_wait_block#, row_wait_row# where lockwait is not null and sid = '&sid'

-- Get information on the lock holder and the waiter:

SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess, id1, id2, lmode, request, type
FROM V$LOCK WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request

To get instance statistics on locks/ enqueues:

-- To get information on what enqueues are causing most waits in the database (oracle9 and above):

SQL> SELECT eq_type "Lock", total_req# "Gets", total_wait# "Waits", cum_wait_time
FROM V$enqueue_stat WHERE Total_wait# > 0

-- To get information on what enqueues are causing most waits in the database (oracle8i and below):

SQL> SELECT ksqsttyp "Lock", ksqstget "Gets", ksqstwat "Waits" FROM X$KSQST where KSQSTWAT > 0;

Table above lists the various enqueue types and what they are acquired for by oracle.

Reducing the waits:

TX Transaction Lock:

Generally caused due to application issues. The TX lock is acquired when a transaction initiates its first change and is held until the transaction performs a COMMIT or ROLLBACK. Make sure the application is designed properly to handle transaction concurrency and also proper transaction control statements are in place.Every block header has space allocated for storing transaction table which contains an entry for each transaction that has locked data within the block. The size of this structure is controlled by INITRANS and MAXTRANS defined for the object. These two parameters control the concurrency for a block among various other factors. Locking problems can arise if proper values are not set for INITRANS and MAXTRANS.

TM DML enqueue:

These locks are used by oracle to ensure that the structure of a table is not altered while the data within the table's being modified. The total number of TM locks allowed within the database is controlled by the database parameter DML_LOCKS. Waits for this enqueue are generally due to application issues, particularly if foreign key constraints have not been indexed.

ST Space management enqueue:

Usually caused by too much space management occurring (Eg: small extent sizes, lots of sorting etc.). When Oracle needs to perform a space management operation (such as allocating temporary segments for a sort) the user session aquires the 'ST' enqueue. You can try and reduce this enqueue by using locally managed tablespaces with automatic segment space management (ASSM) for tables and indexes and by using tempfiles for temporary tablespaces.