ORACLE 如何診斷高水位爭用（enq: HW – contention）

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

    透過P3進行DBMS_UTILITY轉換可以獲知發生爭用的檔案和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
            
    進而透過file#和block#定位物件

    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值相同，同樣透過id2，也可以知道發生爭用的檔案和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

    透過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

    當知道了，lob物件發生高水位爭用時，該怎麼辦呢？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