block中AVG_SPACE的意义

在hwm很高的情况下，AVG_SPACE越大意味着block中free space越大，这时候如果表很大的话，这里表大一方面是指num_rows很大，另一方AVG_ROW_LEN也很大。AVG_SPACE如果大到一定程度（当然这个度自己根据和block_size相比较把握）想办法shrink一下这个表，降低hwm的同时降低AVG_SPACE...从而提高性能和空间利用率。[@more@]

SQL> create table t tablespace users as select * from dba_objects;

表已创建。

SQL> insert into t select * from t;

已创建11319行。

SQL>
SQL> insert into t select * from t;

已创建22638行。

SQL> commit;

提交完成。

SQL> exec dbms_stats.gather_table_stats('SYS','T');

PL/SQL 过程已成功完成。

SQL> select blocks,num_rows,empty_blocks,AVG_SPACE,AVG_ROW_LEN from user_tables
where table_name='T';

BLOCKS NUM_ROWS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN
---------- ---------- ------------ ---------- -----------
634 45276 0 0 85

SQL> delete from t where object_id<1000;

已删除3812行。

SQL> commit;

提交完成。

SQL> exec dbms_stats.gather_table_stats('SYS','T');
BEGIN dbms_stats.gather_table_stats('SYS','T'); END;

*
第 1 行出现错误:
ORA-20000: Unable to analyze TABLE "SYS"."T", insufficient privileges or does
not exist
ORA-06512: 在 "SYS.DBMS_STATS", line 13046
ORA-06512: 在 "SYS.DBMS_STATS", line 13076
ORA-06512: 在 line 1

SQL> exec dbms_stats.gather_table_stats('SYS','T');
BEGIN dbms_stats.gather_table_stats('SYS','T'); END;

*
第 1 行出现错误:
ORA-20000: Unable to analyze TABLE "SYS"."T", insufficient privileges or does
not exist
ORA-06512: 在 "SYS.DBMS_STATS", line 13046
ORA-06512: 在 "SYS.DBMS_STATS", line 13076
ORA-06512: 在 line 1

SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开

C:>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 12月 17 10:07:43 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> exec dbms_stats.gather_table_stats('SYS','T');
BEGIN dbms_stats.gather_table_stats('SYS','T'); END;

*
第 1 行出现错误:
ORA-20000: Unable to analyze TABLE "SYS"."T", insufficient privileges or does
not exist
ORA-06512: 在 "SYS.DBMS_STATS", line 13046
ORA-06512: 在 "SYS.DBMS_STATS", line 13076
ORA-06512: 在 line 1

SQL> show user
USER 为 "SYS"
SQL> analyze table t compute statistics;

表已分析。

SQL> select blocks,num_rows,empty_blocks,AVG_SPACE,AVG_ROW_LEN from user_tables
where table_name='T';

BLOCKS NUM_ROWS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN
---------- ---------- ------------ ---------- -----------
634 41464 6 1989 89

SQL> delete from t where object_id<2000;

已删除4000行。

SQL> commit;

提交完成。

SQL> exec dbms_stats.gather_table_stats('SYS','T');
BEGIN dbms_stats.gather_table_stats('SYS','T'); END;

*
第 1 行出现错误:
ORA-20000: Unable to analyze TABLE "SYS"."T", insufficient privileges or does
not exist
ORA-06512: 在 "SYS.DBMS_STATS", line 13046
ORA-06512: 在 "SYS.DBMS_STATS", line 13076
ORA-06512: 在 line 1

SQL> analyze table t compute statistics;

表已分析。

SQL> select blocks,num_rows,empty_blocks,AVG_SPACE,AVG_ROW_LEN from user_tables
where table_name='T';

BLOCKS NUM_ROWS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN
---------- ---------- ------------ ---------- -----------
634 37464 6 2544 89

SQL> delete from t where object_id<5000;

已删除11852行。

SQL> commit;

提交完成。

SQL> analyze table t compute statistics;

表已分析。

SQL> select blocks,num_rows,empty_blocks,AVG_SPACE,AVG_ROW_LEN from user_tables
where table_name='T';

BLOCKS NUM_ROWS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN
---------- ---------- ------------ ---------- -----------
634 25612 6 4173 91

SQL> delete from t where object_id<10000;

已删除18776行。

SQL> commit;

提交完成。

SQL> analyze table t compute statistics;

表已分析。

SQL> select blocks,num_rows,empty_blocks,AVG_SPACE,AVG_ROW_LEN from user_tables
where table_name='T';

BLOCKS NUM_ROWS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN
---------- ---------- ------------ ---------- -----------
634 6836 6 6874 95

SQL> delete from t ;

已删除6836行。

SQL> commit;

提交完成。

SQL> analyze table t compute statistics;

表已分析。

SQL> select blocks,num_rows,empty_blocks,AVG_SPACE,AVG_ROW_LEN from user_tables
where table_name='T';

BLOCKS NUM_ROWS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN
---------- ---------- ------------ ---------- -----------
634 0 6 7919 0

SQL> alter table t enable row movement;

表已更改。

SQL> alter table t shrink space ;

表已更改。

SQL> analyze table t compute statistics;

表已分析。

SQL> select blocks,num_rows,empty_blocks,AVG_SPACE,AVG_ROW_LEN from user_tables
where table_name='T';

BLOCKS NUM_ROWS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN
---------- ---------- ------------ ---------- -----------
1 0 7 7864 0

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1043207/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1043207/