oracle 表的碎片化整理

数据库 waitig 789℃ 百度已收录 0评论

数据库在日常的使用过程中,不断的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释放空闲数据块。


本文由【waitig】发表在等英博客
本文固定链接:oracle 表的碎片化整理
欢迎关注本站官方公众号,每日都有干货分享!
等英博客官方公众号
点赞 (0)分享 (0)