高水位线争用 enq:HW–contention
# HW锁争用是在急速空间扩张时普遍出现的等待现象，有时也会引发严重的性能下降。
# 通常在执行insert操作时，当高水位线以下block不够用时，Oracle将会推进高水位线。
# 更进一步讲，当有多个进程在同时进行insert操作时，比较容易引起高水位线争用，主要表现为enq: HW - contention。
#### 找到事件：'enq: HW - contention' 热点对象：
# 1 查看v$session_wait
select p1, p2, p3 from v$session_wait where event = 'enq: HW - contention';
P1 P2 P3
--------- -- ------------
1213661190 7 140003563
1213661190 7 140003563
# 2 通过P3进行DBMS_UTILITY转换可以获知发生争用的文件和block：
SQL> select dbms_utility.data_block_address_block(140003563) block,dbms_utility.data_block_address_file(140003563) file from dual;
block file
------- ----
1591531 33
# 3 通过file#和block#定位对象：
select owner, segment_type, segment_name from dba_extents where file_id = 33 and 1591531 between block_id and block_id + blocks - 1;
###### 解决方案：减少HW锁争用的方法如下：
###### 方案1 提前分配空间
###### 基本(非分区)表(测试表)
create table cog.test_random(id int,msg clob);
insert into cog.test_random select rownum id,'123' from dual connect by rownum<=500;
-- 查看分配的空间(普通表)
select '&&owner' a,'&&table_name' b from dual;
undefine table_name
with t as (
select a.*,rank() over(partition by segment_name order by extent_id desc) num from dba_extents a
where OWNER=upper('&&owner')
and SEGMENT_NAME in ( select SEGMENT_NAME from DBA_LOBS where OWNER=upper('&&owner') and TABLE_NAME=upper('&&table_name') union select index_name from dba_indexes where TABLE_OWNER=upper('&&owner') and table_name=upper('&&table_name') union select upper('&&table_name') from dual )
) select SEGMENT_NAME,round(sum(bytes)/1024/1024,1) sizeMB from t group by SEGMENT_NAME
/
-- 基础表(索引)空间分配(回收)(基础表 不影响LOB段)
alter table cog.test_random allocate extent;
alter table cog.test_random allocate extent(size 65536);
alter table cog.test_random allocate extent(size 10M);
alter table cog.test_random allocate extent(datafile 5 size 1M);
alter index cog.IND_TEST_RAMDOM_ID allocate extent;
-- 回收 -- 只是回收高水位线以上的空间
alter table cog.test_random deallocate unused;
alter table cog.test_random deallocate unused keep 10M;
-- LOB段分配
alter table cog.test_random modify lob(msg) (allocate extent(size 10M));
-- LOB段回收
alter table cog.test_random modify lob (msg) (deallocate unused);
alter table cog.test_random modify lob (msg) (deallocate unused keep 5M);
###### 提前分配空间
###### 分区(测试)表 分区+LOB
drop table cog.test_hash;
create table cog.test_hash(id int,dt date,msg1 varchar2(4000),msg2 clob )
partition by hash(id) ( partition p01 tablespace users, partition p02, partition p03 tablespace users, partition p04);
insert into cog.test_hash select rownum id,sysdate,'123','123' from dual connect by rownum<=500;
commit;
select count(1) from cog.test_hash;
-- truncate分区
alter table cog.test_hash truncate partition p01;
alter table cog.test_hash truncate partition p01 drop storage;
alter table cog.test_hash drop partition p01; -- hash分区不能删除
undefine table_name
select '&&owner' a,'&&table_name' b from dual;
with seg as (
select upper('&&table_name') seg_name from dual
union select SEGMENT_NAME from DBA_LOBS where OWNER=upper('&&owner') and TABLE_NAME=upper('&&table_name')
union select index_name from dba_indexes where TABLE_OWNER=upper('&&owner') and table_name=upper('&&table_name')
union select lob_name from DBA_LOB_PARTITIONS where TABLE_OWNER=upper('&&owner') and table_name=upper('&&table_name')
union select LOB_INDPART_NAME from DBA_LOB_PARTITIONS where TABLE_OWNER=upper('&&owner') and table_name=upper('&&table_name')
union select index_name from DBA_PART_INDEXES where OWNER=upper('&&owner') and table_name=upper('&&table_name')
union select lob_name from DBA_PART_LOBS where TABLE_OWNER=upper('&&owner') and table_name=upper('&&table_name')
union select lob_index_name from DBA_PART_LOBS where TABLE_OWNER=upper('&&owner') and table_name=upper('&&table_name')
),t as(
select a.*,rank() over(partition by segment_name order by extent_id desc) num from dba_extents a,seg
where a.OWNER=upper('&&owner') and a.SEGMENT_NAME=seg.seg_name
) select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BYTES,BLOCKS,RELATIVE_FNO from t order by 1,2,3;
-- select SEGMENT_NAME,round(sum(bytes)/1024/1024,1) sizeMB from t group by SEGMENT_NAME;
-- 每个基础分区都会扩展1个分片
alter table cog.test_hash allocate extent;
-- 每个基础分区都会扩展10M(8+1+1)
alter table cog.test_hash allocate extent(size 10M);
-- 所有基础分区都会被回收未使用的空间(高水位线以上的空间)
alter table cog.test_hash deallocate unused;
-- 扩展分区表的LOB（每一个分区的LOB都会扩展/回收）
alter table cog.test_hash modify lob(msg2) (allocate extent);
alter table cog.test_hash modify lob(msg2) (allocate extent(size 8M));
alter table cog.test_hash modify lob(msg2) (deallocate unused);
alter table cog.test_hash modify lob(msg2) (deallocate unused keep 1M);
-- 指定分区的LOB将会被扩展/回收
alter table cog.test_hash modify partition p01 lob(msg2) (allocate extent);
alter table cog.test_hash modify partition p01 lob(msg2) (allocate extent(size 8M));
alter table cog.test_hash modify partition p01 lob(msg2) (deallocate unused);
alter table cog.test_hash modify partition p01 lob(msg2) (deallocate unused keep 1M);
-- 为指定分区分配空间（只会扩展基础分区、不影响LOB段）
alter table cog.test_hash modify partition p01 allocate extent;
alter table cog.test_hash modify partition p01 deallocate unused;
###### 提前分配空间
###### 分区(测试)表 分区+子分区+LOB
create table cog.test_range_hash(id int,dt date,msg1 varchar2(4000),msg2 clob )
partition by range(dt) interval(numtoyminterval(1,'year'))
subpartition by hash(id)
subpartition template( subpartition sp01 tablespace users, subpartition sp02, subpartition sp03, subpartition sp04)
(partition p19 values less than (to_date('2020-01-01', 'yyyy-mm-dd')),
partition p20 values less than (to_date('2021-01-01', 'yyyy-mm-dd'))
)
;
insert into cog.test_range_hash select rownum id,sysdate,'123','123' from dual connect by rownum<=500;
insert into cog.test_range_hash select rownum id,sysdate-400,'123','123' from dual connect by rownum<=500;
create index cog.range_hash_dt on cog.test_range_hash(dt) local;
undefine table_name
select '&&owner' a,'&&table_name' b from dual;
with seg as (
select upper('&&table_name') seg_name from dual
union select SEGMENT_NAME from DBA_LOBS where OWNER=upper('&&owner') and TABLE_NAME=upper('&&table_name')
union select index_name from dba_indexes where TABLE_OWNER=upper('&&owner') and table_name=upper('&&table_name')
union select lob_name from DBA_LOB_PARTITIONS where TABLE_OWNER=upper('&&owner') and table_name=upper('&&table_name')
union select LOB_INDPART_NAME from DBA_LOB_PARTITIONS where TABLE_OWNER=upper('&&owner') and table_name=upper('&&table_name')
union select lob_name from DBA_LOB_SUBPARTITIONS where TABLE_OWNER=upper('COG') and table_name=upper('&&table_name')
union select index_name from DBA_PART_INDEXES where OWNER=upper('&&owner') and table_name=upper('&&table_name')
union select lob_name from DBA_PART_LOBS where TABLE_OWNER=upper('&&owner') and table_name=upper('&&table_name')
union select lob_index_name from DBA_PART_LOBS where TABLE_OWNER=upper('&&owner') and table_name=upper('&&table_name')
),t as(
select a.*,rank() over(partition by segment_name order by extent_id desc) num from dba_extents a,seg
where a.OWNER=upper('&&owner') and a.SEGMENT_NAME=seg.seg_name
) select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,TABLESPACE_NAME,EXTENT_ID,FILE_ID,BLOCK_ID,BYTES,BLOCKS,RELATIVE_FNO from t order by 1,2,3;
-- select SEGMENT_NAME,round(sum(bytes)/1024/1024,1) sizeMB from t group by SEGMENT_NAME;
-- 每个基础分区都会扩展1个分片 (混合分区不能指定表扩展 可以回收)
-- 指定分区扩展(不会影响LOB段，指定分区的所有所有子分区都会扩展)
alter table cog.test_range_hash modify partition p19 allocate extent;
-- 指定分区扩展10M(8+1+1)(不会影响LOB段，指定分区的所有子分区都会扩展)
alter table cog.test_range_hash modify partition p19 allocate extent(size 10M);
-- 所有基础分区都会被回收未使用的空间(高水位线以上的空间)
alter table cog.test_range_hash deallocate unused;
alter table cog.test_range_hash modify partition p19 deallocate unused;
-- 指定子分区分配空间(不会影响LOB段)
alter table cog.test_range_hash modify subpartition P19_SP01 allocate extent(size 10M);
alter table cog.test_range_hash modify subpartition P19_SP01 deallocate unused;
alter table cog.test_range_hash modify subpartition P19_SP01 deallocate unused keep 5M;
-- 扩展指定分区的LOB（指定分区的每一个子分区的LOB都会扩展/回收）
alter table cog.test_range_hash modify partition p19 lob(msg2) (allocate extent);
alter table cog.test_range_hash modify partition p19 lob(msg2) (allocate extent(size 10M));
alter table cog.test_range_hash modify partition p19 lob(msg2) (deallocate unused);
alter table cog.test_range_hash modify partition p19 lob(msg2) (deallocate unused keep 1M);
-- 扩展指定子分区的LOB将会被扩展/回收
alter table cog.test_range_hash modify subpartition P19_SP01 lob(msg2) (allocate extent);
alter table cog.test_range_hash modify subpartition P19_SP01 lob(msg2) (allocate extent(size 8M));
alter table cog.test_range_hash modify subpartition P19_SP01 lob(msg2) (deallocate unused);
alter table cog.test_range_hash modify subpartition P19_SP01 lob(msg2) (deallocate unused keep 1M);
###### 解决方案2
# 创建一个大的统一分配的表空间，迁移HW争用的对象到表空间中
-- 一次将会分配更多的空间（超大区-1个有1G）
CREATE TABLESPACE "T09" DATAFILE size 3G EXTENT MANAGEMENT LOCAL uniform size 1G;
-- 迁移到其它表空间，会根据其它表空间的规则分配区(extent)
alter table COG.TEST_RANDOM move lob(msg2) store as(tablespace T09);
###### 解决方案 3 使用分区表(hash分区)方式分散数据 数据水平分布
create table cog.test_hash(id int,dt date,msg1 varchar2(4000),msg2 clob )
partition by hash(id) ( partition part_01 tablespace users, partition part_02, partition part_03 tablespace users, partition part_04)
;
###### 解决方案 4 修改表的空间分配属性，一次多分配一些空间(依赖于表空间的管理模式，空间自动管理，可能不会生效)
alter table cog.test_random STORAGE(NEXT 2M); -- 不影响已有数据
alter table cog.test_random move STORAGE(INITIAL 64K NEXT 1G); -- move 会移动现有数据
alter table cog.test_random move LOB("msg2") STORE AS BASICFILE (STORAGE( initial 1M NEXT 2G));
-- 修改INDEX extent空间分配 LOB index不能手动调整
alter index cog.IND_TEST_RANDOM_ID STORAGE(NEXT 2M);
alter index cog.IND_TEST_RANDOM_ID rebuild STORAGE(INITIAL 1M NEXT 2M);
alter index cog.IND_TEST_RANDOM_ID rebuild online STORAGE(INITIAL 1M NEXT 2M);
alter index cog.IND_TEST_RANDOM_ID rebuild online STORAGE(INITIAL 64K NEXT 1M) compute statistics;
-- 同时修改完成后，索引可能会会受影响报错或无效，需要重建
alter index cog.IND_TEST_RANDOM_ID rebuild;
alter index cog.IND_TEST_RANDOM_ID rebuild online compute statistics;
#####################################################
-- 建表DDL 建索引DDL
select dbms_metadata.get_ddl('TABLE',schema=>upper('&owner'),name=>upper('&table_name')) define from dual;
select dbms_metadata.get_ddl('INDEX',schema=>upper('&&owner'),name=>upper('&index_name')) define from dual;
-- 查看是否有无效的索引
查询物理失效索引
查询失效的全局索引
select owner,index_name,status from dba_indexes where status='UNUSABLE';
select owner,index_name,STATUS from dba_indexes where TABLE_OWNER=upper('&&owner') and table_name=upper('&&table_name');
查询失效的索引分区
select index_owner,index_name,partition_name,status from dba_ind_partitions where status='UNUSABLE';
select index_owner,index_name,partition_name,SUBPARTITION_NAME,status from dba_ind_subpartitions where status='UNUSABLE';
重建失效索引 重建全局索引：
with t as (
select 1 type,owner||'.'||index_name idx,'alter index '||owner||'.'||index_name||' rebuild parallel 2 online compute statistics;' sql_str
from dba_indexes where status='UNUSABLE'
union select 2 type,owner||'.'||index_name idx,'alter index '||owner||'.'||index_name||' parallel 1;' sql_str
from dba_indexes where status='UNUSABLE'
) select sql_str from t order by idx,type;
select 'alter index '||index_owner||'.'||index_name||' rebuild subpartition '||SUBPARTITION_NAME||' online;' sql_str
from dba_ind_subpartitions where status='UNUSABLE';
重建分区索引
alter index NXM.INX_LOG_2 rebuild partition P50;
alter table cog.test_range_hash move subpartition p19_sp01;
alter index COG.RANGE_HASH_DT rebuild subpartition p19_sp01 online;
#####################################################
-- 查看表空间定义的分配方式
select dbms_metadata.get_ddl('TABLESPACE',name=>upper('&tablespace_name')) define from dual;
-- LOGGING ONLINE PERMANENT BLOCKSIZE 8192
-- EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
-- NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
alter table cog.test_random disable row movement;
alter table cog.test_random enable row movement;
# 每一个区(extent)的大小必定为64K 1M 8M 64M 对应块(block)数为8 128 1024 8192个数据块
-- 自动段空间管理 第1-16个区是64K 第16-80个区是1M 第80个以上是8M 第200个块以上才分配64M一个区
-- 修改Table extent空间分配
alter table cog.test_random STORAGE(NEXT 2M); -- 不影响已有数据
alter table cog.test_random move STORAGE(INITIAL 64K NEXT 1G); -- move 会移动现有数据
alter table cog.test_random move LOB("msg2") STORE AS BASICFILE (STORAGE( initial 1M NEXT 2G));
-- 修改INDEX extent空间分配 LOB index不能手动调整
alter index cog.IND_TEST_RANDOM_ID STORAGE(NEXT 2M);
alter index cog.IND_TEST_RANDOM_ID rebuild STORAGE(INITIAL 1M NEXT 2M);
alter index cog.IND_TEST_RANDOM_ID rebuild online STORAGE(INITIAL 1M NEXT 2M);
alter index cog.IND_TEST_RANDOM_ID rebuild online STORAGE(INITIAL 64K NEXT 1M) compute statistics;
-- 同时修改完成后，索引可能会会受影响报错或无效，需要重建
alter index cog.IND_TEST_RANDOM_ID rebuild;
alter index cog.IND_TEST_RANDOM_ID rebuild online compute statistics;
truncate table cog.test_random;
# 插入数据 测试数据区(extent)块分布
alter table cog.test_random nologging;
insert /*+ append */ into cog.test_random nologging
select rownum id, sysdate dt, dbms_random.string('x', 4000) msg1,
to_clob(dbms_random.string('x', 4000))||dbms_random.string('x', 4000)||dbms_random.string('x', 4000)||dbms_random.string('x', 4000) msg2
from dual connect by level <= 200;
commit;
insert /*+ append */ into cog.test_random nologging select * from cog.test_random;
commit;
-- 实际建表语句
createt table cog.test_random(id number primary key,name clob);
insert into cog.test_random(1,'123') from dual;
-- 查询到的DDL建表语句
select dbms_metadata.get_ddl('TABLE',schema=>upper('&owner'),name=>upper('&table_name')) define from dual;
# 默认区扩展是1M 把区扩展参数调大
# 修改分区方式(非业务时间，领导同意之后进行操作，必要时，操作前进行备份)
# 需要加MOVE
alter table cog.test_random move
LOB("msg2") STORE AS BASICFILE (
STORAGE(NEXT 104857600))
alter table cog.test_random disable row movement;
alter table cog.test_random enable row movement;
同时修改完成后，索引会受影响报错：
alter index 索引名 rebuild
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
