大量insert引起的enq: HW - contention等待

最新推荐文章于 2021-04-14 15:13:40 发布

coocy8221

最新推荐文章于 2021-04-14 15:13:40 发布

阅读量559

点赞数

文章标签：数据库

为防止多个进程同时修改HWM而提供的锁称为HW锁。想要移动HWM的进程必须获得HW锁。若在获取HW锁过程中发生争用，则等待enq: HW - contention事件。HW锁争用大部分是大量执行insert所引发的。

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

下面AWR信息是某时段业务处理缓慢， 数据库服务器资源紧张，EVENT出现大量enq: HW - contention

>>>>>DBTIME非常高。CPU资源紧张。

	Snap Id	Snap Time	Sessions	Cursors/Session	Instances
Begin Snap:	3020	13-Jan-16 14:36:29	792	9.8	2
End Snap:	3021	13-Jan-16 15:02:26	769	9.6	2
Elapsed:		25.94 (mins)
DB Time:		8,041.80 (mins)

>>>>> enq: HW - contention平均等待时间超过30m，严重超时

Event	Waits	Total Wait Time (sec)	Wait Avg(ms)	% DB time	Wait Class
enq: HW - contention	13,223	454K	34334	94.1	Configuration
log file sync	95,032	17.1K	180	3.5	Commit
direct path read	125,620	6893.6	55	1.4	User I/O
db file sequential read	19,684	1274.4	65	.3	User I/O
DB CPU		1048.9		.2
direct path write	17,776	818.1	46	.2	User I/O
gc buffer busy acquire	59,693	585.7	10	.1	Cluster
gc buffer busy release	2,888	293.3	102	.1	Cluster
reliable message	188,410	291.3	2	.1	Other
gc cr block busy	11,461	223.6	20	.0	Cluster

>>>>>94%的DB TIME都是由于enq: HW - contention造成

发现一LOB字段Segments Writes等待很严重。

desc表定义确实存在一个大字段。

SQL> desc HUB.T_CACHE
Name Null? Type
------------------ -------- -----------------
CACHE_ID NOT NULL VARCHAR2(100)
CACHE_CONTENT BLOB
CACHE_TIMESTAMP NOT NULL TIMESTAMP(6)

参考metalink，
Bug 6376915 - HW enqueue contention for ASSM LOB segments (文档 ID 6376915.8)
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=362487965591328&parent=DOCUMENT&sourceId=740075.1&id=6376915.8
Analyzing 'enq: HW - contention' Wait Event
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=362146541556026&id=740075.1&_afrWindowMode=0&_adf.ctrl-state=rma4knoc0_4

处理方法：
1. When using Auto matic 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 S egment 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

把源表换成分区表

CREATE TABLE "HUB"."T_CACHE"
( "CACHE_ID" VARCHAR2(100) NOT NULL ENABLE,
"CACHE_CONTENT" BLOB,
"CACHE_TIMESTAMP" TIMESTAMP (6) NOT NULL ENABLE,
CONSTRAINT "PK_T_CACHE" PRIMARY KEY ("CACHE_ID"))
partition by hash(CACHE_ID)
(partition p1 tablespace users,
partition p2 tablespace users,
partition p3 tablespace users,
partition p4 tablespace users );

观察一段时间， “enq: HW - contention” 现象已经不存在，故障解决

	Snap Id	Snap Time	Sessions	Cursors/Session	Instances
Begin Snap:	3027	13-Jan-16 16:16:19	486	6.5	2
End Snap:	3028	13-Jan-16 16:25:48	607	9.5	2
Elapsed:		9.48 (mins)
DB Time:		869.59 (mins)

Event	Waits	Total Wait Time (sec)	Wait Avg(ms)	% DB time	Wait Class
gc buffer busy acquire	426,665	18.7K	44	35.8	Cluster
log file sync	35,535	16.9K	477	32.5	Commit
direct path read	56,082	8634.6	154	16.5	User I/O
direct path write	6,528	1286	197	2.5	User I/O
gc buffer busy release	3,360	1240.3	369	2.4	Cluster
gc cr block busy	15,554	1152.5	74	2.2	Cluster
enq: TX - contention	106	1071.4	10108	2.1	Other
DB CPU		527.2		1.0
reliable message	58,857	411.9	7	.8	Other
gc current block busy	1,535	368.9	240	.7	Cluster

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

转载于:http://blog.itpub.net/17086096/viewspace-1983199/