enq: HW - contention等待事件模拟
enq: HW - contention
为防止多个进程同时修改HWM而提供的锁称为HW锁。想要移动HWM的进程必须获得HW锁。若在获取HW锁过程中发生争用，则等待enq: HW - contention事件。
Parameters:
P1 = name|mode
P2 = tablespace#
P3 = block (RDBA)
环境信息
操作系统：VMWARE虚拟机 linux 7.6
数据库：RAC 11.2.0.4.201020
压测工具：Swingbench
初始化环境
Swingbench安装
$cd /home/oracle
$tar -zxvf jdk-8u291-linux-x64.tar.gz
$unzip swingbenchlatest.zip
$vi .bash_profile
#添加如下内容
export JAVA_HOME=/home/oracle/jdk1.8.0_291
export CLASSPATH=.:${JAVA_HOME}/jre/lib/rt.jar:${JAVA__HOME}/lib/dt.jar:${JAVA_HOME}/lib/tools.jar:${CLASSPATH}
export PATH=${JAVA_HOME}/bin:$PATH
$source .bash_profile
#查看jdk版本
$java -version
数据库准备
#创建swingbench使用表空间test
create tablespace test datafile '+DATADG' size 1G autoextend on ;
#创建测试表数据使用表空间test1
create tablespace test1 datafile '+DATADG' size 100m autoextend on ;
#创建测试表
alter system set deferred_segment_creation=false;
grant dba to scott;
conn scott/trigger
truncate table t1;
create table t1 tablespace test1 as select * from dba_objects where 1=2;
alter table t1 modify OBJECT_NAME varchar(2000);
insert脚本准备
300个并发，每个并发插入200条，共计插入60000条
cat run.sh
#!/bin/bash
insert()
{
sqlplus scott/trigger <<EOF
begin
for j in 1..200
loop
insert into t1(object_id,created,object_name) values(j,sysdate,rpad(j,2000,'a'));
commit;
end loop;
end;
/
EOF
}
main()
{
if [ $# -ne 1 ]
then
echo "Usage: sh run.sh insert"
exit 1
fi
for i in `seq 1 300`
do
insert &
done
wait
}
main $@
测试记录
IO正常情况
不提前分配空间
不提前分配空间
sqlplus scott/trigger
truncate table t1;
开启insert
time sh run.sh insert
测试结果
select event,p1,p2,p3,count(*)
from v$active_session_history where to_char(sample_time,'yyyy-mm-dd hh24:mi:ss') between '2023-03-22 16:18:06' and '2023-03-22 16:23:08'and
event='enq: HW - contention' group by event,p1,p2,p3 order by count(*);
EVENT P1 P2 P3 COUNT(*)
-------------------------------------------- ---------- ---------- ----------
enq: HW - contention 1213661190 7 29360258 1720
select event,round(avg(time_waited)/1000,0) avg_,round(min(time_waited)/1000,0) min_,round(max(time_waited)/1000,0) max_,count(*)
from v$active_session_history where to_char(sample_time,'yyyy-mm-dd hh24:mi:ss') between '2023-03-22 16:18:06' and '2023-03-22 16:23:08'and
event='enq: HW - contention' group by event order by count(*);
EVENT AVG_ MIN_ MAX_ COUNT(*)
---------------------------------------- ---------- ---------- ----------
enq: HW - contention 104 0 1028 1720
select event,round(avg(time_waited)/1000,0) avg_,round(min(time_waited)/1000,0) min_,round(max(time_waited)/1000,0) max_,count(*)
from v$active_session_history where to_char(sample_time,'yyyy-mm-dd hh24:mi:ss') between '2023-03-22 16:18:06' and '2023-03-22 16:23:08'and
event like 'enq%' group by event order by count(*);
EVENT AVG_ MIN_ MAX_ COUNT(*)
---------------------------------------- ---------- ---------- ----------
enq: FB - contention 364 0 1132 195
enq: HW - contention 104 0 1028 1720
提前分配空间
提前分配空间
sqlplus scott/trigger
truncate table t1;
alter table scott.t1 allocate extent(size 1g);
开启insert
time sh run.sh insert
测试结果
16:31:49 SQL> select event,p1,p2,p3,count(*)
16:33:44 2 from v$active_session_history where to_char(sample_time,'yyyy-mm-dd hh24:mi:ss') between '2023-03-22 16:28:31' and '2023-03-22 16:31:49'and
16:33:44 3 event='enq: HW - contention' group by event,p1,p2,p3 order by count(*);
EVENT P1 P2 P3 COUNT(*)
-------------------------- ---------- ---------- ---------- ----------
enq: HW - contention 1213661190 7 29360258 115
Elapsed: 00:00:00.01
16:33:45 SQL>
16:33:49 SQL> select event,round(avg(time_waited)/1000,0) avg_,round(min(time_waited)/1000,0) min_,round(max(time_waited)/1000,0) max_,count(*)
16:33:49 2 from v$active_session_history where to_char(sample_time,'yyyy-mm-dd hh24:mi:ss') between '2023-03-22 16:28:31' and '2023-03-22 16:31:49'and
16:33:49 3 event='enq: HW - contention' group by event order by count(*);
EVENT AVG_ MIN_ MAX_ COUNT(*)
------------------------- --------- ---------- ---------- ----------
enq: HW - contention 1142 0 2507 115
Elapsed: 00:00:00.00
16:33:50 SQL> select event,round(avg(time_waited)/1000,0) avg_,round(min(time_waited)/1000,0) min_,round(max(time_waited)/1000,0) max_,count(*)
16:33:54 2 from v$active_session_history where to_char(sample_time,'yyyy-mm-dd hh24:mi:ss') between '2023-03-22 16:28:31' and '2023-03-22 16:31:49'and
16:33:54 3 event like 'enq%' group by event order by count(*);
EVENT AVG_ MIN_ MAX_ COUNT(*)
------------------------ ---------- ---------- ---------- ----------
enq: FB - contention 166 0 1391 66
enq: HW - contention 1142 0 2507 115
IO满情况
不提前分配空间
不提前分配空间
sqlplus scott/trigger
truncate table t1;
conn / as sysdba
drop user soe cascade;
Swingbench压测初始化数据，使IO增高
cd /home/oracle/swingbench/bin
oewizard -s -create -c /home/oracle/swingbench/wizardconfigs/oewizard.xml -create \
-version 2.6 -cs //192.168.31.242:1521/db11g -dba "sys as sysdba" -dbap oracle -dt thin \
-ts test -u SOE -p soe -allindexes -scale 2 -tc 16 -v -cl
-s是静默，-create表示创建数据，-c是指定配置文件，就是oewizard.xml，-version指定swingbench版本，-cs表示数据库连接串，
-dba表示连接到oracle数据库的用户，-dbap表示连接到oracle数据库的密码，-dt表示连接类型，-ts是指定表空间为test,
-u表示压测的用户，-p表示压测用户的密码，-scale是数据量，1就是1G，0.001就是1Mb数据，-tc 是并发度，
-allindexes是支持所有的索引类型，-v显示详细信息，-cl表示运行在字符模式。
IOSTAT监控IO使用增高后，开启insert
time sh run.sh insert
测试结果
select event,p1,p2,p3,count(*)
from v$active_session_history where to_char(sample_time,'yyyy-mm-dd hh24:mi:ss') between '2023-03-22 16:10:12' and '2023-03-22 16:14:19'and
event='enq: HW - contention' group by event,p1,p2,p3 order by count(*);
EVENT P1 P2 P3 COUNT(*)
---------------------------------- ---------- ---------- ---------- ----------
enq: HW - contention 1213661190 2 12595768 1
enq: HW - contention 1213661190 2 12592856 3
enq: HW - contention 1213661190 2 12600152 4
enq: HW - contention 1213661190 7 29360258 3073
select event,round(avg(time_waited)/1000,0) avg_,round(min(time_waited)/1000,0) min_,round(max(time_waited)/1000,0) max_,count(*)
from v$active_session_history where to_char(sample_time,'yyyy-mm-dd hh24:mi:ss') between '2023-03-22 16:10:12' and '2023-03-22 16:14:19'and
event='enq: HW - contention' group by event order by count(*);
EVENT AVG_ MIN_ MAX_ COUNT(*)
------------------------------- ---------- ---------- ---------- ----------
enq: HW - contention 234 0 3277 3081
select event,round(avg(time_waited)/1000,0) avg_,round(min(time_waited)/1000,0) min_,round(max(time_waited)/1000,0) max_,count(*)
from v$active_session_history where to_char(sample_time,'yyyy-mm-dd hh24:mi:ss') between '2023-03-22 16:10:12' and '2023-03-22 16:14:19'and
event like 'enq%' group by event order by count(*);
EVENT AVG_ MIN_ MAX_ COUNT(*)
--------------------------------- ---------- ---------- ---------- ----------
enq: CF - contention 9 9 9 1
enq: RO - fast object reuse 53 53 53 1
enq: FB - contention 217 0 3667 397
enq: HW - contention 234 0 3277 3081
提前分配空间
提前分配空间
sqlplus scott/trigger
truncate table t1;
alter table scott.t1 allocate extent(size 2g);
conn / as sysdba
drop user soe cascade;
Swingbench压测初始化数据，使IO增高
cd /home/oracle/swingbench/bin
oewizard -s -create -c /home/oracle/swingbench/wizardconfigs/oewizard.xml -create \
-version 2.6 -cs //192.168.31.242:1521/db11g -dba "sys as sysdba" -dbap oracle -dt thin \
-ts test -u SOE -p soe -allindexes -scale 2 -tc 16 -v -cl
IOSTAT监控IO使用增高后，开启insert
16:39:50 SQL> select event,p1,p2,p3,count(*)
16:40:48 2 from v$active_session_history where to_char(sample_time,'yyyy-mm-dd hh24:mi:ss') between '2023-03-22 16:38:21' and '2023-03-22 16:39:50'and
16:40:48 3 event='enq: HW - contention' group by event,p1,p2,p3 order by count(*);
EVENT P1 P2 P3 COUNT(*)
--------------------------------------- ---------- ---------- ---------- ----------
enq: HW - contention 1213661190 2 12593080 4
enq: HW - contention 1213661190 7 29360258 200
Elapsed: 00:00:00.01
16:40:48 SQL>
16:40:48 SQL>
16:40:48 SQL>
16:40:48 SQL> select event,round(avg(time_waited)/1000,0) avg_,round(min(time_waited)/1000,0) min_,round(max(time_waited)/1000,0) max_,count(*)
16:40:48 2 from v$active_session_history where to_char(sample_time,'yyyy-mm-dd hh24:mi:ss') between '2023-03-22 16:38:21' and '2023-03-22 16:39:50'and
16:40:48 3 event='enq: HW - contention' group by event order by count(*);
EVENT AVG_ MIN_ MAX_ COUNT(*)
--------------------------------- ---------- ---------- ---------- ----------
enq: HW - contention 1215 0 2043 204
Elapsed: 00:00:00.01
16:40:48 SQL> select event,round(avg(time_waited)/1000,0) avg_,round(min(time_waited)/1000,0) min_,round(max(time_waited)/1000,0) max_,count(*)
16:40:48 2 from v$active_session_history where to_char(sample_time,'yyyy-mm-dd hh24:mi:ss') between '2023-03-22 16:38:21' and '2023-03-22 16:39:50'and
16:40:48 3 event like 'enq%' group by event order by count(*);
EVENT AVG_ MIN_ MAX_ COUNT(*)
---------------------------------- ---------- ---------- ---------- ----------
enq: CF - contention 34 34 34 1
enq: FB - contention 176 0 2412 30
enq: HW - contention 1215 0 2043 204
测试结论
1、IO占满比IO正常的情况下，相同insert并发下：
enq: HW – contention等待次数增长(3073-1720)/1720=78%
2、不论IO情况如何，提前分配表区段的情况下，enq: HW – contention等待会明显减少。
最后修改时间：2023-03-28 14:41:57
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
