数据库高水位
一、数据库高水位背景
在我们的系统中,如果经常对一个存储对象进行删除和更新,就会在它分配的空间内形成一定的零散空间,这些零散的空间将会影响到 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命令能重置高水位