解决HWM带来性能问题

来源：互联网发布：2017年淘宝天猫电商节编辑：程序博客网时间：2024/06/06 01:45

解决HWM带来性能问题

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

SQL> create tablespace test datafile'/stg/sx/oradata/mydata/test01.dbf' size 500m autoextend off extent management localuniform size 1m segmentspace management auto ;

Tablespace created

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

Table created

SQL> selectsegment_name,header_file,header_block,blocks,bytes,extents,min_extents,max_extents,segment_typefrom dba_segments where segment_name='TESTHW';

SEGMENT_NA HEADER_FILEHEADER_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) 首先对表进行分析：ANALYZETABLE <tablename> ESTIMATE/COMPUTE STATISTICS;

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

SQL> ANALYZE TABLE TESTHW COMPUTESTATISTICS;

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 ormod(i, 5) = 0) then

delete fromtesthw where id = i;

commit;

end if;

end loop;

end;

SQL> ANALYZE TABLE testhw COMPUTESTATISTICS;

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> selectsegment_name,header_file,header_block,blocks,bytes,extents,min_extents,max_extents,segment_typefrom dba_segments where segment_name='TESTHW';

SEGMENT_NA HEADER_FILEHEADER_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> execshow_space('testhw','auto','t','y');

TotalBlocks............................2176

TotalBytes.............................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 spaceblocks............204

25% -- 50% free spacebytes.............1671168

50% -- 75% free spaceblocks............173

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

75% -- 100% free spaceblocks...........804

75% -- 100% free spacebytes............6586368

UnusedBlocks...........................62

UnusedBytes............................507904

TotalBlocks............................897

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

PL/SQL procedure successfully completed

SQL> select 204+173+804+62+897 Total fromdual;

TOTAL

----------

2140

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

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

SQL> set autotrace TRACESTAT

SQL> select * fromdbmgr.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> setautotrace off

Oracle 10g提供了一个清理碎片的方法：alter tabletable_name shrink space，该命令将重组表中现有行，在此之前要确保在该表上禁用所有基于行 id 的触发器，这是因为行将会移动（和行迁移有些不同所以姑且称之为行移动），行 id 可能会发生改变。要确保该表支持行移动，如果不支持，您可以使用如下命令来支持它：altertable table_name enable row movement。该命令将会在块内重新分配行并空间返回给表空间，如图 3 所示，该表内所有未用的空间都返回给表空间，以让其他段使用。如图3 所示，此时会把段中原来空闲的块返回给数据库，HWM 本身也会进行重新分配。

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

SQL> ANALYZE TABLE testhw COMPUTESTATISTICS;

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> selectsegment_name,header_file,header_block,blocks,bytes,extents,min_extents,max_extents,segment_typefrom dba_segments where segment_name='TESTHW';

SEGMENT_NA HEADER_FILEHEADER_BLOCK BLOCKS BYTES EXTENTS MIN_EXTENTS MAX_EXTENTS SEGMENT_TYPE

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

TESTHW 96 12 1152 9437184 9 1 2147483645 TABLE

SQL> alter table testhw enable rowmovement;

Table altered.

SQL> alter table testhw shrink space;

Table altered.

SQL> execshow_space('testhw','auto','t','y');

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

TotalBytes.............................9437184

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

UnusedBytes............................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 spaceblocks...........2

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

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

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

TotalBlocks............................1029

Totalbytes.............................8429568

PL/SQL procedure successfully completed

SQL> set autotrace TRACE STAT

SQL> select * from dbmgr.testhw whereid=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 tabletable_name shrink space compact，此时如图4所示在 HWM 之下产生了更多的空闲块 — 但是 HWM 自身不会进行分配。

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

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

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

Table created

SQL> ANALYZETABLE testhw2 COMPUTE STATISTICS;

Table analyzed

SQL> SELECTtable_name, num_rows, blocks, empty_blocks,avg_row_len FROM user_tables u WHEREtable_name like 'TESTHW%';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN

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

TESTHW 266332 1033 119 28

TESTHW2 0 0 128 0

SQL> selectsegment_name,header_file,header_block,blocks,bytes,extents,min_extents,max_extents,segment_typefrom dba_segments where segment_name like 'TESTHW%';

SEGMENT_NAHEADER_FILE HEADER_BLOCK BLOCKS BYTES EXTENTSMIN_EXTENTS MAX_EXTENTS SEGMENT_TYPE

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

TESTHW 96 12 1152 9437184 9 1 2147483645TABLE

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 .. iloop

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

end loop;

commit;

end loop;

end;

PL/SQL procedure successfully completed

SQL> ANALYZE TABLE testhw COMPUTESTATISTICS;

Table analyzed

SQL> ANALYZE TABLE testhw2 COMPUTESTATISTICS;

Table analyzed

SQL> SELECT table_name, num_rows, blocks, empty_blocks,avg_row_len FROM user_tables u WHEREtable_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_typefrom dba_segments where segment_name like 'TESTHW%' ;

SEGMENT_NAHEADER_FILE HEADER_BLOCK BLOCKS BYTES EXTENTSMIN_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=128blocks。接着创建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_FILEHEADER_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 COMPUTESTATISTICS;

Table analyzed

SQL> ANALYZE TABLE testhw2 COMPUTESTATISTICS;

Table analyzed

SQL> ANALYZE TABLE testhw3 COMPUTESTATISTICS;

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_typefrom dba_segments where segment_name like 'TESTHW%';

SEGMENT_NA HEADER_FILEHEADER_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> execshow_space('testhw','auto','t','y');

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

TotalBytes.............................9437184

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

UnusedBytes............................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_typefrom dba_segments where segment_name like 'TESTHW%' ;

SEGMENT_NA HEADER_FILEHEADER_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 2147483645TABLE

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

SQL> alter table testhw move tablespaceusers;

Table altered

SQL> alter table testhw2 move tablespaceusers;

Table altered

SQL> alter table testhw3 move tablespaceusers;

Table altered

SQL> alter table testhw3 move tablespacetest;

Table altered

SQL> alter table testhw2 move tablespacetest;

Table altered

SQL> alter table testhw move tablespacetest;

Table altered

SQL> alter table testhw2 move tablespacetest;

Table altered

SQL> alter table testhw3 move tablespacetest;

Table altered

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

SEGMENT_NA HEADER_FILEHEADER_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 2147483645TABLE

SQL> alter database datafile 96 resize 4M;

Database altered

show_space的脚本：

create or replaceprocedure show_space(p_segname_1 invarchar2,

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_segnamevarchar2(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;

procedurep(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('TotalBlocks', l_total_blocks);

p('TotalBytes', l_total_bytes);

p('UnusedBlocks', l_unused_blocks);

p('UnusedBytes', l_unused_bytes);

p('Last UsedExt FileId', l_LastUsedExtFileId);

p('Last UsedExt BlockId', l_LastUsedExtBlockId);

p('Last UsedBlock', l_LAST_USED_BLOCK);

/*IF thesegment 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;

0 0