Enqueue整理總結（轉）

建議大家看看原文 文件區可以下到..《Wait.Interface.A.Practical.Guide.to.Performance.Diagnostics.and.Tuning》 這本書寫的不錯.

當處理enqueue等待時,熟記這些要點:

1 Enqueues 是應用到資料庫物件的locks.與latch(應用於SGA記憶體)不同.
2 Enqueues 是由應用程式發起,具有事務性.(具體參見下文)
3 Oracle session 正在等候獲取一個特定的enqueue. enqueue的名字和mode被記錄在P1引數裡.針對不同型別的enqueue競爭應採用不同的方式解決.
4 一直到Oracle 9i,enqueue wait event 代表所有enqueue waits；
   從Oracle 10g開始enqueue waits被分類定義(下文詳細介紹).

What Is an Enqueue?
一個enqueue是什麼由具體環境決定. 如果它被看成是動詞,則表示將一個lock request置於一個佇列的動作.如果被看成是名詞,它表示一個特定的lock,比如TX的enqueue.

Enqueues 是一種非常精密的鎖定機制,用來管理訪問資料庫共享資源,比如(objects, background jobs, and redo threads).Oracle使用enqueues出於兩個目的: 第一,當enqueue為不相容模式(mode)時,enqueues防止併發sessions共享資料庫資源. 第二,當enqueue為相容模式時,enqueues容許併發sessions共享資料庫資源.

當session A請求一個資料庫物件上的鎖資源時,如果被請求的鎖模式為不相容模式,且該資料庫物件已經被另一個session 以不相容模式鎖持有,則session A將它的鎖請求放置於一個佇列裡並且按順序等待被喚起（防止活鎖）.這個事件被稱為enqueue wait.

Enqueue waits 除了包括buffer locks (discussed in the “Buffer Busy Waits” section), library cache locks, row cache locks, and PCM (Parallel Cache Management) locks,也是對各種local locks的等待.

What Is an Enqueue Resource?
An enqueue resource是由於受到一個enqueue lock的資料庫資源.

Oracle通過內部陣列結構進行管理.
通過X$KSQRS (kernel service enqueue resource) or V$RESOURCE 檢視可以看到具體條目.
SQL> select * from v$resource;

ADDR     TY        ID1        ID2
-------- -- ---------- ----------
6D304434 XR          4          0
6D304594 CF          0          0
6D3046F4 RT          1          0
6D30474C RS         25          1
6D304904 MR          3          0
6D30495C MR          4          0
6D3049B4 MR          5          0
6D304B24 MR          1          0
6D304B7C MR          2          0
6D304BD4 MR        201          0
6D305634 TA          6          1

ADDR     TY        ID1        ID2
-------- -- ---------- ----------
6D305B5C PW          1          0
6D3069DC TS          3          1

已選擇13行。

SQL>
根據以上查詢輸出,我們可以看到enqueue resource structure 由lock type和兩個引數組成. Oracle用兩個字元符號例如(TX, TM, TS, MR, RT, etc)表示lock type. ID1,ID2兩個引數由於lock type的不同所以具有不同含義.
具體可以參見<>第六章 Enqueue部分 Table6-3.
在Oracle10g之前,ID1和ID2對於不同型別lock的含義並未公開.
在Oracle10g中可以通過如下查詢輕鬆得到.
col name for a20
col parameter2 for a20
col parameter3 for a20
select name,parameter2,parameter3 from v$event_name
/
稍後在本文enqueue資源爭用部分再對ID1與ID2作詳細介紹.

可以被lock manager並行鎖的enqueue resources的最大數量由ENQUEUE_RESOURCES初始化引數控制.該引數預設值通常來講已經足夠,如果在應用程式中使用並行DML操作,可以考慮增加ENQUEUE_RESOURCES的值.
關於ENQUEUE_RESOURCES [10..65535]
At database startup time, Oracle allocates the number of enqueues specified by the ENQUEUE_RESOURCES parameter. The default value of ENQUEUE_RESOURCES is derived from the SESSIONS parameter and is usually adequate, as long as its value is greater than DML_LOCKS + 20. For three or fewer sessions, the default value is the number of database files + 20. For 4 to 10 sessions, the default value is the number of database files + ((SESSIONS - 3) * 5) + 20. For more than 10 sessions, it is the number of database files + ((SESSIONS - 10) * 2) + 55.
If you explicitly set ENQUEUE_RESOURCES to a value higher than DML_LOCKS + 20, then Oracle uses the value you provide. Oracle will automatically allocate additional enqueues from the shared pool as needed if the number specified by ENQUEUE_RESOURCES is exceeded. You can check resource usage by querying V$RESOURCE_LIMIT.
不能獲取an enqueue resource 的程式會報ORA-00052: "maximum number of enqueue resources exceeded" error.

V$RESOURCE_LIMIT檢視提供系統資源使用的資訊.通過這個檢視可以監控很多資料庫資源(resources, locks, or processes)的消耗情況.
注意一下這個檢視的幾個欄位:
MAX_UTILIZATION 表示例項啟動後該類資源的最大消耗值
以下監控與enqueue有關的資源消耗情況:

col name for a18
col limit_usage for a15
select RESOURCE_NAME name,
           CURRENT_UTILIZATION cur_usage,
           MAX_UTILIZATION max_usage,
           LIMIT_VALUE limit_usage,
           INITIAL_ALLOCATION init_allo
  from v$resource_limit
where resource_name in ('enqueue_resources','enqueue_locks',
                         'dml_locks','processes','sessions')
/
NAME                CUR_USAGE  MAX_USAGE LIMIT_USAGE     INIT_ALLO
------------------ ---------- ---------- --------------- -----------
processes                  19         24         90              90
sessions                   23         30        104             104
enqueue_locks              13         24       1480            1480
enqueue_resources          13         13  UNLIMITED             676
dml_locks                   0          7  UNLIMITED             456

What Is an Enqueue Lock?
An enqueue lock 是lock本身.Oracle用一個單獨的陣列管理.可以通過X$KSQEQ (kernel service enqueue object) 或者 V$ENQUEUE_LOCK 試圖察看.隱含引數_ENQUEUE_LOCKS影響這個陣列的大小.

Oracle使用不同的結構管理TX and TM 佇列.
X$KTCXB (kernel transaction control transaction object—the base view for V$TRANSACTION_ENQUEUE) and X$KTADM (kernel transaction access definition dml lock). TRANSACTIONS and DML_LOCKS 初始化引數會決定管理TX and TM 佇列的結構大小.
當然通過查詢V$LOCK可以獲得所有資料庫locks的資訊.也是我們診斷enqueue爭用的一個重要檢視.


Enqueue Architecture

在資料庫內部,enqueue architecture 和 sga中cache buffers architecture十分近似. (讀過eygle  <>這本書的朋友可以參照學習enqueue architecture)
enqueue architecture 的最主要元件由enqueue hash chains latches, enqueue hash table, enqueue hash chains, and enqueue resources組成.
它們之間的數量關係:
enqueue hash chains latch (1:m) a  hash bucket(1:1) a enqueue hash chain .
（意思也就是說一個hash chains latch可以管理多個hash bucket，而每一個hash bucket都只包含著一個hash chain）

子enqueue hash chains latches 保護enqueue hash table and hash chains.預設狀態下enqueue hash chains latches的個數等於CPU_COUNT,這個數量可以通過隱含引數_ENQUEUE_HASH_CHAIN_LATCHES調整.

根據resource type and identifiers  v$resource(TY,ID1,ID2) Enqueue resources 被hash到enqueue hash table 並且被置於相應enqueue hash chains.要使用某個enqueue resource必須獲取相應enqueue hash chain. enqueue hash table 的大小來源於SESSIONS初始化引數,也可以通過設定_ENQUEUE_HASH設定（375個）.
If you ever need to increase the ENQUEUE_RESOURCES parameter significantly from its default value, you might want to keep an eye on the sleep rate of the enqueue hash chains latches. This is because the enqueue hash table length will remain the same because it is derived from SESSIONS, not from ENQUEUE_RESOURCES. The combination of a high demand for enqueue resources and a small enqueue hash table will result in a higher hash collision rate and potentially lengthy hash chains. This problem manifests itself as latch contentions for the enqueue hash chains latches. In this case, you need to increase the _ENQUEUE_HASH.
enqueue hash table length = ((SESSIONS – 10) * 2) + 55
通過dump enqueue 結構進一步學習:
alter session set events ’immediate trace name enqueues level 3’;


Decoding Enqueue Type and Mode

Oracle 通過對P1列的解碼,我們可以得知enqueue type 和 mode.

select sid, event, p1, p1raw,
       chr(bitand(P1,-16777216)/16777215)||chr(bitand(P1,16711680)/65535) type,
       mod(P1, 16) "MODE"
  from v$session_wait
where event ='enqueue'
/

另一種方法.通過v$session_wait(p1raw)列也可以.
The values from the above example are from a 64-bit Oracle Database. You can ignore the leading zeros and focus on the last 4 bytes (that is, the last eight numbers). The high order 2 bytes give the enqueue type. Using 54580006 as an example, the 2 high order bytes are 0x5458Hex. Now, 54Hex is decimal 84 and 58Hex is decimal 88, so the enqueue type can be discovered as follows: (Appendix B has a complete list of enqueue types.)
select chr(84) || chr(88) from dual;CH--TX

Oracle 10g後enqueue wait event name 可以通過v$session_wait(event)直接獲得.

Processes request enqueue locks in one of these six modes: Null (N), Row Share (RS), Row Exclusive (RX), Share (S), Share Row Exclusive (SRX), or Exclusive (X)
這部分可參見Oracle相關文件.

Common Causes, Diagnosis, and Actions

由於enqueue type種類繁多,產生an enqueue wait event的原因也不同.我們可以根據多個session正在競爭的enqueue的type和mode找到不同的解決方法.
對於每一種type的enqueue,Oracle內部通過X$KSQST 維護一條相應的記錄.在Oracle9i中,通過V$ENQUEUE_STAT檢視, 可以具體獲得不同type enqueue的請求統計資訊(例項啟動後的統計值).

-- Oracle 7.1.6 to 8.1.7
select inst_id,
       ksqsttyp inst_lock,
       ksqstget inst_Gets,
       ksqstwat inst_Waits
  from x$ksqst
where ksqstwat > 0
order by inst_id,ksqstwat
/

-- Oracle9i Database and above
select *
  from v$enqueue_stat
where cum_wait_time > 0
order by inst_id, cum_wait_time
/

下面就最常見的一些enqueue等待事件進行討論.

Wait for TX Enqueue in Mode 6

對於 TX enqueue in mode 6 的等待是最常見的enqueue wait
(In Oracle Database 10g, the wait event name is enq: TX—row lock contention.) .當一個事物試圖update 或 delete 一行或多行已經被另一個事物鎖定的記錄時這個等待事件發生.通常這是由應用程式引起的.
eg.
在Session A中:
update a set object_id=0 where object_id=11;
與此同時在Session B中執行相同的語句,Session B is hung:
update a set object_id=0 where object_id=11;
Session B一直會等待session A 提交或回滾此事物.
沒有其他方法可以釋放Session A持有的row exclusive lock(Kill session A會引起事物被pmon回滾並釋放相應lock資源)

The following listing shows an example of TX enqueue wait in mode 6 as seen in the V$LOCK view:

ADDR     KADDR    SID TY    ID1    ID2 LMODE REQUEST CTIME BLOCK                                  -------- -------- --- -- ------ ------ ----- ------- ----- -----                                                                  A3950688 A395069C  10 TM 188154      0     3       0     3     0
A304E2A0 A304E2B0  10 TX  65585 147836     0       6     3     0
01AD23D4 01AD24A4  20 TX  65585 147836     6       0    10     1A3950A28 A3950A3C  20 TM 188154      0     3       0    10     0

每當你發現TX enqueue等待事件時,首要任務是找出哪個session是鎖阻塞者(session A),哪些session是鎖等待者(session B),在一個繁忙的系統中,很科能伴有多個鎖等待者.
使用以下sql定位即可:
/*
BLOCKER_SID: 鎖持有者sid(阻塞sid)
WAITER_SID: 等待者sid
MODE_HELD: 鎖持有模式
REQUEST_MODE: 請求模式
*/
select /*+ ordered */ a.sid         
                      blocker_sid,
                      a.username
                      blocker_username,
                      a.serial#,
                      a.logon_time,
                      b.type,     
                      b.lmode mode_held,
                      b.ctime       time_held,
                      c.sid         waiter_sid,      
                      c.request     request_mode,
                      c.ctime       time_waited
   from   v$lock b,
          v$enqueue_lock c,
          v$session a
   where  a.sid = b.sid
     and  b.id1 = c.id1(+)
     and  b.id2 = c.id2(+)
     and  c.type(+) = 'TX'
     and  b.type ='TX'
     and  b.block = 1
order by time_held, time_waited
/

你也可通過以下查詢定位,哪些資料庫資源正在被徵用.
select c.sid waiter_sid,
       a.object_name,
       a.object_type
  from dba_objects a,
       v$session b,
       v$session_wait c
where ( a.object_id = b.row_wait_obj# or a.data_object_id = b.row_wait_obj#)
   and  b.sid = c.sid
   and chr(bitand(c.P1,-16777216)/16777215) || chr(bitand(c.P1,16711680)/65535) = 'TX'
   and c.event like 'enq%'
/

當發生TX Enqueue in Mode 6等待事件時
獲取到waiting session 和 blocking session正在執行的sql語句十分重要.我們可以通過這個sql語句定位應用程式可能出現問題(1 很有可能應用程式沒有commit 2 沒有及時commit 或 rollback DML操作 這就需要結合應用需求而調整了)的大致範圍.

Wait for TX Enqueue in Mode 4—ITL Shortage

TX enqueue in mode 4 的等待事件我們分成3部分討論.
1> ITL (interested transaction list) shortage
2 > Unique key enforcement
3 > Bitmap index entry
Ok 我們先從ITL Shortage說起.


SQL> select  dbms_rowid.ROWID_RELATIVE_FNO(rowid) file_no,
  2          dbms_rowid.ROWID_BLOCK_NUMBER(rowid) blk_no
  3    from a
  4   where rownum<2
  5  /

   FILE_NO     BLK_NO
---------- ----------
         4       1550

SQL> alter system dump datafile 4 block 1550;

系統已更改。

Itl           Xid                                               Uba            Flag  Lck        Scn/Fsc
0x01   0x000a.017.00000128  0x00802ee0.004b.2a  C---    0  scn 0x0000.00076ef1
0x02   0x0005.01c.00000133  0x00803f33.0059.28  --U-    9  fsc 0x0009.00079e2b
0x03   0x0002.02c.0000012a  0x00800605.005b.1b  C---    0  scn 0x0000.00076d65

ITL是資料塊內"感興趣的事物列表",是資料塊內事物插槽(transaction slot).由建表時INITRANS 和 MAXTRANS子句決定. "--U-"表示此ITL正在被使用.
在預設情況下,表所包含的每個資料塊中有1個ITL,索引有2個ITL,每個ITL佔據24個位元組,以USN.SLOT#.WRAP#. 形式包含事務ID. 每一個DML事物被處理之前必須佔有資料塊內ITL空間,當某個資料庫塊內所有可用的ITL都在使用中，並且PCTFREE中沒有空間讓ORACLE動態分配一個新的ITL事物插槽時，ITL爭用就會發生,在這種情況下,會話將持續等待,直到一個事務被提交或回滾.


The following listing shows an example of the TX enqueue wait in mode 4 that is caused by ITL shortage, as seen in the V$LOCK view.

ADDR     KADDR    SID TY    ID1    ID2 LMODE REQUEST CTIME BLOCK                              -------- -------- --- -- ------ ------ ----- ------- ----- -----                                                                                8A2B6400 8A2B6414   8 TM   3172      0     3       0   248     0
89EF3A0C 89EF3A1C   8 TX 131147     13     0       4   248     0
01A4177C 01A41848   9 TX 131147     13     6       0   376     18                                            A2B6388 8A2B639C   9 TM   3172      0     3       0   376     0

從Oracle 9i開始 可以通過以下sql定位ITL徵用的資料庫物件.
SELECT s.owner,
       s.object_name,
       s.subobject_name,
       s.object_type,
       s.tablespace_name,
       s.VALUE,
       s.statistic_name
FROM v$segment_statistics s
WHERE s.statistic_name = 'ITL waits'
  AND s.VALUE > 0
ORDER by VALUE DESC
/





Wait for TX Enqueue in Mode 4—Unique Key Enforcement

Unique or primary key enforcement is yet another reason you might see contention for the TX enqueue in mode 4. (In Oracle Database 10g, the wait event name is enq: TX—row lock contention.)
This only occurs when multiple concurrent sessions insert the same key value into a table that has one or more unique key constraints. The first session to insert the value succeeds, but the rest freeze and wait until the first session commits or rolls back to see if “ORA-00001 unique constraint (%s.%s) violated” should be raised.

The following listing shows an example of a TX enqueue wait in mode 4 as seen in the V$LOCK view that is due to unique key enforcement. What is the difference between this listing and the one caused by ITL shortage? Notice that the waiter (SID=8) has two TX entries in the V$LOCK view. This doesn’t mean that it owns two transactions. In fact, the V$TRANSACTION view shows only two transactions—one for SID 8 and another for SID 9. This shows SID 8 is waiting for the TX lock held by SID 9, and it wants a share lock (mode 4) on the object. SID 8 also holds a TX lock for its own transaction. Another thing you should be aware of is the object ID that is recorded in ID1 of the DML transaction (TM) is always the table ID, not the index ID, although a unique key is enforced through an index.





Wait for TX Enqueue in Mode 4—Bitmap Index Entry

A wait for the TX enqueue in mode 4 can also occur when multiple sessions try to update or delete different rows that are covered by the same bitmap entry. Of course, this does not apply if the application does not use bitmap indexes.

Unlike the B-tree index entry, which contains a single ROWID, a bitmap entry can potentially cover a range of ROWIDs. So when a bitmap index entry is locked, all the ROWIDs that are covered by the entry are also locked.

The following listing shows an example of a TX enqueue wait in mode 4 as seen in the V$LOCK view due to bitmap entry. What is the difference between this listing and the preceding one in the unique key enforcement? Can you tell if you are dealing with a bitmap index entry or unique key enforcement issue by looking at the V$LOCK view output? No, you can’t. The object ID in the TM lock doesn’t help either, as it is the object ID of the table and not the index. That is why it is very important for you to capture the SQL statement of the waiting and blocking sessions.

If the waiting session is attempting an insert, you are dealing with a unique key enforcement issue.
如果waiting session正在試圖作insert操作.
If the waiting session is attempting an update or delete, most likely you are dealing with a bitmap entry issue.如果waiting session正在試圖作update or delete操作

In order to resolve the contention, you have to hunt down the offending user. However, the offending user is not always the user who holds the lock. That user was there first, for crying out loud. If the user has a legitimate reason to hold the lock, the waiters should back out of their transactions.


Wait for ST Enqueue

每個資料庫只有一個ST鎖。
修改UET$(使用者範圍表)和FET$(空閒範圍表)的資料庫操作需要ST鎖，這包括刪除、擷取、結合等動作。
ST鎖爭用表明有多個活動會話在字典管理的表空間中執行動態磁碟空間分配或解除分配。不是使用TEMPORARY子句建立的臨時表空間和經歷大範圍分配和解除分配的字典管理的表空間是ST鎖爭用的主要原因。

減少ST鎖爭用的方法：

1) 使用本地管理表空間，在ORACLE9i中所有表空間均可本地化管理。

2) 使用CREATE TEMPORARY TABLESPACE TEMPFILE...命令建立所有臨時表空間。