Oracle中的shrink space
- 一、概述
- 二、使用shrink space整理过程

Oracle中的shrink space

一、概述

数据库在日常使用过程中，不断的insert，delete，update操作，导致表和索引出现碎片是在所难免的事情，碎片多了，sql的执行效率自然就差了，道理很简单，高水位线（HWL）下的许多数据块都是无数据的，但全表扫描的时候要扫描到高水位线的数据块，也就是说oracle要做许多的无用功！

在9i的时候，使用move进行碎片的整理

alter table xxx move    高水位以下合并碎片，不移动高水位
alter table xxx move compress    高水位以下合并碎片，同时压缩表，不移动高水位。

缺点：

但是美中不足的是move不移动高水位，并且还要重建索引。
另外，在整个move过程表上TM上一直有6的锁，所以MOVE会影响业务。

在10g的时候，使用shrink space进行碎片的整理

shrink space碎片整理功能不仅能整理碎片还可以收缩高水位,索引也不需要重建

缺点：在整个shrink space过程中，在迁移数据的时候，在表上获取3级别和6级别的锁。

总结

move不能降低高水位的原因是不能修改rowid，而 alter table shrink space 是可以修改rowid的
move表后index的状态是UNUSABLE的,需要进行rebuild
shrink在移动行数据时，也一起维护了index上相应行的数据rowid的信息，当然shrink过程中用来维护index的成本也会比较高。
move使用中会一直有6的锁；shrink space分数据重组和HWM调整2个阶段,第一个阶段只会有3的锁,第二个阶段有6的锁
move+rebuild index效率要高，shrink时间太久，产生归档更多

二、使用shrink space整理过程

segment shrink分为两个阶段：

数据重组(compact):通过一系列insert、delete操作，将数据尽量排列在段的前面。在这个过程中需要在表上加RX锁，即只在需要移动的行上加锁。由于涉及到rowid的改变，需要enable row movement.同时要disable基于rowid的trigger.这一过程对业务影响比较小。
HWM调整：第二阶段是调整HWM位置，释放空闲数据块。此过程需要在表上加X锁，会造成表上的所有DML语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。

1.找出碎片率高的表(>50%)

主要思路

SELECT table_name,
       ROUND((blocks * 8/1024), 2) "高水位空间 M",
       ROUND((num_rows * avg_row_len / 1024/1024), 2) "真实使用空间 M",
       ROUND((blocks * 10 / 100) * 8, 2) "预留空间(pctfree) M",
       ROUND((blocks * 8 - (num_rows * avg_row_len / 1024) -blocks * 8 * 10 / 100), 2) "浪费空间 M",
       ((blocks * 8-(num_rows * avg_row_len / 1024))/1024)/(blocks * 8/1024) "浪费空间 %"
  FROM user_tables
 WHERE table_name = 'table_name';

在高水位和真实使用的空间之间的差距=浪费空间，而产生浪费空间的原因是高水位的上涨，真实使用的空间变小（大量的delete）而造成的，而这样也会产生大量的碎片。浪费空间 %大于25就需要整理了。

如果以上没有结果,可能是表没有统计信息,可以先收集表的统计信息

这个分析很耗时间可以设定estimate_percent来调整分析的量:
exec dbms_stats.gather_table_stats(user,'table_name',CASCADE=>TRUE);

生产环境可以使用下面的语句一次性查出需要做碎片整理的表：

col OWNER for a10;
col TABLE_NAME for a20;
select * from (select t.owner owner,
t.table_name table_name,
       trunc(s.blocks*ts.block_size/1024/1024*1000)/1000 ssize,
TRUNC((num_rows*avg_row_len)*1000/((100-pct_free-5)/100)/1024/1024)/1000 est_size, trunc((s.blocks*ts.block_size)/((num_rows*avg_row_len)/((100-pct_free-5)/100))*100)/100 sp,
       last_analyzed,
       num_rows,
       avg_row_len,
       pct_free,
       s.blocks
  from dba_tables t,
      (SELECT owner,segment_name,sum(blocks) blocks from dba_segments
      where
       owner not in ('SYS','OUTLN','SYSTEM','MGMT_VIEW','SYSMAN','DBSNMP','WMSYS','XDB',
      'DIP','GOLDENGATE','CTXSYS' )
      group by owner ,segment_name )S,
    dba_tablespaces ts
  where
      t.tablespace_name=ts.tablespace_name and
      t.owner=s.owner and t.table_name=s.segment_name and
      t.num_rows*avg_row_len >0 and
      t.owner not in ('SYS','OUTLN','SYSTEM','MGMT_VIEW','SYSMAN','DBSNMP','WMSYS','XDB',
      'DIP','GOLDENGATE','CTXSYS' ) and
      S.blocks>200 and
       t.last_analyzed is not null)
  where sp >=50 order by 3;

2.开启行迁移

alter table table_name enable row movement;

3.进行碎片整理

alter table table_name shrink space cascade; # 加cascade可以连带重建索引

连同索引一起压缩，比move方便。

4.再根据实际情况关闭行迁移

alter table table_name disable row movement;

注意事项

开启/关闭行迁移的两个命令都会使表相关联的对象变为无效，操作结束后别忘记重新编译,以防万一，虽然程序下次运行的时候会自动编译，但总是会担心，还是编译掉，确认对象都是有效的比较OK.
shrink space compact只执行第一个阶段。
如果系统业务比较繁忙，可以先执行shrink space compact重组数据,然后在业务不忙的时候再执行shrink space降低HWM释放空闲数据块。
shrink必须开启行迁移功能
在表上建有函数索引（包括全文索引）shrink space会失败

表碎片分析与整理