文档课题:ORACLE数据库高水位线(high water mark).
1、相关概念
ORACLE数据库逻辑结构包括:数据库块(block),区(extent),段(segment),表空间(tablespace).高水位线存在于段中,用于标识段中已使用过的数据块与未使用过的数据块二者间交界.扫描表数据时,高水位线以下的所有数据块都必须被扫描.
高水位线存在于段,且位置记录在段头,也就是段的第一个数据块中.因此可以转储段头信息来看高水位线信息.段又分数据段、索引段、临时段、回滚段等.当创建段的时候会分配区,区是由若干个物理连续的数据块组成.区的分配是需要初始化数据块的,默认初始化单位为1M.注意:高水位线并不是初始化的交界片,被初始化过的数据块并不一定被使用过.
2、实验测试
以下通过实验来进一步阐述高水位线.
2.1、创建测试表
SYS@orcl150> create table hwm as select * from dba_segments;

Table created.

SYS@orcl150> begin
2 dbms_stats.gather_table_stats('SYS','HWM');
3 end;
4 /

PL/SQL procedure successfully completed.
2.2、查高水位线信息
SYS@orcl150> select header_file,header_block from dba_segments where segment_name='HWM';

HEADER_FILE HEADER_BLOCK
----------- ------------
1 94648
--使用dump命令转储块信息到trace文件中.
SYS@orcl150> alter system dump datafile 1 block 94648;

System altered.
--查找相应的trace文件.
SYS@orcl150> select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl150/orcl150/trace/orcl150_ora_40548.trc

[oracle@leo-oel150 trace]$ tail -5000f orcl150_ora_40548.trc
……
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 6
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x004172c0 ext#: 1 blk#: 8 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 22
mapblk 0x00000000 offset: 1
……
2.3、删除数据高水位情况
删除3000行数据后确认高水位线是否有变化.
SYS@orcl150> delete from hwm where rownum<3000;

2999 rows deleted.

SYS@orcl150> commit;

Commit complete.

SYS@orcl150> alter system checkpoint;

System altered.

SYS@orcl150> alter system dump datafile 1 block 94648;

System altered.

[oracle@leo-oel150 trace]$ tail -5000f orcl150_ora_40548.trc
……
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 14 #blocks: 111
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x004173ad ext#: 13 blk#: 5 ext size: 8
#blocks in seg. hdr's freelists: 55
#blocks below: 108
mapblk 0x00000000 offset: 13
……
说明:此处与参考网址数据存在差异.
2.4、insert数据高水位情况
Insert 2000行继续观察
SYS@orcl150> insert into hwm select * from dba_segments where rownum<2001;

2000 rows created.

SYS@orcl150> commit;

Commit complete.

SYS@orcl150> alter system checkpoint;

System altered.

SYS@orcl150> alter system dump datafile 1 block 94648;

System altered.

[oracle@leo-oel150 trace]$ tail -5000f orcl150_ora_40548.trc
……
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 14 #blocks: 111
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x004173ad ext#: 13 blk#: 5 ext size: 8
#blocks in seg. hdr's freelists: 19
#blocks below: 108
mapblk 0x00000000 offset: 13
……

说明:高水位线并未变化,表明高水位线以下delete过的数据块是可以被复用的.

2.5、再次insert测试
--此时再insert 1000行.
SYS@orcl150> insert into hwm select * from dba_segments where rownum<1001;

1000 rows created.

SYS@orcl150> commit;

Commit complete.

SYS@orcl150> alter system checkpoint;

System altered.

SYS@orcl150> alter system dump datafile 1 block 94648;

System altered.

[oracle@leo-oel150 trace]$ tail -5000f orcl150_ora_40548.trc
……
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 14 #blocks: 111
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x004173ad ext#: 13 blk#: 5 ext size: 8
#blocks in seg. hdr's freelists: 2
#blocks below: 108
mapblk 0x00000000 offset: 13
……

说明:发现高水位线依旧没有变化,继续insert数据.
SYS@orcl150> insert into hwm select * from dba_segments where rownum<1001;

1000 rows created.

SYS@orcl150> commit;

Commit complete.

SYS@orcl150> alter system checkpoint;

System altered.

SYS@orcl150> alter system dump datafile 1 block 94648;

System altered.

[oracle@leo-oel150 trace]$ tail -5000f orcl150_ora_40548.trc
……

Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 16 #blocks: 127
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x004173c0 ext#: 15 blk#: 8 ext size: 8
#blocks in seg. hdr's freelists: 3
#blocks below: 127
mapblk 0x00000000 offset: 15
……

综上:delete并不能回收表数据,在增删改数据的过程中使得高水位线持续增长,而被删除的数据记录位置也无法100%复用,因此难免会存在碎片.比如一张100万数据的表,将全表数据delete之后,高水位线位置依旧在第100万行处,尽管此时表数据为0行,但全表扫描时仍会扫描所有已使用过的数据块,使数据库效率低下.
3、回收高水位线
oracle也提供如下回收高水位线的方法.
a、表重建, 如CATS(create table as select ...)
b、导出导入(exp/imp,expdp/impdp)
c、truncate (注意:此方法慎用)
d、shrink space,语句alter table table_name shrink space;
e、move table,语句alter table table_name move;
f、DBMS_REDEFINITION表在线重定义