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