enq: HW - contention等待事件模拟

原创董宏伟云和恩墨 2023-03-20

596

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
复制