ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 优化Oracle存储——合并碎片

优化Oracle存储——合并碎片

原创 Linux操作系统 作者:g644516804 时间:2012-03-07 13:52:27 0 删除 编辑
在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对分区表是不适用的。

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

上一篇: ORA-31693
请登录后发表评论 登录
全部评论

注册时间:2011-03-04

  • 博文量
    104
  • 访问量
    238053