Thursday, February 19, 2009

enqueue

Summary
Enqueues are sophisticated locking mechanisms for managing access to shared resources such as schema objects, background jobs, and redo threads.
Oracle uses enqueues for two purposes.
First, they prevent multiple concurrent sessions from sharing the same resource when their lock modes are incompatible.
Second, they allow sessions to share the same resource when their lock modes are compatible.
When a session requests a lock on the whole or part of an object, and if the requested lock mode is incompatible with the mode held by another session, the requesting session puts its lock request in a queue (hence enqueue) and waits to be served in order. This event is known as an enqueue wait.
Enqueue waits are waits for a variety of local locks, except for buffer locks, library cache locks, row cache locks, and PCM (Parallel Cache Management) locks.
If you see a wait event of type:enqueue then this is related with transaction locking, with other words there is a blocked session.

To find who is blocking it execute the query posted in "Blocking Sessions".
The tip is obvious! Killing the blocking session disappears the enqueue.

Another way is to use the script posted in "Identify waiting sessions".
From that, you can:

1. Using the SQL hash value from the CURRENT_EVENT cursor, you can capture the SQL text from the GV$SQLTEXT view as shown next and store it in the appropriate history table.
If you are also interested in the SQL statistics, you can get the information from the V$SQLAREA using the same SQL hash value from the cursor:
SELECT INST_ID,
         HASH_VALUE,
         ADDRESS,
         PIECE,
         SQL_TEXT
    FROM GV$SQLTEXT
   WHERE HASH_VALUE = 4190307765                              --sql_hash_value
                                AND INST_ID = 1                 --RAC Instance
ORDER BY INST_ID, PIECE;

2. Identify The blocking session:
SELECT A.INST_ID,
       A.SID,
       A.SERIAL#,
       A.USERNAME,
       A.MODULE,
       A.PADDR,
       A.LOGON_TIME,
       A.SQL_HASH_VALUE,
       B.TYPE,
       B.LMODE,
       B.CTIME,
       A.STATUS,
          'alter system kill session '
       || ''''
       || A.SID
       || ',
'
       || A.SERIAL#
       || ''''
       || ' immediate;'
          KILL_SESSION
  FROM GV$SESSION A, GV$LOCK B
 WHERE     A.INST_ID = B.INST_ID
       AND A.SID = B.SID
       AND B.ID1 = 131253                                                 --P2
       AND B.ID2 = 0                                                      --P3
       AND B.BLOCK > 0;

3. Find out the enqueue name and mode requested by the waiting process:
SELECT SID,
       CHR (BITAND (P1, -16777216) / 16777215)
       || CHR (BITAND (P1, 16711680) / 65535)
          "Name",
       (BITAND (P1, 65535)) "Mode"
  FROM V$SESSION_WAIT
 WHERE EVENT = 'enqueue';

No comments:

Post a Comment