大量insert引起的enq: HW - contention等待

startay發表於2016-01-27
ENQ

為防止多個程式同時修改HWM而提供的鎖稱為HW鎖。想要移動HWM的程式必須獲得HW鎖。若在獲取HW鎖過程中發生爭用，則等待enq: HW - contention事件。HW鎖爭用大部分是大量執行insert所引發的。

眾所周知，Oracle高水位線標誌著該線以下的block均被Oracle格式過，通俗一點講就是該高水位線以下的block都被Oracle使用過。 通常在執行insert操作時，當高水位線以下block不夠用時，Oracle將會推進高水位線。更進一步講，當有多個程式在同時進行insert操作時，比較容易引起高水位線爭用，主要表現為enq: HW - contention。

下面AWR資訊 是某時段業務處理緩慢，資料庫伺服器資源緊張，EVENT出現大量enq: HW - contention

>>>>>DBTIME非常高。CPU資源緊張。
Snap Id Snap Time Sessions Cursors/Session Instances
Begin Snap: 3020 13-Jan-16 14:36:29 792 9.8 2
End Snap: 3021 13-Jan-16 15:02:26 769 9.6 2
Elapsed:   25.94 (mins)      
DB Time:   8,041.80 (mins)    

>>>>>enq: HW - contention平均等待時間超過30m， 嚴重超時
Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
enq: HW - contention 13,223 454K 34334 94.1 Configuration
log file sync 95,032 17.1K 180 3.5 Commit
direct path read 125,620 6893.6 55 1.4 User I/O
db file sequential read 19,684 1274.4 65 .3 User I/O
DB CPU   1048.9   .2  
direct path write 17,776 818.1 46 .2 User I/O
gc buffer busy acquire 59,693 585.7 10 .1 Cluster
gc buffer busy release 2,888 293.3 102 .1 Cluster
reliable message 188,410 291.3 2 .1 Other
gc cr block busy 11,461 223.6 20 .0 Cluster

>>>>>94%的DB TIME都是由於enq: HW - contention造成


發現一LOB欄位Segments Writes等待很嚴重。



desc表定義確實存在一個大欄位。
  1. SQL> desc HUB.T_CACHE
  2.  Name                Null?      Type
  3.  ------------------ -------- -----------------
  4.  CACHE_ID          NOT NULL    VARCHAR2(100)
  5.  CACHE_CONTENT                 BLOB
  6.  CACHE_TIMESTAMP   NOT NULL    TIMESTAMP(6)

參考metalink，
Bug 6376915 - HW enqueue contention for ASSM LOB segments (文件 ID 6376915.8)
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=362487965591328&parent=DOCUMENT&sourceId=740075.1&id=6376915.8
Analyzing 'enq: HW - contention' Wait Event
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=362146541556026&id=740075.1&_afrWindowMode=0&_adf.ctrl-state=rma4knoc0_4

處理方法：
1. When using Automatic Segment Space Management (ASSM) 
   a) As temporary workaround, manually add extra space to the LOB segment 
      ALTER TABLE  
      MODIFY LOB () (allocate extent (size )); 
OR 
   b) It may related Bug 6376915. 
   Refer to Note 6376915.8 "Bug 6376915 HW enqueue contention for ASSM LOB segments" 
   In 10.2.0.4 or above, this fix has been included, and can be enabled by setting event 44951 to a value 
   between 1 and 1024.  A higher value would be more beneficial in reducing contention. 
   EVENT="44951 TRACE NAME CONTEXT FOREVER, LEVEL < 1 - 1024 >" 
OR 
  c) Consider partitioning the LOB  in a manner that will evenly distribute concurrent DML across multiple partitions          >>>>我們使用建立分割槽表解決

2. When using Manual Segment Space Management(MSSM) 
a) As temporary workaround, manually add extra space to the LOB segment 
    ALTER TABLE    
    MODIFY LOB () (allocate extent (size )); 
OR 
     b) Consider partitioning the LOB in a manner that will evenly distribute concurrent DML across multiple partitions


把源表換成分割槽表
  1. CREATE TABLE "HUB"."T_CACHE"
       (    "CACHE_ID" VARCHAR2(100) NOT NULL ENABLE,
            "CACHE_CONTENT" BLOB,
            "CACHE_TIMESTAMP" TIMESTAMP (6) NOT NULL ENABLE,
             CONSTRAINT "PK_T_CACHE" PRIMARY KEY ("CACHE_ID"))
      partition by hash(CACHE_ID)
       (partition p1 tablespace users,
       partition p2 tablespace users,
       partition p3 tablespace users,
       partition p4 tablespace users );

觀察一段時間，“enq: HW - contention” 現象已經不存在，故障解決

Snap Id Snap Time Sessions Cursors/Session Instances
Begin Snap: 3027 13-Jan-16 16:16:19 486 6.5 2
End Snap: 3028 13-Jan-16 16:25:48 607 9.5 2
Elapsed:   9.48 (mins)      
DB Time:   869.59 (mins)      


Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
gc buffer busy acquire 426,665 18.7K 44 35.8 Cluster
log file sync 35,535 16.9K 477 32.5 Commit
direct path read 56,082 8634.6 154 16.5 User I/O
direct path write 6,528 1286 197 2.5 User I/O
gc buffer busy release 3,360 1240.3 369 2.4 Cluster
gc cr block busy 15,554 1152.5 74 2.2 Cluster
enq: TX - contention 106 1071.4 10108 2.1 Other
DB CPU   527.2   1.0  
reliable message 58,857 411.9 7 .8 Other
gc current block busy 1,535 368.9 240 .7 Cluster



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17086096/viewspace-1983199/，如需轉載，請註明出處，否則將追究法律責任。

相關文章