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

众所周知，Oracle段都有一个在段内容纳数据块的上限，我们把这个上限称为"High Water Mark"（HWM）。这个HWM是一个标记，用来说明已经有多少没有使用的数据块分配给这个segment。原则上HWM只会增大，不会缩小，即使将表中的数据都删除，HWM还是为原值。HWM就像一个水库的历史最高水位，这也是为何会称之为“高水位”的缘故。实际环境中随着我们表中数据的不断增长，表的高水位也被不断的推高。当高水位达到一定程度之后，会对该表上的SQL查询效率产生负面影响，因此需要采取有效措施降低高水位。下面做个测试，来比较下如何删除数据才能有效降低高水位。注意，我的测试环境为11.2.0.3，其他版本的测试结果可能略有不同。delete与truncate清空表数据对HWM的影响 http://www.linuxidc.com/Linux/2012-08/69278.htm--创建测试环境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............................1152Total Bytes.............................9437184Unused Blocks...........................51Unused Bytes............................417792Last Used Ext FileId....................6Last Used Ext BlockId...................1152Last 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 a10SQL> 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 TABLEZLM1 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-14ZLM2 75541 1101 51 100 27-SEP-14 删除ZLM1表中数据以后，NUM_ROWS值为0，但是还是占用了1152个数据块，说明此时高水位并没有下降，仍然为1152更多详情见请继续阅读下一页的精彩内容： http://www.linuxidc.com/Linux/2014-09/107345p2.htm

MySQL 升级的最佳实践Delete删除表数据时对性能的影响分析相关资讯 Truncate

存在外键关联的主表truncate如何做（06/09/2015 16:22:57）

Truncate数据表背后的几个参数（04/07/2015 21:03:50）

本文评论查看全部评论（0）