首页 > 代码库 > 用Delete与Truncate清除表数据对高水位的影响

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

2024-07-24 17:44:10   222人阅读

众所周知，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;

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

对于普通表而言，用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.

--查看测试表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倒入等方法，这里就不一一举例了，当然了，这些方法也都是降低高水位的有效手段。



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


联系
我们