oracle 表的碎片化整理

数据库在日常的使用过程中，不断的insert，delete，update，导致表和索引出现碎片。这会导致HWM之前有很多的空闲空间，而oracle在做全表扫描的时候会读取HWM一下的所有块，这样会产生更多的IO，影响性能。

oracle提供了shrink space碎片整理的功能，对于索引要采取rebuild online的方式进行碎片整理。

实验：

1.创建一张表，数据量50w，并创建索引

SQL> show user
USER is "SCOTT"
SQL> create table helloa (id int,name char(200));

Table created.

SQL> declare
  2  i int;
  3  begin
  4  for i in 1..100000 loop
  5  insert into helloa values (i,'test');
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> create index idx_helloa_id on helloa(id);

Index created.

2.收集表的信息，查看表的空间使用情况

SQL> exec dbms_stats.gather_table_stats('SCOTT','HELLOA');

PL/SQL procedure successfully completed.

SQL> select table_name,round((blocks*8),2)/1024,round((avg_row_len*num_rows),2)/1024/1024 from user_tables where table_name='HELLOA';

TABLE_NAME                     ROUND((BLOCKS*8),2)/1024 ROUND((AVG_ROW_LEN*NUM_ROWS),2)/1024/1024
------------------------------ ------------------------ -----------------------------------------
HELLOA                                        22.984375                                19.6456909

3.删除部分记录，并重新收集表的信息，发现高水位线未变，但是块使用率下降了

SQL> exec dbms_stats.gather_table_stats('SCOTT','HELLOA');

PL/SQL procedure successfully completed.

SQL> select table_name,round((blocks*8),2)/1024,round((avg_row_len*num_rows),2)/1024/1024 from user_tables where table_name='HELLOA';

TABLE_NAME                     ROUND((BLOCKS*8),2)/1024 ROUND((AVG_ROW_LEN*NUM_ROWS),2)/1024/1024
------------------------------ ------------------------ -----------------------------------------
HELLOA                                        22.984375                                9.82304192

4.对表进行碎片整理，重新收集统计信息，看结果有显著效果

SQL> alter table helloa enable row movement;
a    
Table altered.

SQL>alter table helloa shrink space;

Table altered
SQL> exec dbms_stats.gather_table_stats('SCOTT','HELLOA');

PL/SQL procedure successfully completed.

SQL> select table_name,round((blocks*8),2)/1024,round((avg_row_len*num_rows),2)/1024/1024 from user_tables where table_name='HELLOA';

TABLE_NAME                     ROUND((BLOCKS*8),2)/1024 ROUND((AVG_ROW_LEN*NUM_ROWS),2)/1024/1024
------------------------------ ------------------------ -----------------------------------------
HELLOA                                        11.515625                                9.8230419

为什么要整理表碎片

oracle对数据段的整理有一个高水位(HWM,high water mark)的概念。高水位是数据段中使用过和未使用过的数据块的分界线。高水位以下的数据块是曾使用过的，以上的是从未被使用或初始化过的。

当oracle进行全表扫描（FTS，full table scan）的操作时，它会读高水位下的所有数据块。如果高水位下还有很多空闲空间(碎片)，读取这些空闲数据块会降低操作的性能。

行链接和行迁移：

行链接Row Chaining：当插入数据量大的行时，如果一个block不能存放一条记录，该记录的一部分会存储到同个Extent中的其他block，这些block形成一个数据块链。

行迁移Row Migration：当update的时候导致记录长度增加了，存储的block已经满了，就会发生行迁移。oracle会迁移整行数据到一个能够存储下整行数据的block中，迁移的原始指针指向新的存放行数据的block，rowid不变。

当数据行发生链接或迁移时，对其访问将会造成I/O性能降低，因为oracle为获取这些数据行的数据，必须访问更多的数据块(data block).

表碎片导致的问题：

1）查询响应时间(尤其是全表扫描）变慢

2）产生大量行迁移

3）浪费空间

如何整理表碎片：

10G之前：

1）导出表，删除表，再倒入表
2）alter table move            --需要跟当前表一样大小的空闲空间
一般选择第二种，但是不能在线进行而且需要重建索引。

10G之后：

在10G开始，提供了一个shrink命令，需要表空间是基于自动段管理的。
1）整理表，不影响DML操作
alter table table_name enable row movement;--启动行移动功能
alter table table_name shrink space compact;--只整理碎片，不回收空间
2）重置高水位，此时不能有DML操作
alter table table_name shrink space;--整理碎片并回收空间，并调整水位线。业务少时执行
alter table table_name disable row  movement;--关闭行移动
也可以一步到位
alter table table_name shrink space;

shrink 的优势：
不需要重建索引。
可以在线操作。

注意：

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