ITPub博客

首页 > 应用开发 > IT综合 > block中AVG_SPACE的意义

block中AVG_SPACE的意义

原创 IT综合 作者:warehouse 时间:2010-12-17 10:14:54 0 删除 编辑
在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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    717
  • 访问量
    5097743