enqueue

"enqueue" Reference Note

This is a reference note for the wait event "enqueue" which includes the following subsections:

• (eg: For waits seen in )
• (eg: For waits seen in )

See for an introduction to Wait Events.

Definition:

• Versions:7.0 - 9.2 Documentation: 9.0
• Enqueues are local locks that serialize access to various resources. 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. See (about V$LOCK) for details of which lock modes are compatible with which.

  Enqueues are usually represented in the format "TYPE-ID1-ID2" where

  • "TYPE" is a 2 character text string
  • "ID1" is a 4 byte hexadecimal number
  • "ID2" is a 4 byte hexadecimal number

P2 =

P3 =

• Lock Type & Mode

  The lock type and requested mode are encoded into P1 thus:

  • Convert P1 into hexadecimal (or use P1RAW) and extract the lock type and requested mode from this by converting the first 2 bytes of the hexadecimal number to ASCII and the second 2 bytes to a number:

      Eg: 54580006  
          ^^^^------ Converted to ASCII gives "TX" (0x54 = "T", 0x58 = "X")
              ^^^^-- 0006 is 6 in decimal so this is a mode 6 request
    
      Note that on UNIX, the command 'man ascii' will display the Octal, 
      hexadecimal, and decimal ASCII character sets to help with the hex 
      translation.
    
    

  An alternative way to extract this information is to use SQL thus:

  •  SELECT 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'
      ;

• Lock ID1

  P2 represents ID1 of the enqueue name in decimal.
  P2RAW represents ID1 of the enqueue name in hexadecimal.

• Lock ID2

  P3 represents ID2 of the enqueue name in decimal.
  P3RAW represents ID2 of the enqueue name in hexadecimal.

  Wait Time:

The actual wait time depends on the lock type. In most cases Oracle waits for up to 3 seconds or until posted that the enqueue resource is now available (whichever occurs first). When the wait event times out Oracle will check that the session holding the lock is still alive and if so wait again. Deadlock detection is also performed against deadlock sensitive locks.

  Finding Blockers:

If a session is stuck waiting on an "enqueue" wait one can use to find the blocker/s and other waiters:

• V$LOCK.TYPE is the lock type from P1
• V$LOCK.ID1 is the lock ID1 from
• V$LOCK.ID2 is the lock ID2 from

It can often be simpler just to look at waiters in V$LOCK directly:

  SELECT * FROM v$lock WHERE request > 0;

or to see blockers as well:

  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
  ;

" above). To qualify how many waits have really occurred you need the enqueue waits statistic from , or from the "Statistics" section of the StatsPack or Estat report.

Eg: Assume
        Wait Events show enqueue time_waited=3000 total_waits=10
        Statistics  show enqueue waits has a count of 2
    This means there were 2 actual waits whose individual wait times totalled 
    to 3000 hundredths of a second (ie: 30 seconds).

To determine which enqueues are causing the most waits system-wide look at (Oracle9i onwards only) thus:

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

In Oracle8i or earlier look at instead thus:

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

The above give the system wide number of waits for each lock type. Remember that it only takes one long wait to distort the average wait time figures. One can also look at:

• Sessions with high numbers of "enqueue waits" in
• Sampling of to find waiting / blocking sessions

for example scenarios which can cause TX lock waits. TM DML enqueue Generally due to application issues, particularly if foreign key constraints have not been indexed. The following two articles describe referential integrity issues related to TM locking: Example TM locks During Referential Integrity Enforcement TM locks and Foreign Key Constraints ST Space management enqueue Usually caused by too much space management occurring (Eg: small extent sizes, lots of sorting etc..) See for more information about the ST enqueue.

补充一个RAC下查询锁的语句

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