大量数据table_实验：测试对某张表进行大量delete操作HWM线会不会下降？

概述

对一个 table 进行 DML 操作，主要是 insert、update、delete 这三种操作。当一个 table 进行了多次 insert数据时，table的 HWM 会不停地提升。

如果在这期间对这个 table 进行了大量的 delete 操作，这时 table 的 HWM 会不会随着数据量的减少而下降？

当然很多朋友实际上是知道结果的，但是实际有没有下降可能就不太清楚了，下面介绍下HWM概念然后做一个实验来测试下。

---

HWM概念

在一个 segment中，HWM 是使用和未使用空间的分界线。当请求新的空闲块，并且现有空闲列表中的块不能满足要求时，HWM 指向的块将被标记为已使用，然后 HWM 将移动指向下一个未使用过的块。HWM跟ORACLE的段空间管理密切相关。

Oracle 从 9i开始，推出了一种新的 segm ent的空间管理方式，即 ASSM(Auto Segment Space Management)。这种segment空间管理方式和以前的FLM(Freelist Management)是不一样的。

在 FLM 模式下，对于一个 segment的HWM 下的所有 block 空间的使用，是通过 Freelist来管理的，Freelist位于 segment的第一个 extent中。一个 block 何时应该位于Freelist之上，取决于 PCTUSED 和 PCTFREE 这样两个参数。基于 Freelist管理模式和位于 segment header的情况，如果对一个 segment进行高并发的频繁的 DML 操作，就不可避免地会出现 header争用的情况，虽然可以采用增加 Freelists或 Freelist Group 的方式来缓解这种状况。

ASSM 这样一种全新的 segm ent空间管理的方式( Bitmap Managed Segments)，Freelist被位图所取代，使用位图来管理 block 的空间使用状况，并且这些位图块分散在segment中。ASSM 管理的 segment会略掉任何为 PCTUSED、NEXT 和 Freelists所指定的值。

概念的东西这里不多讲，就简单过下~下面看下实验过程。

---

1、先引入一个 procedure

create or replace procedure show_space(p_segname in varchar2, p_owner in varchar2 default user, p_type in varchar2 default 'TABLE', p_partition in varchar2 default NULL) as l_total_blocks number; l_total_bytes number; l_unused_blocks number; l_unused_bytes number; l_LastUsedExtFileId number; l_LastUsedExtBlockId number; l_last_used_block number; procedure p(p_label in varchar2, p_num in number) is begin dbms_output.put_line(rpad(p_label, 40, '.') || p_num); end;begin dbms_space.unused_space(segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, partition_name => p_partition, total_blocks => l_total_blocks, total_bytes => l_total_bytes, unused_blocks => l_unused_blocks, unused_bytes => l_unused_bytes, last_used_extent_file_id => l_LastUsedExtFileId, last_used_extent_block_id => l_LastUsedExtBlockId, last_used_block => l_last_used_block); p('Total Blocks', l_total_blocks); p('Total Bytes', l_total_bytes); p('Unused Blocks', l_unused_blocks); p('Unused Bytes', l_unused_bytes); p('Last Used Ext FileId', l_LastUsedExtFileId); p('Last Used Ext BlockId', l_LastUsedExtBlockId); p('Last Used Block', l_last_used_block);end;/

通过这个 procedure 显示的结果，可以得到一个 segment的 HWM 的位置。在 SQL*Plus中，要看到这个 procedure 显示的结果，就需要设置 set serveroutput on。

这里，HWM = total_blocks- Unused Blocks +1。

2、使用系统视图 all_objects 来创建测试 table MY_OBJECTS，然后 insert 84737 行数据

conn nwpp/nwpp;create table MY_OBJECTS as select * from all_objects;select count(*) from MY_OBJECTS;SQL> select count(*) from MY_OBJECTS; COUNT(*)---------- 84737set serveroutput onexec show_space(p_segname=>'MY_OBJECTS',p_owner =>'NWPP',p_type => 'TABLE'); SQL> exec show_space(p_segname=>'MY_OBJECTS',p_owner =>'NWPP',p_type => 'TABLE'); Total Blocks............................1240Total Bytes.............................10158080Unused Blocks...........................1Unused Bytes............................8192Last Used Ext FileId....................9Last Used Ext BlockId...................1273Last Used Block.........................4PL/SQL procedure successfully completed. 

使用 show _space 来计算 table MY_OBJECTS的HWM ，这里 HWM =1240-1+1=1240；

3、现在对 table MY_OBJECTS 进行 delete 操作，删除前 60000 行数据

delete from MY_OBJECTS where rownum <60000;

delete from MY_OBJECTS where rownum <60000;commit;SQL> exec show_space(p_segname=>'MY_OBJECTS',p_owner =>'NWPP',p_type => 'TABLE'); Total Blocks............................1240Total Bytes.............................10158080Unused Blocks...........................1Unused Bytes............................8192Last Used Ext FileId....................9Last Used Ext BlockId...................1273Last Used Block.........................4PL/SQL procedure successfully completed.

观察 HWM 的结果，可以看到，这里 HWM =1240 -1 + 1 = 1240。

4、测试结果

HWM 的位置并没有发生变化。这说明对 table MY_OBJECTS 删除了60000行数据后，并不会改变 HWM 的位置。也就是对某个 table 进行了大量的 delete 操作，这时 table 的 HWM 并不会随着数据量的减少而下降。

---

总结：

前面已经说了delete操作后HWM线没有下降，这里假设是全表扫描，也就是删数据前后系统每次都需要去扫描HWM下所有block，访问的block 数量越多，代表需要消耗的资源越多。那么，当一个 table 在进行了大量的 delete 操作后，我们需要去考虑采用一些方法来降低该表的 HWM ，以减小 table full scan 时需要访问的 block 数量。

篇幅有限，后面再分享下什么时候去降低HWM线和怎么去降低HWM。