用Delete与Truncate清除表数据对高水位的影响

来源：互联网发布：在淘宝卖保健品的要求编辑：程序博客网时间：2024/05/21 06:27

众所周知，oracle段都有一个在段内容纳数据块的上限，我们把这个上限称为"High Water Mark"（HWM）。这个HWM是一个标记，用来说明已经有多少没有使用的数据块分配给这个segment。原则上HWM只会增大，不会缩小，即使将表中的数据都删除，HWM还是为原值。HWM就像一个水库的历史最高水位，这也是为何会称之为“高水位”的缘故。实际环境中随着我们表中数据的不断增长，表的高水位也被不断的推高。当高水位达到一定程度之后，会对该表上的SQL查询效率产生负面影响，因此需要采取有效措施降低高水位。下面做个测试，来比较下如何删除数据才能有效降低高水位。注意，我的测试环境为11.2.0.3，其他版本的测试结果可能略有不同。

--创建测试环境

SQL> conn / as sysdba

SQL> create tablespace zlm datafile '/u01/app/oracle/oradata/zlm11g/zlm01.dbf' size 50m;

Tablespace created.

SQL> create table zlm.zlm1 as select * from dba_objects;

Table created.

SQL> set lin 130 pages 130

SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'ZLM%';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED

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

ZLM1

--分析表的统计信息

SQL> analyze table zlm.zlm1 estimate statistics;

Table analyzed.

SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'ZLM%';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED

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

ZLM1 77341 1101 51 100 27-SEP-14

SQL> select count(*) from zlm.zlm1;

COUNT(*)

----------

75541

可以看到，用estimate分析的表的行数会不准确，差了1800条记录，我们用compute来分析表

SQL> analyze table zlm.zlm1 compute statistics;

Table analyzed.

SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'ZLM%';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED

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

ZLM1 75541 1101 51 100 27-SEP-14

也可以用dbms_stats包来收集表的统计信息

SQL> exec dbms_stats.gather_table_stats('ZLM','ZLM1')

PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'ZLM%';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED

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

ZLM1 75541 1101 51 97 27-SEP-14

这里我们发现，两者除了在AVG_ROW_LEN字段上的值略有不同（前者是100，后者是97）外，其他基本一致

对于普通表而言，用dbms_stats包和用analyze来收集统计信息区别不大，但这两种方法还是有各自应用场景的：

1、对于分区表，建议使用DBMS_STATS，而不是使用Analyze语句
a) 可以并行进行，对多个用户，多个Table
b) 可以得到整个分区表的数据和单个分区的数据
c) 可以在不同级别上Compute Statistics：单个分区，子分区，全表，所有分区
d) 可以倒出统计信息
e) 可以用户自动收集统计信息
2、DBMS_STATS的缺点
a) 不能Validate Structure
b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，这两个仍旧需要使用Analyze语句
c) DBMS_STATS 默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True
3、对于oracle 9里面的External Table，不能使用Analyze，只能使用DBMS_STATS

SQL> select header_file,header_block,bytes,blocks,extents from dba_segments where segment_name like 'ZLM%';

HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS

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

6 130 9437184 1152 24

这里我们发现，在dba_tables中占用的块为1101+52=1152，其中1152就是我们的高水位，EMPTY_BLOCKS表示高水位以下未被使用的空块，

我们可以通过show_space()存储过程来验证一下：

SQL> exec show_space('ZLM1','ZLM')

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

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

Unused Blocks...........................51

Unused Bytes............................417792

Last Used Ext FileId....................6

Last Used Ext BlockId...................1152

Last Used Block.........................77

PL/SQL procedure successfully completed.

注意，这里77+51=128，正好是最后分配的一个extent的大小

--查看测试表ZLM1占用extent和block的情况

SQL> select block_id,extent_id,bytes,blocks from dba_extents where segment_name like 'ZLM%';

BLOCK_ID EXTENT_ID BYTES BLOCKS

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

128 0 65536 8

136 1 65536 8

144 2 65536 8

152 3 65536 8

160 4 65536 8

168 5 65536 8

176 6 65536 8

184 7 65536 8

192 8 65536 8

200 9 65536 8

208 10 65536 8

216 11 65536 8

224 12 65536 8

232 13 65536 8

240 14 65536 8

248 15 65536 8

256 16 1048576 128 --从256块起，开始分配1M的空间作为1个extent

384 17 1048576 128

512 18 1048576 128

640 19 1048576 128

768 20 1048576 128

896 21 1048576 128

1024 22 1048576 128

1152 23 1048576 128

从block_id字段的值可以发现，当在表中插入数据后，分配给表的block从128开始一直到1152，并且，开始的1-16个extent会以8个块（8*8=64K）为分配单位，而到了第17个extent之后，则以128个块（8*128=1M）为分配单位，目前ZLM1表共分配了24个extent

--查看测试表ZLM1的block分配情况

SQL> col segment_name for a10

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

SEGMENT_NA HEADER_FILE HEADER_BLOCK BLOCKS BYTES EXTENTS MIN_EXTENTS MAX_EXTENTS SEGMENT_TYPE

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

ZLM1 6 130 1152 9437184 24 1 2147483645 TABLE

这里可以发现一个规律，dba_segments中的header_block的值总是要比dba_extents查出来值的多2个块

--创建测试表ZLM2并查看block分配情况

SQL> create table zlm.zlm2 as select * from zlm.zlm1;

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 like 'ZLM%';

SEGMENT_NA HEADER_FILE HEADER_BLOCK BLOCKS BYTES EXTENTS MIN_EXTENTS MAX_EXTENTS SEGMENT_TYPE

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

ZLM2 6 1282 1152 9437184 24 1 2147483645 TABLE

ZLM1 6 130 1152 9437184 24 1 2147483645 TABLE

第2个表从1282个块开始分配，为什么是1282呢？1282=130+1152，也就是从测试表ZLM1之后的block开始分配

--delete测试表ZLM1中的数据

SQL> delete from zlm.zlm1;

75541 rows deleted.

SQL> commit;

Commit complete.

SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'ZLM%';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED

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

ZLM1 75541 1101 51 100 27-SEP-14

--分析测试表ZLM1和ZLM2并查看各自数据块占用情况

SQL> analyze table zlm.zlm1 compute statistics;

Table analyzed.

SQL> analyze table zlm.zlm2 compute statistics;

Table analyzed.

SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'ZLM%';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED

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

ZLM1 0 1101 51 0 27-SEP-14

ZLM2 75541 1101 51 100 27-SEP-14

删除ZLM1表中数据以后，NUM_ROWS值为0，但是还是占用了1152个数据块，说明此时高水位并没有下降，仍然为1152

--truncate测试表ZLM1

SQL> truncate table zlm.zlm1;

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 'ZLM%';

SEGMENT_NA HEADER_FILE HEADER_BLOCK BLOCKS BYTES EXTENTS MIN_EXTENTS MAX_EXTENTS SEGMENT_TYPE

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

ZLM2 6 1282 1152 9437184 24 1 2147483645 TABLE

ZLM1 6 130 8 65536 1 1 2147483645 TABLE

用truncate清空表数据之后，高水位立即下降到8了，8个block共占用64k，1个extent

--重新分析测试表ZLM2并查看数据块占用情况

SQL> analyze table zlm.zlm1 compute statistics;

Table analyzed.

SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'ZLM%';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED

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

ZLM2 75541 1101 51 100 27-SEP-14

ZLM1 0 0 8 0 27-SEP-14

--查看数据块和extent分布情况

SQL> select block_id,extent_id,bytes,blocks from dba_extents where segment_name like 'ZLM1';

BLOCK_ID EXTENT_ID BYTES BLOCKS

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

128 0 65536 8

可以看到，虽然测试表ZLM1中已经没有数据，但是还是会占用8个数据块的高水位，也就是说，当你创建一个空表，即只建立表结构的时候，Oracle就会给这个表分配至少一个extent，默认为8，可以通过参数调整这个默认值

SQL> select block_id,extent_id,bytes,blocks from dba_extents where segment_name like 'ZLM2';

BLOCK_ID EXTENT_ID BYTES BLOCKS

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

1280 0 65536 8

1288 1 65536 8

1296 2 65536 8

1304 3 65536 8

1312 4 65536 8

1320 5 65536 8

1328 6 65536 8

1336 7 65536 8

1344 8 65536 8

1352 9 65536 8

1360 10 65536 8

1368 11 65536 8

1376 12 65536 8

1384 13 65536 8

1392 14 65536 8

1400 15 65536 8

1408 16 1048576 128

1536 17 1048576 128

1664 18 1048576 128

1792 19 1048576 128

1920 20 1048576 128

2048 21 1048576 128

2176 22 1048576 128

2304 23 1048576 128

--创建测试表ZLM3并插入数据查看数据块分配情况

SQL> create table zlm.zlm3 as select * from zlm.zlm2;

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 like 'ZLM%';

SEGMENT_NA HEADER_FILE HEADER_BLOCK BLOCKS BYTES EXTENTS MIN_EXTENTS MAX_EXTENTS SEGMENT_TYPE

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

ZLM3 6 138 1152 9437184 24 1 2147483645 TABLE

ZLM2 6 1282 1152 9437184 24 1 2147483645 TABLE

ZLM1 6 130 8 65536 1 1 2147483645 TABLE

SQL> select block_id,extent_id,bytes,blocks from dba_extents where segment_name like 'ZLM3';

BLOCK_ID EXTENT_ID BYTES BLOCKS

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

136 0 65536 8

144 1 65536 8

152 2 65536 8

160 3 65536 8

168 4 65536 8

176 5 65536 8

184 6 65536 8

192 7 65536 8

200 8 65536 8

208 9 65536 8

216 10 65536 8

224 11 65536 8

232 12 65536 8

240 13 65536 8

248 14 65536 8

256 15 65536 8

384 16 1048576 128

512 17 1048576 128

640 18 1048576 128

768 19 1048576 128

896 20 1048576 128

1024 21 1048576 128

1152 22 1048576 128

2432 23 1048576 128

在降低了测试表空间中的高水位后，新创建的表会从第138块开始分配header_block空间，而不是1282+1152=2434，注意这里header_block的值依然会比此表extent中分配的第一个block的值要大2，而2434-2正好等于2432，也就是该表分配到的最后一个extent中block的起始id，通过truncate，可以重新把原来应该由测试表ZLM1占用的那部分block空间释放出来，给测试表ZLM3的extent，用于分配block

总结：

本测试只是验证了delete和truncate对于高水位的影响，与减少磁盘碎片及空间释放的概念并不是很相关，其实用truncate降低高水位来释放磁盘空间的作用是很有限的，我们关注高水位的主要目的，不是去减少浪费磁盘空间，而是出于性能上的考虑，由于高水位的存在，一旦对表做全表扫描，就会使性能大大降低，即使只是一张空的表，如果要真正减少表空间碎片，减少磁盘占用空间，更有效的方法是使用move或shrink(10g or higher)，甚至expdp导出再impdp倒入等方法，这里就不一一举例了，当然了，这些方法也都是降低高水位的有效手段。

0 0