如何進行Enqueue的整理

如何進行Enqueue的整理，針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。

當處理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的不同所以具有不同含義.

具體可以參見<<Wait.Interface.A.Practical.Guide.to.Performance.Diagnostics.and.Tuning>>第六章 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  <<Oracle入門,進階與診斷案例>>這本書的朋友可以參照學習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...命令創建所有臨時表空間。

關于如何進行Enqueue的整理問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注億速云行業資訊頻道了解更多相關知識。