高水位（High Water Mark）的概念及高水位问题的解决
1、 准备知识-oracle的逻辑存储结构：表空间——>段——>区——>块
块（block）:是粒度最小的存储单位,现在标准的块大小是8K，有参数DB_BLOCK_SIZE指定。ORACLE每一次I/O操作也是按块来操作的,也就是说当ORACLE从数据文件读数据时,是读取多少个块,而不是多少行。
| SQL> show parameter db_block_size |
区（extent）：由一系列相邻的块而组成,这也是ORACLE空间分配的基本单位,举个例子来说,当我们创建一个表A时,首先ORACLE会分配一区的空间给这个表,随着不断的INSERT数据到A,原来的这个区容不下插入的数据时,ORACLE是以区为单位进行扩展的,也就是说再分配多少个区给A,而不是多少个块。一个extent包含多少个block是由创建表空间时指定的，类似：extent management local uniform. size 1m。默认情况下一个extent对应8个block，即64k。
段:是由一系列的区所组成,一般来说,当创建一个对象时(表,索引),就会分配一个段给这个对象.所以从某种意义上来说,段就是某种特定的数据。
表空间:包含段,区及块.表空间的数据物理上储存在其所在的数据文件中.一个数据库至少要有一个表空间。
2、 高水位（High Water Mark）的概念：
所有的oracle段都有一个在段内容纳数据的上限，我们把这个上限称为"High Water Mark"或HWM。这个HWM是一个标记，用来说明已经有多少没有使用的数据块分配给这个segment。HWM通常增长的幅度为一次5个数据块，原则上HWM只会增大，不会缩小，即使将表中的数据全部删除，HWM还是为原值，由于这个特点，使 HWM很象一个水库的历史最高水位，这也就是HWM的原始含义，当然不能说一个水库没水了，就说该水库的历史最高水位为0。
实际环境中随着我们表中数据的不断增长，表的高水位也被不断的推高。当高水位达到一定程度之后，会对该表上的SQL查询效率产生负面影响，因此需要采取有效措施降低高水位。
考虑让我们有一个段，如一张表，其中填满了块，如图 1 所示。在正常操作过程中，删除了一些行，如图 2 所示。现有就有了许多浪费的空间：(1) 在表的上一个末端和现有的块之间，以及 (2) 在块内部，其中还有一些没有删除的行。
图 1：分配给该表的块。用灰色正方形表示行。
图 2：行后面的块已经删除了；HWM 仍保持不变。
Oracle 不会释放空间以供其他对象使用，有一条简单的理由：由于空间是为新插入的行保留的，并且要适应现有行的增长。被占用的最高空间称为最高使用标记 (HWM)，如图 2 所示。但是，这种方法有两个主要的问题：
（1）当用户发出一个全表扫描时，Oracle 始终必须从段一直扫描到 HWM，即使它什么也没有发现。该任务延长了全表扫描的时间，即降低了sql的性能。
（2）当用直接路径插入行时 — 例如，通过直接加载插入（用 APPEND 提示插入）或通过 SQL*Loader 直接路径 — 数据块直接置于 HWM 之上，即它下面的空间就浪费了。
3、 解决HWM的问题：
3.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 ; SQL> create table testhw(name varchar2(100),id number ) tablespace test; SQL> select segment_name,header_file,header_block,blocks,bytes,extents,min_extents,max_extents,segment_type from dba_segments where segment_name='TESTHW'; |
我们建一个空表，此时虽然没有数据，但是表占1*extent=128*blocks=128*8192=1048576=1M的空间。
怎么知道一个表的HWM呢？
(1) 首先对表进行分析：ANALYZE TABLE 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; SQL> SELECT table_name, num_rows, blocks, empty_blocks FROM user_tables WHERE table_name = 'TESTHW'; |
BLOCKS 列代表该表中曾经使用过得数据库块的数目，即水线。EMPTY_BLOCKS 代表分配给该表，但是在水线以上的数据库块，即从来没有使用的数据块。
使用下面语句插入批量数据，此时的HWM是2140 blocks，如图1所示：
| declare SQL> SELECT table_name, num_rows, blocks, empty_blocks,avg_row_len FROM user_tables u WHERE table_name = 'TESTHW'; SQL> select segment_name,header_file,header_block,blocks,bytes,extents,min_extents,max_extents,segment_type from dba_segments where segment_name='TESTHW'; |
下面我就删除一些数据以模拟出一些碎片，此时HWM仍然是2140 blocks ，但是HWM下有很多空块，如图2所示。
| declare SQL> ANALYZE TABLE testhw COMPUTE STATISTICS; SQL> SELECT table_name, num_rows, blocks, empty_blocks,avg_row_len FROM user_tables u WHERE table_name = 'TESTHW'; SQL> select segment_name,header_file,header_block,blocks,bytes,extents,min_extents,max_extents,segment_type from dba_segments where segment_name='TESTHW'; |
下面利用oracle 10g的dbms_space包来检查碎片的详细信息，当然也可以用dump文件的方法来查看碎片情况，这里不做介绍，show_space包的代码在文章最后会给出。
| SQL> exec show_space('testhw','auto','t','y'); SQL> select 204+173+804+62+897 Total from dual; |
说明：结果分两个部分，第一部分是总体情况，第二部分是实际使用情况，第二部分所有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，该命令将重组表中现有行，在此之前要确保在该表上禁用所有基于行 id 的触发器，这是因为行将会移动（和行迁移有些不同所以姑且称之为行移动），行 id 可能会发生改变。要确保该表支持行移动，如果不支持，您可以使用如下命令来支持它：alter table table_name enable row movement。该命令将会在块内重新分配行并空间返回给表空间，如图 3 所示，该表内所有未用的空间都返回给表空间，以让其他段使用。 如图3 所示，此时会把段中原来空闲的块返回给数据库，HWM 本身也会进行重新分配。
图 3：在收缩后，把空闲块返回给数据库。
如下所示HWM已降低到1031，表中没有碎片，全表扫描读取的数据块随之减少。如果表上有索引还可以一起压缩索引：alter table bookings shrink space cascade;
| SQL> ANALYZE TABLE testhw COMPUTE STATISTICS; SQL> SELECT table_name, num_rows, blocks, empty_blocks,avg_row_len FROM user_tables u WHERE table_name = 'TESTHW'; SQL> select segment_name,header_file,header_block,blocks,bytes,extents,min_extents,max_extents,segment_type from dba_segments where segment_name='TESTHW'; SQL> alter table testhw enable row movement; SQL> alter table testhw shrink space; SQL> exec show_space('testhw','auto','t','y'); SQL> set autotrace TRACE STAT |
联机 shrink 命令是一个用于回收浪费的空间和重置 HWM 的强大的特性。我把后者（重置 HWM）看作该命令最有用的结果，因为它改进了全表扫描的性能。到此我们可以说已经解决了HWM带来的性能问题。其实shrink还可以只收回空间不重置HWM，即：alter table table_name shrink space compact，此时如图4所示在 HWM 之下产生了更多的空闲块 — 但是 HWM 自身不会进行分配。
图 4：重组行后的表中的块单不重置HWM。
3.2 解决高水位带来的空间问题：
表空间的高水位，由前面的概念不难理解即是所有表空间上段的HWM之和。考虑下面的情况，一个表空间中有很多表，有些表中有很多碎片，有些空表，我们想回收表空间上的空闲空间或者说我们空间紧张要收回这部分空间，如何处理？
接着上面例子，首先我们的思路是，接着创建一个空表testhw2，然后对表testhw再插入些数据，因为表testhw已经没有空闲的空间此时表数据会插入到testhw2后面，再创建一个空表testhw3，此时可以肯定testhw中会部分数据在testhw2和testhw3之间，就是说表空间中有些表的数据分布很散而且有碎片，有些是空表。
SQL> create table testhw2 tablespace test as select * from testhw where 1=2;
Table createdSQL> ANALYZE TABLE testhw2 COMPUTE STATISTICS;
Table analyzedSQL> 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 0SQL> 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 completedSQL> ANALYZE TABLE testhw COMPUTE STATISTICS;
Table analyzedSQL> ANALYZE TABLE testhw2 COMPUTE STATISTICS;
Table analyzedSQL> 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 0SQL> 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 SEGMENT_NA HEADER_FILE HEADER_BLOCK BLOCKS BYTES EXTENTS MIN_EXTENTS MAX_EXTENTS SEGMENT_TYPE |
1420=1164+128+128 第一个128 blocks是分配给testhw2的，
第二个128 blocks是上面我们已经算出来，是testhw 表中我们后来插入的id大于1000的部分。
此时表空间的HWM是1548。删除id大于1000的数据，然后使用上面的shrink命令收回空间。
| SQL> delete from testhw where id > 1000; SQL> commit; SQL> alter table testhw shrink space; SQL> ANALYZE TABLE testhw COMPUTE STATISTICS; SQL> ANALYZE TABLE testhw2 COMPUTE STATISTICS; SQL> ANALYZE TABLE testhw3 COMPUTE STATISTICS; SQL> SELECT table_name, num_rows, blocks, empty_blocks,avg_row_len FROM user_tables u WHERE table_name like 'TESTHW%' ; 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%'; SQL> exec show_space('testhw','auto','t','y'); |
虽然表testhw中已经没有碎片，但是testhw3的header_block并不会因此而向前移动，表空间的空闲空间仍然无法收回！实际上即便是truncate也无法降低表空间的HWM。
| SQL> truncate table testhw; SQL> truncate table testhw2; SQL> truncate table testhw3; 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%' ; |
那该如何收回空闲的空间呢，答案是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; SQL> alter table testhw2 move tablespace users; SQL> alter table testhw3 move tablespace users; SQL> alter table testhw3 move tablespace test; SQL> alter table testhw2 move tablespace test; SQL> alter table testhw move tablespace test; SQL> alter table testhw2 move tablespace test; SQL> alter table testhw3 move tablespace test; 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 ; SQL> alter database datafile 96 resize 4M; |
至此有关高水位的所有问题都已圆满解决，希望你读了本文之后不再为高水位以及表的碎片的问题而烦恼。但是如果数据库是Oracle9i 及其以前的版本的话，上面的方法不再适用，可以通过。1、exp表的数据，删除表，重建表，imp数据来回收空间；2、通过使用 ALTER TABLE MOVE 命令把表移动到一个不同的表空间；3、使用联机表重组特性，但是这需要至少双倍的现有表空间。
下面是show_space的脚本：
create or replace procedure show_space(p_segname_1 in varchar2,
p_space in varchar2 default 'MANUAL',
p_type_1 in varchar2 default 'TABLE',
p_analyzed in varchar2 default 'N',
p_owner_1 in varchar2 default user) as
p_segname varchar2(100);
p_type varchar2(10);
p_owner varchar2(30);
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number;
l_fs1_bytes number;
l_fs2_blocks number;
l_fs2_bytes number;
l_fs3_blocks number;
l_fs3_bytes number;
l_fs4_blocks number;
l_fs4_bytes number;
l_full_blocks number;
l_full_bytes number;
l_free_blks number;
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
p_segname := upper(p_segname_1); -- rainy changed
p_owner := upper(p_owner_1);
p_type := p_type_1;
if (p_type_1 = 'i' or p_type_1 = 'I') then
--rainy changed
p_type := 'INDEX';
end if;
if (p_type_1 = 't' or p_type_1 = 'T') then
--rainy changed
p_type := 'TABLE';
end if;
if (p_type_1 = 'c' or p_type_1 = 'C') then
--rainy changed
p_type := 'CLUSTER';
end if;
sys.dbms_space.unused_space(segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
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);
if p_space = 'MANUAL' or (p_space <> 'auto' and p_space <> 'AUTO') then
dbms_space.free_blocks(segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks);
p('Free Blocks', l_free_blks);
end if;
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);
/*IF the segment is analyzed */
if (p_analyzed = 'Y' or p_analyzed ='y' ) then
dbms_space.space_usage(segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
unformatted_blocks => l_unformatted_blocks,
unformatted_bytes => l_unformatted_bytes,
fs1_blocks => l_fs1_blocks,
fs1_bytes => l_fs1_bytes,
fs2_blocks => l_fs2_blocks,
fs2_bytes => l_fs2_bytes,
fs3_blocks => l_fs3_blocks,
fs3_bytes => l_fs3_bytes,
fs4_blocks => l_fs4_blocks,
fs4_bytes => l_fs4_bytes,
full_blocks => l_full_blocks,
full_bytes => l_full_bytes);
dbms_output.put_line(rpad(' ', 50, '*'));
dbms_output.put_line('The segment is analyzed');
p('0% -- 25% free space blocks', l_fs1_blocks);
p('0% -- 25% free space bytes', l_fs1_bytes);
p('25% -- 50% free space blocks', l_fs2_blocks);
p('25% -- 50% free space bytes', l_fs2_bytes);
p('50% -- 75% free space blocks', l_fs3_blocks);
p('50% -- 75% free space bytes', l_fs3_bytes);
p('75% -- 100% free space blocks', l_fs4_blocks);
p('75% -- 100% free space bytes', l_fs4_bytes);
p('Unused Blocks', l_unformatted_blocks);
p('Unused Bytes', l_unformatted_bytes);
p('Total Blocks', l_full_blocks);
p('Total bytes', l_full_bytes); end if;
end;
/
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15779287/viewspace-630577/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/15779287/viewspace-630577/
扫一扫
675
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
