高水位（High Water Mark）的概念及高水位问题的解决

1.??? 背景

随着表中数据的不断增长，表的高水位也被不断的推高。当高水位达到一定程度之后，会对该表上的SQL查询效率产生负面影响，因此需要采取有效措施降低高水位。有时候即便是表中数据很少，一条全表扫描的sql仍然很慢，有时候表的数据很少，但是仍然占用很多空间，这些都是高水位在作怪。

?

2.??? 准备知识

Oracle 的逻辑存储结构可简单表示为：表空间——>段——>区——>块。

2.1.? 块（block）

块（block）是oracle粒度最小的存储单位,现在标准的块大小是8K，由参数DB_BLOCK_SIZE指定。ORACLE每一次I/O操作也是按块来操作的,也就是说当ORACLE从数据文件读数据时,是读取多少个块,而不是多少行。

SQL> show parameter db_block_size

NAME???????????????? TYPE??????? VALUE

------------------ ----------- -------

db_block_size?????? integer???? 8192

2.2.? 区（extent）

区（extent）是由一系列相邻的块而组成,这也是ORACLE空间分配的基本单位,举个例子来说,当我们创建一个表A时,首先ORACLE会分配一区的空间给这个表,随着不断的INSERT数据到A,原来的这个区容不下插入的数据时,ORACLE是以区为单位进行扩展的,也就是说再分配多少个区给A,而不是多少个块。一个extent包含多少个block是由创建表空间时指定的，类似：extent management local uniform size 1m。默认情况下一个extent对应8个block，即64k。

2.3.? 段（segment）

段（segment）是由一系列的区所组成,一般来说,当创建一个对象时(表,索引),就会分配一个段给这个对象.所以从某种意义上来说,段就是某种特定的数据。

2.4.? 表空间（tablespace）

表空间（tablespace）包含段,区及块。表空间的数据物理上储存在其所在的数据文件中.一个数据库至少要有一个表空间。

?

3.??? 高水位（High Water Mark）的概念

所有的oracle段都有一个在段内容纳数据的上限，我们把这个上限称为"High Water Mark"或HWM。这个HWM是一个标记，用来说明已经有多少没有使用的数据块分配给这个segment。HWM通常增长的幅度为一次5个数据块，原则上HWM只会增大，不会缩小，即使将表中的数据全部删除，HWM还是为原值，由于这个特点，使 HWM很象一个水库的历史最高水位，这也就是HWM的原始含义，当然不能说一个水库没水了，就说该水库的历史最高水位为0。

实际环境中随着我们表中数据的不断增长，表的高水位也被不断的推高。当高水位达到一定程度之后，会对该表上的SQL查询效率产生负面影响，因此需要采取有效措施降低高水位。

图 1：分配给该表的块。用灰色正方形表示行。

图 2：行后面的块已经删除了；HWM 仍保持不变。

（1）当用户发出一个全表扫描时，Oracle 始终必须从段一直扫描到 HWM，即使它什么也没有发现。该任务延长了全表扫描的时间，即降低了sql的性能。

（2）当用直接路径插入行时 — 例如，通过直接加载插入（用 APPEND 提示插入）或通过 SQL*Loader 直接路径 — 数据块直接置于 HWM 之上，即它下面的空间就浪费了。

4.??? 解决HWM的问题

4.1.? 解决HWM带来的性能问题

首先我们知道truncate table 会重置HWM，但是truncate会删除所有数据，如果如上面图2所示，表中有大量的碎片该怎么处理呢，我们来模拟这个例子：

SQL> create tablespace test datafile '/paic/g4cs2021/stg/sx/oradata/ls17xa/test01.dbf' size 500m autoextend off extent management local uniform size 1m segment space management auto ;

Tablespace created

SQL> create table testhw(name varchar2(100),id number ) tablespace test;

Table created

SQL> select segment_name,header_file,header_block,blocks,bytes,extents,min_extents,max_extents,segment_type from dba_segments where segment_name='TESTHW';

SEGMENT_NA HEADER_FILE HEADER_BLOCK???? BLOCKS????? BYTES??? EXTENTS MIN_EXTENTS MAX_EXTENTS SEGMENT_TYPE

---------- ----------- ------------ ---------- ---------- ---------- ----------- ----------- -----------

TESTHW????????????? 96?????????? 12??????? 128??? ????? 1048576????????? 1?????????? 1? ??? 2147483645 TABLE

我们建一个空表，此时虽然没有数据，但是表占1*extent=128*blocks=128*8192=1048576=1M的空间。

怎么知道一个表的HWM呢？

(1) 首先对表进行分析：ANALYZE TABLE <tablename> ESTIMATE/COMPUTE STATISTICS;

(2) SELECT table_name, num_rows, blocks, empty_blocks FROM user_tables WHERE table_name = ‘&tablename’;

SQL> ANALYZE TABLE TESTHW COMPUTE STATISTICS;

Table analyzed

SQL> SELECT table_name, num_rows, blocks, empty_blocks FROM user_tables WHERE table_name = 'TESTHW';

TABLE_NAME?????????????????????? NUM_ROWS???? BLOCKS EMPTY_BLOCKS

------------------------------ ---------- ---------- ------------

TESTHW????????????????????????????????? 0????????? 0????????? 128

BLOCKS 列代表该表中曾经使用过得数据库块的数目，即水线。EMPTY_BLOCKS 代表分配给该表，但是在水线以上的数据库块，即从来没有使用的数据块。

使用下面语句插入批量数据，此时的HWM是2140 blocks，如图1所示：

下面我就删除一些数据以模拟出一些碎片，此时HWM仍然是2140 blocks ，但是HWM下有很多空块，如图2所示。

declare

? i number := 0;

begin

? for i in 1 .. 1000 loop

??? if (mod(i, 3) = 0 or mod(i, 5) = 0) then

????? delete from testhw where id = i;

????? commit;

??? end if;

? end loop;

end;

/

SQL> ANALYZE TABLE testhw COMPUTE STATISTICS;

Table analyzed

SQL> SELECT table_name, num_rows, blocks, empty_blocks,avg_row_len FROM user_tables u WHERE table_name = 'TESTHW';

TABLE_NAME?????????????????????? NUM_ROWS???? BLOCKS EMPTY_BLOCKS AVG_ROW_LEN

------------------------------ ---------- ---------- ------------ -----------

TESTHW???????????????????????????? 266332?????? 2140?????????? 36????????? 28

SQL> select segment_name,header_file,header_block,blocks,bytes,extents,min_extents,max_extents,segment_type from dba_segments where segment_name='TESTHW';

SEGMENT_NA HEADER_FILE HEADER_BLOCK???? BLOCKS????? BYTES??? EXTENTS MIN_EXTENTS MAX_EXTENTS SEGMENT_TYPE

---------- ----------- ------------ ---------- ---------- ---------- ----------- ----------- ------------

TESTHW????????????? 96?????????? 12?????? 2176?? 17825792???????? 17?????????? 1? 2147483645 TABLE

下面利用oracle 10g的dbms_space包来检查碎片的详细信息，当然也可以用dump文件的方法来查看碎片情况，这里不做介绍，show_space包的代码在文章最后会给出。

SQL> exec show_space('testhw','auto','t','y');

Total Blocks............................2176

Total Bytes.............................17825792

Unused Blocks...........................0

Unused Bytes............................0

Last Used Ext FileId....................96

Last Used Ext BlockId...................2057

Last Used Block.........................128

*************************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free space bytes..............0

25% -- 50% free space blocks............204

25% -- 50% free space bytes.............1671168

50% -- 75% free space blocks............173

50% -- 75% free space bytes.............1417216

75% -- 100% free space blocks...........804

75% -- 100% free space bytes............6586368

Unused Blocks...........................62

Unused Bytes............................507904

Total Blocks............................897

Total bytes.............................7348224

PL/SQL procedure successfully completed

?

SQL> select 204+173+804+62+897 Total from dual;

?

???? TOTAL

----------

????? 2140

说明：结果分两个部分，第一部分是总体情况，第二部分是实际使用情况，第二部分所有block之和等于水位线值。结果中第一行Total Blocks，是表（segment）testhw已分配的总的block数；倒数第二行的Total Blocks表testhw实际100%使用的block数。

下面的例子是我们经常遇到的，一个全表扫描的sql，虽然表中很多空块，但是sql执行起来仍然很慢，如下testhw表中实际使用了897个块，但是仍然2042次的物理读，即扫描HWM2140以下所有块。

SQL> set autotrace TRACE STAT

SQL> select * from dbmgr.testhw where id=998;

998 rows selected.

Statistics

----------------------

????????? 1? recursive calls

????????? 0? db block gets

?????? 2153? consistent gets

?????? 2042? physical reads

????????? 0? redo size

????? 17703? bytes sent via SQL*Net to client

?????? 1218? bytes received via SQL*Net from client

???????? 68? SQL*Net roundtrips to/from client

????????? 0? sorts (memory)

????????? 0? sorts (disk)

??????? 998? rows processed

???? SQL> set autotrace off

Oracle 10g提供了一个清理碎片的方法：alter table table_name shrink space，该命令将重组表中现有行，在此之前要图 3：在收缩后，把空闲块返回给数据库。

如下所示HWM已降低到1031，表中没有碎片，全表扫描读取的数据块随之减少。如果表上有索引还可以一起压缩索引：alter table bookings shrink space cascade;

SQL> ANALYZE TABLE testhw COMPUTE STATISTICS;

Table analyzed

SQL> SELECT table_name, num_rows, blocks, empty_blocks,avg_row_len FROM user_tables u WHERE table_name = 'TESTHW';

TABLE_NAME?????????????????????? NUM_ROWS???? BLOCKS EMPTY_BLOCKS AVG_ROW_LEN

------------------------------ ---------- ---------- ------------ -----------

TESTHW???????????????????????????? 266332?????? 1033????????? 119????????? 28

SQL> select segment_name,header_file,header_block,blocks,bytes,extents,min_extents,max_extents,segment_type from dba_segments where segment_name='TESTHW';

SEGMENT_NA HEADER_FILE HEADER_BLOCK???? BLOCKS????? BYTES??? EXTENTS MIN_EXTENTS MAX_EXTENTS SEGMENT_TYPE

---------- ----------- ------------ ---------- ---------- ---------- ----------- ----------- -------------

TESTHW????????????? 96?????????? 12?????? 1152??? 9437184????????? 9?????????? 1? 2147483645 TABLE

SQL> alter table testhw enable row movement;

Table altered.

SQL> alter table testhw shrink space;

Table altered.

SQL> exec show_space('testhw','auto','t','y');

Total Blocks............................1152

Total Bytes.............................9437184

Unused Blocks...........................101

Unused Bytes............................827392

Last Used Ext FileId....................96

Last Used Ext BlockId...................1033

Last Used Block.........................27

?*************************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free space bytes..............0

25% -- 50% free space blocks............0

25% -- 50% free space bytes.............0

50% -- 75% free space blocks............0

50% -- 75% free space bytes.............0

75% -- 100% free space blocks...........2

75% -- 100% free space bytes............16384

Unused Blocks...........................0

Unused Bytes............................0

Total Blocks............................1029

Total bytes.............................8429568

PL/SQL procedure successfully completed

SQL> set autotrace TRACE STAT

SQL> select * from dbmgr.testhw where id=998;

998 rows selected.

Statistics

--------------------------------------------------

??????? 164? recursive calls

????????? 0? db block gets

?????? 1120? consistent gets

?????? 1033? physical reads

????????? 0? redo size

????? 17703? bytes sent via SQL*Net to client

?????? 1218? bytes received via SQL*Net from client

???????? 68? SQL*Net roundtrips to/from client

????????? 4? sorts (memory)

????????? 0? sorts (disk)

??????? 998? rows processed

联机 shrink 命令是一个用于回收浪费的空间和重置 HWM 的强大的特性。我把后者（重置 HWM）看作该命令最有用的结果，因为它改进了全表扫描的性能。到此我们可以说已经解决了HWM带来的性能问题。其实shrink还可以只收回空间不重置HWM，即：alter table table_name shrink space compact，此时如图4所示

图 4：重组行后的表中的块单不重置HWM。

?

4.2.? 解决高水位带来的空间问题

表空间的高水位，由前面的表的高水位概念不难理解表空间的高水位即是所有表空间上段（表和索引如果有索引的话）的HWM之和。考虑下面的情况，一个表空间中有很多表，有些表中有很多碎片，有些空表，我们想回收表空间上的空闲空间或者说我们空间紧张要收回这部分空间，如何处理？

接着上面例子，首先我们的思路是，接着创建一个空表testhw2，然后对表testhw再插入些数据，因为表testhw已经没有空闲的空间此时表数据会插入到testhw2后面，再创建一个空表testhw3，此时可以肯定testhw中会部分数据在testhw2和testhw3之间，就是说表空间中有些表的数据分布很散而且有碎片，有些是空表。

SQL> create table testhw2 tablespace test as select * from testhw where 1=2;

Table created

SQL> ANALYZE TABLE testhw2 COMPUTE STATISTICS;

Table analyzed

SQL> SELECT table_name, num_rows, blocks, empty_blocks,avg_row_len FROM user_tables u WHERE table_name like 'TESTHW%';

TABLE_NAME?????????????????????? NUM_ROWS???? BLOCKS EMPTY_BLOCKS AVG_ROW_LEN

------------------------------ ---------- ---------- ------------ -----------

TESTHW???????????????????????????? 266332?????? 1033????????? 119????????? 28

TESTHW2???????????????????????????????? 0????????? 0????????? 128?????????? 0

SQL> select segment_name,header_file,header_block,blocks,bytes,extents,min_extents,max_extents,segment_type from dba_segments where segment_name like 'TESTHW%';

SEGMENT_NA HEADER_FILE HEADER_BLOCK???? BLOCKS????? BYTES??? EXTENTS MIN_EXTENTS MAX_EXTENTS SEGMENT_TYPE

---------- ----------- ------------ ---------- ---------- ---------- ----------- ----------- ------------

TESTHW????????????? 96?????????? 12?????? 1152??? 9437184????????? 9 ??????????1? 2147483645 TABLE

TESTHW2???????????? 96???????? 1164??????? 128??? 1048576????????? 1?????????? 1? 2147483645 TABLE????????

这里testhw2的header_block=testhw的header_block+testhw的blocks，即：12+1152=1164。

SQL> declare

? ??? i number := 0;

? ??? j number := 0;

? ?? begin

? ???? for i in 1 .. 300 loop

? ?????? j := 0;

? ?????? for j in 1 .. i loop

? ???????? insert into testhw values (i || 'records inserted!', i+1000);

? ?????? end loop;

?? ????? commit;

?? ? end loop;

?? ? end;

?? ? /

PL/SQL procedure successfully completed

SQL> ANALYZE TABLE testhw COMPUTE STATISTICS;

Table analyzed

SQL> ANALYZE TABLE testhw2 COMPUTE STATISTICS;

Table analyzed

SQL> SELECT table_name, num_rows, blocks, empty_blocks,avg_row_len FROM user_tables u WHERE table_name like 'TESTHW%' ;

TABLE_NAME?????????????????????? NUM_ROWS???? BLOCKS EMPTY_BLOCKS AVG_ROW_LEN

------------------------------ ---------- ---------- ------------ -----------

TESTHW???????????????????????????? 311482?????? 1258?????????? 22????????? 28

TESTHW2??????????????????????????????? ?0???????? ?0????????? 128?????????? 0

SQL> select segment_name,header_file,header_block,blocks,bytes,extents,min_extents,max_extents,segment_type from dba_segments where segment_name like 'TESTHW%' ;

SEGMENT_NA HEADER_FILE HEADER_BLOCK???? BLOCKS????? BYTES??? EXTENTS MIN_EXTENTS MAX_EXTENTS SEGMENT_TYPE

---------- ----------- ------------ ---------- ---------- ---------- ----------- ----------- ------------

TESTHW????????????? 96?????????? 12?????? 1280?? 10485760???????? 10?????????? 1? 2147483645 TABLE

TESTHW2???????????? 96???????? 1164??????? 128??? 1048576 ?????????1?????????? 1? 2147483645 TABLE

这里1164没有改变，但是testhw分配的blocks变成1280已经不是原来的1152了，即1164<>12+1280，也就是说testhw的一部分数据在testhw2之后。我们知道testhw原来有1152个blocks，现在是1280，即多了1280-1152=128 blocks。接着创建testhw3表，这样testhw的数据就有一部分在testhw2和testhw3之间了。然后再把testhw这些数据删除掉，我们试着要能把这部分空间收回来。

TABLE_NAME?????????????????????? NUM_ROWS???? BLOCKS EMPTY_BLOCKS AVG_ROW_LEN

------------------------------ ---------- ---------- ------------ -----------

TESTHW???????????????????????????? 311482?????? 1258?????????? 22????????? 28

TESTHW2???????????????????????????????? 0????????? 0????????? 128?????????? 0

TESTHW3???????????????????????????????? 0????????? 0????????? 128?????????? 0

?

SEGMENT_NA HEADER_FILE HEADER_BLOCK???? BLOCKS????? BYTES??? EXTENTS MIN_EXTENTS MAX_EXTENTS SEGMENT_TYPE

---------- ----------- ------------ ---------- ---------- ---------- ----------- ----------- ------------------

TESTHW????????????? 96?????????? 12?????? 1280?? 10485760???????? 10?????????? 1? 2147483645 TABLE

TESTHW2???????????? 96???????? 1164??????? 128??? 1048576????????? 1?????????? 1? 2147483645 TABLE

TESTHW3???????????? 96???????? 1420??????? 128??? 1048576????????? 1?????????? 1? 2147483645 TABLE

????????1420=1164+128+128?第一个128 blocks是分配给testhw2的，第二个128 blocks是上面我们已经算出来，是testhw 表中我们后来插入的id大于1000的部分。此时表空间的HWM是1548。删除id大于1000的数据，然后使用上面的shrink命令收回空间。

SQL> delete from testhw where id > 1000;

45150 rows deleted

SQL> commit;

Commit complete

SQL> alter table testhw shrink space;

Table altered

SQL> ANALYZE TABLE testhw COMPUTE STATISTICS;

Table analyzed

SQL> ANALYZE TABLE testhw2 COMPUTE STATISTICS;

Table analyzed

SQL> ANALYZE TABLE testhw3 COMPUTE STATISTICS;

Table analyzed

SQL> SELECT table_name, num_rows, blocks, empty_blocks,avg_row_len FROM user_tables u WHERE table_name like 'TESTHW%' ;

TABLE_NAME?????????????????????? NUM_ROWS???? BLOCKS EMPTY_BLOCKS AVG_ROW_LEN

------------------------------ ---------- ---------- ------------ -----------

TESTHW???????????????????????????? 266332?? ????1030????????? 122????????? 28

TESTHW2???????????????????????????????? 0????????? 0????????? 128?????????? 0

TESTHW3???????????????????????????????? 0????????? 0????????? 128?????????? 0

SQL> select segment_name,header_file,header_block,blocks,bytes,extents,min_extents,max_extents,segment_type from dba_segments where segment_name like 'TESTHW%';

SEGMENT_NA HEADER_FILE HEADER_BLOCK???? BLOCKS????? BYTES??? EXTENTS MIN_EXTENTS MAX_EXTENTS SEGMENT_TYPE

---------- ----------- ------------ ---------- ---------- ---------- ----------- ----------- ------------------

TESTHW????????????? 96?????????? 12?????? 1152??? 9437184????????? 9?????????? 1? 2147483645 TABLE

TESTHW2???????????? 96???????? 1164??????? 128??? 1048576????????? 1?????????? 1? 2147483645 TABLE

TESTHW3???????????? 96???????? 1420??????? 128??? 1048576????????? 1?????????? 1? 2147483645 TABLE

SQL> exec show_space('testhw','auto','t','y');

Total Blocks............................1152

Total Bytes.............................9437184

Unused Blocks...........................102

Unused Bytes............................835584

Last Used Ext FileId....................96

Last Used Ext BlockId...................1033

Last Used Block.........................26

?*************************************************

The segment is analyzed

0% -- 25% free space blocks.............0

0% -- 25% free space bytes..............0

25% -- 50% free space blocks............1

25% -- 50% free space bytes.............8192

50% -- 75% free space blocks............0

50% -- 75% free space bytes.............0

75% -- 100% free space blocks...........0

75% -- 100% free space bytes............0

Unused Blocks...........................0

Unused Bytes............................0

Total Blocks............................1029

Total bytes.............................8429568

PL/SQL procedure successfully completed

虽然表testhw中已经没有碎片，但是testhw3的header_block并不会因此而向前移动，表空间的空闲空间仍然无法收回！实际上即便是truncate也无法降低表空间的HWM。

SQL> truncate table testhw;

Table truncated

SQL> truncate table testhw2;

Table truncated

SQL> truncate table testhw3;

Table truncated

SQL> select segment_name,header_file,header_block,blocks,bytes,extents,min_extents,max_extents,segment_type from dba_segments where segment_name like 'TESTHW%' ;

SEGMENT_NA HEADER_FILE HEADER_BLOCK???? BLOCKS????? BYTES??? EXTENTS MIN_EXTENTS MAX_EXTENTS SEGMENT_TYPE

---------- ----------- ------------ ---------- ---------- ---------- ----------- ----------- ------------------

TESTHW????????????? 96?????????? 12??????? 128??? 1048576????? ????1?????????? 1? 2147483645 TABLE

TESTHW2????????????96???????? 1164??????? 128??? 1048576????????? 1?????????? 1? 2147483645 TABLE

TESTHW3????????????96???????? 1420??????? 128??? 1048576????????? 1?????????? 1? 2147483645 TABLE

那么该如何收回空闲的空间呢，答案是move table到其它表空间然后再move回来，而且9i数据库也支持。如果有索引还需要重建索引，如下所示表空间一共使用了3 extent+12blocks，所以表空间可以resize到4M。如果你有很多表可以使用下面语句生成脚本：select 'alter table '||table_name||' move tablespace users;' from dba_tables where tablespace_name='TEST';

SQL> alter table testhw move tablespace users;

Table altered

SQL> alter table testhw2 move tablespace users;

Table altered

SQL> alter table testhw3 move tablespace users;

Table altered

SQL> alter table testhw3 move tablespace test;

Table altered

SQL> alter table testhw2 move tablespace test;

Table altered

SQL> alter table testhw move tablespace test;

Table altered

SQL> alter table testhw2 move tablespace test;

Table altered

SQL> alter table testhw3 move tablespace test;

Table altered

SQL> select segment_name,header_file,header_block,blocks,bytes,extents,min_extents,max_extents,segment_type from dba_segments where segment_name like 'TESTHW%' order by 1 ;

SEGMENT_NA HEADER_FILE HEADER_BLOCK???? BLOCKS????? BYTES??? EXTENTS MIN_EXTENTS MAX_EXTENTS SEGMENT_TYPE

---------- ----------- ------------ ---------- ---------- ---------- ----------- ----------- ------------------

TESTHW????????????? 96?????????? 12??????? 128??? 1048576????????? 1?????????? 1? 2147483645 TABLE

TESTHW2????????????96????????? 140??????? 128??? 1048576????????? 1?????????? 1? 2147483645 TABLE

TESTHW3????????????96????????? 268??????? 128??? 1048576????????? 1?????????? 1? 2147483645 TABLE

SQL> alter database datafile 96 resize 4M;

Database altered

至此有关高水位的所有问题都已圆满解决，希望你读了本文之后不再为高水位以及表的碎片的问题而烦恼。但是如果数据库是Oracle9i 及其以前的版本的话，上面的方法不再适用，可以通过。1、exp表的数据，删除表，重建表，imp数据来回收空间；2、通过使用 ALTER TABLE MOVE 命令把表移动到一个不同的表空间；3、使用联机表重组特性，但是这需要至少双倍的现有表空间。