优化Oracle存储——合并碎片

在10g以前，要合并表的碎片，一般使用使用alter table t move ts命令，然后rebuild索引，因为move会导致rowid改变，从而原来的索引实效。

查看哪些表在空间使用上有问题，可以查看dba_tables视图的avg_space列，这个是块的平均空闲空间，缺省为10%，是PCTFREE参数指定的，也就是819 Bytes左右（块大小为8K）。如果avg_space > 2K，可能重整空间会有意义，这个值太大说明空间利用率较低，块空闲较多。我们调整空间的主要的目的是降低HWM，使扫描的块变少，从而提高效率。

10g提供了新的shrink space命令可以合并碎片，下面是对一个大表的操作：

数据库环境：

p5b2@/home/oracle$ sqlplus " / as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Aug 10 17:05:46 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

先查看表的大小：

SQL> show parameter db_block_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192

SQL> select table_name,avg_space,blocks from dba_tables
2  where wner='ERP' and table_name=upper('mpaymentappl');

TABLE_NAME                      AVG_SPACE     BLOCKS
------------------------------ ---------- ----------
MPAYMENTAPPL                         3130      80494

SQL> select blocks from dba_segments where segment_name = 'MPAYMENTAPPL' and wner='ERP';

BLOCKS
----------
81920

SQL> select sum(blocks) blocks from dba_extents where segment_name = 'MPAYMENTAPPL' and wner='ERP';

BLOCKS
----------
81920

SQL> select count(*) from erp.mpaymentappl;

COUNT(*)
----------
792530

必须启用行移动功能才能使用shrink space：

SQL> set time on

17:10:57 SQL> alter table erp.mpaymentappl shrink space cascade;
alter table erp.mpaymentappl shrink space cascade
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled
17:10:59 SQL> alter table erp.mpaymentappl enable row movement;

Table altered.

17:13:00 SQL> alter table erp.mpaymentappl shrink space cascade;

Table altered.

17:23:54 SQL>

17:24:09 SQL> set time off;

查看shrink space后的占用的块：

SQL> select sum(blocks) blocks from dba_extents where segment_name = 'MPAYMENTAPPL' and wner='ERP';

BLOCKS
----------
80896

SQL> select blocks from dba_segments where segment_name = 'MPAYMENTAPPL' and wner='ERP';

BLOCKS
----------
80896

SQL> select table_name,avg_space,blocks from dba_tables where wner='ERP' and table_name=upper('mpaymentappl');

TABLE_NAME                      AVG_SPACE     BLOCKS
------------------------------ ---------- ----------
MPAYMENTAPPL                         3130      80494

可见收缩的块数为81920-80896=1024个，

而dba_tables没有变化是因为这些数据来自统计信息，还没有更新，分析表：

SQL> exec dbms_stats.gather_table_stats(ownname=>'erp',tabname=>'mpaymentappl',method_opt=>'for all indexed columns',cascade=> TRUE);

PL/SQL procedure successfully completed.

SQL> select table_name,avg_space,blocks from dba_tables where wner='ERP' and table_name=upper('mpaymentappl');

TABLE_NAME                      AVG_SPACE     BLOCKS
------------------------------ ---------- ----------
MPAYMENTAPPL                         3130      80421
SQL> analyze table erp.mpaymentappl estimate statistics;

Table analyzed.

SQL> select table_name,avg_space,blocks from dba_tables where wner='ERP' and table_name=upper('mpaymentappl');

TABLE_NAME                      AVG_SPACE     BLOCKS
------------------------------ ---------- ----------
MPAYMENTAPPL                         3123      80421

SQL> select blocks from dba_segments where segment_name = 'MPAYMENTAPPL' and wner='ERP';

BLOCKS
----------
80896

可见AVG_SPACE下降并不明显，收缩的效果有限。

建立新表查看：

SQL>create table mpaymentappl_bak_20090810 as select * from mpaymentappl;

SQL> select table_name,avg_space,blocks,pct_free from dba_tables where wner='ERP' and table_name=upper('mpaymentappl_bak_20090810');

TABLE_NAME                      AVG_SPACE     BLOCKS   PCT_FREE
------------------------------ ---------- ---------- ----------
MPAYMENTAPPL_BAK_20090810               0      56446         10

SQL> analyze table erp.mpaymentappl_bak_20090810 estimate statistics;

Table analyzed.

SQL> select table_name,avg_space,blocks,pct_free from dba_tables where wner='ERP' and table_name=upper('mpaymentappl_bak_20090810');

TABLE_NAME                      AVG_SPACE     BLOCKS   PCT_FREE
------------------------------ ---------- ---------- ----------
MPAYMENTAPPL_BAK_20090810            1106      56446         10

这时占用的block为56446个，比原来减少了81920－56446＝25474个，

减少的比例为25474/81920=31%，而从avg_space来看块的减少个数：

3130-1106=2024  2024*81920/8192=20240

可见shrink space并不是我们想象的那样完美，会充分利用各个块的空闲空间。

如果只是使数据紧密而保持HWM不变：
alter table table_name shrink space compact;

同时收缩表并降低HWM：
alter table table_name shrink space;

收缩表与索引：
alter table table_name shrink space cascade;

只收缩索引：
alter index index_name shrink space;

还要注意的是，shrink space对分区表是不适用的。

轉： http://www.banping.com/2009/08/11/oracle_shrink_space/

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

转载于:http://blog.itpub.net/24849178/viewspace-717936/