如何診斷高水位爭(zhēng)用（enq: HW - contention）for lob

gflei

上文講到了高水位推進(jìn)的情況，當(dāng)并發(fā)會(huì)話同時(shí)進(jìn)行insert時(shí)，極易引起高水位爭(zhēng)用enq: HW - contention，那么發(fā)生此類爭(zhēng)用時(shí)，該如何診斷呢？ 

查看v$session_wait，應(yīng)該會(huì)有如下等待事件： 

SQL>select event,p1,p2,p3 from v$session_wait; 
EVENT                     P1         P2      P3 
---------------------- --------   ------- ---------- 
enq: HW - contention 1213661190  4 17005691 

通過(guò)P3進(jìn)行DBMS_UTILITY轉(zhuǎn)換可以獲知發(fā)生爭(zhēng)用的文件和block 
SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(17005691) FILE#, 
  2  DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(17005691) BLOCK# 
  3  from dual; 

     FILE#     BLOCK# 
---------- ---------- 
         4     228475 
         
進(jìn)而通過(guò)file#和block#定位對(duì)象 

SQL> select owner, segment_type, segment_name 
  2  from dba_extents 
  3  where file_id = 4 
  4  and 228475 between block_id and block_id + blocks - 1; 


OWNER SEGMENT_TYPE SEGMENT_NAME 
--------------- --------------- ------------------------------ 
SCOTT LOBSEGMENT EMP_DATA_LOB 

我們知道enqueue鎖的p2,p3值與v$lock的id1，id2值相同，同樣通過(guò)id2，也可以知道發(fā)生爭(zhēng)用的文件和block 
SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(ID2) FILE#, 
  2  DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(ID2) BLOCK# 
  3  from v$lock 
  4  where type = 'HW'; 
      FILE#     BLOCK# 
---------- ---------- 
         4     228475 

通過(guò)p1值可以知道鎖類型和模式 

SQL> select chr(bitand(1213661190,-16777216)/16777215)|| 
  2  chr(bitand(1213661190,16711680)/65535) "Lock",to_char( bitand(1213661190, 65535) ) "Mode" from dual; 
Lock       Mode 
---------- ---------- 
HW         6 

當(dāng)知道了，lob對(duì)象發(fā)生高水位爭(zhēng)用時(shí)，該怎么辦呢？metalink（740075.1）為我們提供了幾種解決辦法，僅供參考 

引用

1. When using Automatic Segment Space Management (ASSM) 

   a) As temporary workaround, manually add extra space to the LOB segment 
      ALTER TABLE <lob_table> 
      MODIFY LOB (<column_name>) (allocate extent (size <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 <lob_table>    
    MODIFY LOB (<column_name>) (allocate extent (size <extent size>)); 
OR 
     b) Consider partitioning the LOB in a manner that will evenly distribute concurrent DML across multiple partitions