分析一则enq: HW – contention问题

同事在做压力测试的时候，说数据库很慢，于是登上数据库看了下，发现大量的enq: TX - row lock contention和enq: HW - contention事件。

%This     SECONDS        AAS WAIT_CHAIN
------ ---------- ---------- ------------------------------------------------------------------------------------------------------------------------------------------------
  18%         960        1.6 -> ON CPU
   9%         470         .8 -> enq: TX - row lock contention  -> ON CPU
   8%         460         .8 -> enq: HW - contention  -> ON CPU
   8%         410         .7 -> enq: TX - row lock contention  -> enq: HW - contention  -> ON CPU
   5%         260         .4 -> enq: TX - row lock contention  -> enq: HW - contention  -> enq: HW - contention
   4%         220         .4 -> enq: HW - contention  -> enq: HW - contention
   4%         210         .4 -> enq: HW - contention  -> enq: HW - contention  -> ON CPU
   3%         190         .3 -> enq: TX - row lock contention  -> enq: HW - contention  -> enq: HW - contention  -> ON CPU
   3%         180         .3 -> enq: TX - row lock contention  -> enq: TX - row lock contention  -> enq: HW - contention  -> ON CPU
   3%         160         .3 -> enq: TX - row lock contention  -> enq: TX - row lock contention  -> ON CPU
   2%         120         .2 -> enq: HW - contention  -> enq: HW - contention  -> enq: HW - contention
   2%         110         .2 -> enq: TX - row lock contention  -> enq: HW - contention  -> enq: HW - contention  -> enq: HW - contention  -> ON CPU
   2%         100         .2 -> log file parallel write
   2%         100         .2 -> enq: TX - row lock contention  -> enq: HW - contention  -> enq: HW - contention  -> enq: HW - contention  -> enq: HW - contention
   2%          90         .2 -> enq: TX - row lock contention  -> log file sync  -> log file parallel write
   2%          90         .2 -> enq: HW - contention  -> enq: HW - contention  -> enq: HW - contention  -> enq: HW - contention  -> enq: HW - contention  -> enq: HW - contention
   1%          80         .1 -> enq: TX - row lock contention  -> enq: TX - row lock contention  -> enq: HW - contention  -> enq: HW - contention
   1%          70         .1 -> enq: HW - contention  -> enq: HW - contention  -> enq: HW - contention  -> ON CPU

前者一般情况代表应用程序可能存在不合理的情况，导致大量的行锁争用，而后者表示高水位的移动争用，HW锁通常是用于管理超出高水位的段空间分配。段的高水位线是段中已使用空间和未使用空间的边界,如果出现了这个等待事件也就表示使用的数据达到了高水位线，需要将高水位线提高来满足额外的数据插入，通常是频繁的数据扩展造成的。

通过v$session_wait查出P3值等

select P2TEXT,p2, p3 from v$session_wait where event = 'enq: HW - contention';

P2TEXT                       P2         P3
-------------------- ---------- ----------
table space                   6   21051234

可以看出是表空间的自动扩展导致，然后用于分析是什么对象

select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(21051234) FILE#,
       DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(21051234) BLOCK#
  from dual;

     FILE#     BLOCK#
---------- ----------
         5      79714


select owner, segment_type, segment_name
  from dba_extents
 where file_id = 5
   and 79714 between block_id and block_id + blocks - 1
   and tablespace_name =
       (select name from ts$ where ts# = 6);

OWNER                          SEGMENT_TYPE       SEGMENT_NAME
------------------------------ ------------------ ---------------------------------------------------------------------------------
XXX                            TABLE              XXX_T

此外，如果这个等待事件是正在发生的，可以直接查看到使用的段

select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(ID2) FILE#,
       DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(ID2) BLOCK#
  from v$lock
 where type = 'HW';

所以为了解决这个争用，直接调大increment_by的值，或者直接调成非自动扩展模式，即可大幅缓解这个问题。

除了今天碰到的这种情况外，引起enq HW - contention事件还有可能是其他的原因

• IO性能问题
• 一个包含繁忙lob段的表需要满足平均分配DML操作到各个分区的分区方式
• lob段频繁的收缩扩展

针对最后一种情况也有一些常规的解决办法：

• 手动给lob段增加一些额外的空间，这样lob段就不需要频繁的回收块

ALTER TABLE <lob_table> MODIFY LOB (<column_name>) (allocate extent (size <extent size>));

• 通过shrink space命令和dbms_redefinition来对空间进行回收

ALTER TABLE test_lob MODIFY LOB (image) (SHRINK SPACE);