enq: HW - contention争用解决

早上zabbix告警，连接数突然间暴增，登录到数据库查看，有enq: HW - contention争用

enq: HW - contention的官方说明及解释

通过查询官方能够得到大概意思.等待事件从分类来看属于enq，这个是内存结构锁且是serial。

Enqueues are shared memory structures (locks) that serialize access to database resources. They can be associated with a session or transaction.

The HW enqueue is used to manage the allocation of space beyond the high water mark of a segment. The high water mark of a segment is the boundary between used and unused space in that segment. If contention is occurring for “enq: HW – contention” it is possible that automatic extension is occuring to allow the extra data to be stored since the High Water Mark has been reached. Frequent allocation of extents, reclaiming chunks, and sometimes poor I/O performance may be causing contention for the LOB segments high water mark.

据官方文档描述，Oracle设计 HW – contention队列意义在于对于数据库资源来讲，对于保护内存结构都需要“锁”的概念来控制并发，当申请超过高水位空间时，为防止多个进程同时修改HWM而提供的锁称为HW锁。想要移动HWM的进程必须获得HW锁，但是获取HW锁还是排他锁,因为是加排他锁来保证结构不被破坏.enq: HW – contention这个等待，简言之为’HW等待’，每当请求扩展segment高水位线high water mark之外的空间时需要获取申请锁。

HW锁争用大部分是因大量执行insert所引发的，偶尔也会因大量执行update在回滚段中发生HW锁争用现象。若是update，表中段的扩展的大小虽然不多，但在创建回滚数据的过程中，需要回滚段的急速扩张。HW锁争用是在急速空间扩张时普遍出现的等待现象，有时也会引发严重的性能下降。

1. 查看系统争用的事件,发现是"enq: HW - contention"

select count(1),INST_ID,event from Gv$session

where type='USER' AND SQL_ID IS NOT NULL

GROUP BY INST_ID,EVENT ORDER BY 1 DESC

---查看争用的Object，方法一 & 方法二都可查看到哪个Objects的争用

方法一：

1) 查看争用的sql，可查看到最多的是“217ds22cvy9dd” 这个语句

select count(1),INST_ID,event,SQL_ID from Gv$session where type='USER' AND SQL_ID IS NOT NULL GROUP BY INST_ID,SQL_ID,EVENT ORDER BY 1 DESC

2) 根据sql_id,查看执行计划，可看到表是对LD开头的表进行插入的操作

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('217ds22cvy9dd',NULL,'ADVANCED'))；

方法二：

1）查看争用时间涉及到的p1、p2、p3的信息

select P1,P1TEXT,P2,P2TEXT,P3,P3TEXT,count(1) from Gv$session where type='USER' AND SQL_ID IS NOT NULL AND EVENT='enq: HW - contention'

GROUP BY P1,P1TEXT,P2,P2TEXT,P3,P3TEXT ORDER BY 6 DESC

2）根据p3的号码，查看到争用的Object所属的file id & block  id

select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(659233506) FILE#,

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(659233506) BLOCK#

from dual;

3)根据步骤2）查看的文件号和块号定为到具体时间哪个OBJECT,同样可以看到是哪个用户的哪个objects

select owner, segment_type, segment_name

from dba_extents

where file_id = 157

and 727778 between block_id and block_id + blocks-1;

MOS上关于这个“enq: HW - contention”争用，建议将这个表变成分区表，或者避免大批量的插入数据

以下是MOS原文

Bug 18221857 - "enq: HW - contention" on large segments with lots of extents and high insert rate (Doc ID 18221857.8)    To BottomTo Bottom    

This note gives a brief overview of bug 18221857.

The content was last updated on: 08-MAR-2022

Click here for details of each of the sections below.

Affects:

Product (Component)    Oracle Server (Rdbms)

Range of versions believed to be affected    Versions BELOW 12.2

Versions confirmed as being affected    

(None Specified)

Platforms affected    Generic (all / most platforms affected)

Fixed:

The fix for 18221857 is first included in    

12.2.0.1 (Base Release)

12.1.0.2 (Server Patch Set)

11.2.0.4 Bundle Patch 15 for Exadata Database (Jan 2015)

Interim patches may be available for earlier versions - click here to check.

Symptoms:

Related To:

Waits for "enq: HW - contention"

Waits for "L1 validation"

ASSM Space Management (Bitmap Managed Segments)

INSERT

Description

Large segments having high number of extents and high insert rate experiencing

following waitevents

1) hwm contention

2) bmb contention

Small segments 100g or less is not exposed to this contention with same high

insert rates.

Rediscovery Notes

  

Large segments having high number of extents and high insert rate experiencing

following waitevents

1) enq: HW - contention

2) L1 validation

  

Workaround

  

Consider a partitioning strategy that results in smaller segments and spread out inserts.

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.

References

Bug:18221857 (This link will only work for PUBLISHED bugs)

Note:245840.1 Information on the sections in this article