数据库高水位

数据库高水位

一、数据库高水位背景

在我们的系统中，如果经常对一个存储对象进行删除和更新，就会在它分配的空间内形成一定的零散空间，这些零散的空间将会影响到 SQL 的访问性能。Oracle 数据库通过跟踪段中的状态来管理空间，于是引进了高水位标记（high water mark）。这样可以区别出哪些数据块是没有格式化和未使用的，也就是说当请求新的空闲块，并且现有空闲列表中的块不能满足要求时，HWM 指向的块将被标记为已使用，然后 HWM 将移动指向下一个未使用过的块，这样以便于进行数据插入和数据块管理。

1、oracle 手动段空间管理（MSSM）：

​ 高水位标记 HWM，一个段分成三部分，header block，used block(row data)，unusedblock，其中 used block 和 unused block 之间的分界线就是高水位标记 HWM，当进行全表扫描的时候，会扫描到 HWM 下的所有数据块，即使 used block 中很多数据被删除了，全表扫描还是以HWM为准。当 insert 时，freelist 中如果没有发现空闲块，就会在 HWM 上找寻未用块，同时 HWM 会向上移动。但是需要注意，HWM 只会向上移动，不会自动收缩，即使 delete 大量数据，导致 HWM下有很多空闲块

2、高水位的特点：

​ 1、HWM 在插入数据时，当现有空间不足而进行空间的扩展时会向上移，但删除数据时不会往下移，这就好比是水库的水位，当涨水时，水位往上移，当水退出后，留下的水位痕迹还能清晰可见。

​ 2、ORACLE 的全表扫描是读取高水位标记(HWM)以下的所有块。当用户发出一个全表扫描时，Oracle 访问目标表里的数据，会从该表所占用的第一个区(Extent)的第一个块(Block)开始扫描，一直扫描到该表的高水位线(HWM)，即使它什么也没有发现。这样不仅延长了全表扫描的时间，而且影响全表扫描的性能

3、 在 delete 删除大量数据时，不仅删除时间较长，而且 HWM 保持原位置不动。如果全表的数据确实不再需要，建议使用 truncate 来代替 delete 操作，因为truncate 默认下会对空间进行释放，改变了表中的数据块位置，使得 HWM 重置到段头。

二、验证，删除、truncate、索引对高水位的影响

1、创建表

CREATE TABLE TEST_HLM (	   
	"FSOURCENAME" VARCHAR2(255) DEFAULT ' ' NOT NULL, 
	"FCOLWIDTH" NUMBER(10,0) DEFAULT 0 NOT NULL   
)  

​

2 、更新表统计信息

  ANALYZE TABLE TEST_HLM ESTIMATE STATISTICS;  

3 、查看表已使用块 空闲块 表长度

SELECT blocks, empty_blocks, num_rows FROM user_tables  WHERE table_name = 'TEST_HLM'; 

后续每次更新表后，查看表的高水位情况都是执行2和3步骤

4 、插入一点数据

 INSERT INTO  TEST_HLM SELECT FSOURCENAME,FCOLWIDTH FROM T_BAS_IMPORTTEMPLATEENTRY WHERE fseq<60    

5 、更新表统计信息，并查看表已使用块 空闲块 表长度

可以发现高水位线位置指向第40（35+5）个BlOCKS的位置

7 查看全表扫描性能

SELECT count(*) FROM TEST_HLM     

8 删除表

DELETE FROM TEST_HLM      

9 再次查看执行计划

10 更新表统计信息 并查看高水位情况

可以看到删除表 高水位情况没有变 执行计划成本也没有变

11 测试再次增加不超过高水位的数据 高水位是否变化

 INSERT INTO  TEST_HLM SELECT FSOURCENAME,FCOLWIDTH FROM T_BAS_IMPORTTEMPLATEENTRY WHERE fseq<40   

12 更新表统计信息 并查看高水位情况 发现高水位没有上升

13 再次增加超过当前水位容量的数据看看高水位的情况

 INSERT INTO  TEST_HLM SELECT FSOURCENAME,FCOLWIDTH FROM T_BAS_IMPORTTEMPLATEENTRY WHERE fseq>40

图忘记截了

14 更新表统计信息 并查看高水位情况 ,发现高水位上升

15 测试 truncate 是否能重置高水位

  truncate TABLE TEST_HLM           

16 更新表统计信息 并查看高水位情况 ,发现高水位重置了

18 查看全表扫描的执行计划 ,发现成本降低了

  SELECT count(*) FROM TEST_HLM

19 查看高水位情况下，命中索引查询的性能

​ 1、先查看5000w表的高水位情况

​ 2、查看未加索引前的执行计划

​ 3、加索引后的执行计划，发现加索引后性能大大提升，和高水位没有关系

总结

1、高水位上升的情况只能是数据库insert时，可用空间不足，如果数据库定期清理数据，水位不会上升，即先删除后增加同样的数据量，对高水位没有影响

2、高水位只对全表扫描性能有影响对命中索引没有影响

3、通过truncate命令能重置高水位