10亿多条记录的表触发enq: HW - contention等待事件将数据库夯死

问题描述

下班刚回到家就接到同事电话说是应用特别卡，数据库连接工具查询也很慢。先让其生成时间段的AWR报告发我。

分析过程

查看AWR报告中的负载

105,244.05/119.85/112=7.840459056 负载很高了

查看AWR报告中的Top 10 Foreground Events by Total Wait Time

到家后打开AWR报告发现Top 10 Foreground Events by Total Wait Time排名最靠前的是enq: HW - contention的等待事件。

查看AWR报告中的SQL Statistics

SQL ordered by Elapsed Time

SQL ordered by CPU Time

SQL ordered by User I/O Wait Time

SQL ordered by Physical Reads (UnOptimized)

查询CPU和IO负载高相关表的数据量

先查执行次数为空的表数据量

哇，10亿多条数据啊，推测是该表导致的HWM高水位争用，产生的等待事件enq: HW - contention。

select count(*) from PUB_RECORD_LOG;

1030316780

接着查CPU和IO负载高相关表的数据量

以现场112核CPU 250G内存来讲，数据量还可以，不该是导致数据库卡的原因。

select count(*) from PUB_AUDITSTATE   --3413888

select count(*) from REG_BUSMAIINF_XMLDATA   --7646856

enq: HW - contention的官方说明

The HW enqueue is used to serialize the allocation of space beyond the high water mark of a segment.

?V$SESSION_WAIT.P2 / V$LOCK.ID1 is the tablespace number.

?V$SESSION_WAIT.P3 / V$LOCK.ID2 is the relative data block address (dba) of segment header of the object for which space is being allocated.

If this is a point of contention for an object, then manual allocation of extents solves the problem.

翻译：

HW enqueue 用于序列化超出 segment 高水位线的空间分配。

?V$SESSION_WAIT。P2 / V$LOCK。ID1 是表空间编号。

?V$SESSION_WAIT。P3 / V$LOCK。ID2 是为其分配空间的对象分段标头的相对数据块地址 （dba）。

如果这是对象的争用点，则手动分配扩展数据块可解决问题。

enq: HW - contention解释

为防止多个进程同时修改HWM而提供的锁称为HW锁。想要移动HWM的进程必须获得HW锁。若在获取HW锁过程中发生争用，则等待enq: HW - contention事件。HW锁争用大部分是因大量执行insert所引发的，偶尔也会因大量执行update在回滚段中发生HW锁争用现象。若是update，表中段的扩展的大小虽然不多，但在创建回滚数据的过程中，需要回滚段的急速扩张。HW锁争用是在急速空间扩张时普遍出现的等待现象，有时也会引发严重的性能下降。

select event#,name,parameter1,parameter2,parameter3 from v$event_name where name = 'enq: HW - contention';

EVENT#      NAME              PARAMETER1  PARAMETER2  PARAMETER3
---------- ----------------  ------------ ------------ --------------
254 enq:    HW - contention    name|mode   table space # block

Oracle高水位线标志着该线以下的block均被Oracle格式过，通俗一点讲就是该高水位线以下的block都被Oracle使用过。 通常在执行insert操作时，当高水位线以下block不够用时，Oracle将会推进高水位线。更进一步讲，当有多个进程在同时进行insert操作时，比较容易引起高水位线争用，主要表现为enq: HW - contention。

找到事件：'enq: HW - contention' 热点对象

查看v$session_wait，应该会有如下等待事件

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

输出如下：

P1	        P2	 P3
1213661190	6	  310385682
1213661190	6	  310385682
1213661190	6	  310385682
1213661190	6	  310385682
1213661190	6	  310385682
1213661190	6	  310385682
1213661190	6	  310385682

通过P3进行DBMS_UTILITY转换可以获知发生争用的文件和block

一定得替换成上个步骤的P3值

select dbms_utility.data_block_address_block(310385682),dbms_utility.data_block_address_file(310385682) from dual;

输出如下：

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(310385682) DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(310385682)
------------------------------------------------ ------------------------------------
1507334  289

通过file#和block#定位对象

select owner, segment_type, segment_name
from dba_extents
where file_id = 289 and 1507334 between block_id and block_id + blocks - 1;

解决办法

办法1：清理表记录(本案例采用)

和开发沟通PUB_RECORD_LOG表是记录的日志表，一般没啥用，只有排查业务的时候会用；如果太大的话，建议全部清掉就行 。最后排查的结果是应用中定时任务中有对该表的清理但是配置项丢失，清理掉表记录后，联系应用方面的同事进行配置项的增加。

truncate table PUB_RECORD_LOG;

办法2：手动对对象提前分配空间(本案例未采用)

为了减少HW锁争用，可手动对对象提前分配空间。由于头头未同意，该方法暂时搁置。

alter table PUB_RECORD_LOG allocate extent;

参考链接：等待事件enq:HW–contention说明及解决方法 - 数据派 - 博客园 (cnblogs.com)