如何诊断等待事件 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
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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7199859/viewspace-1174629/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7199859/viewspace-1174629/