程序员人生 网站导航

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

栏目:互联网时间:2014-10-08 08:00:01

众所周知,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                                    
------分隔线----------------------------
------分隔线----------------------------

最新技术推荐