ORACLE数据库段空间收缩的研究

高旭

摘 要：在基于oracle的大型業务系统中，空间碎片是不可避免的。随着ASSM表空间的出现，数据库中的碎片问题主要集中在heap表上，即段的空间管理。严重的碎片问题不仅影响sql执行效率，降低数据库的性能；还持续消耗物理空间，造成存储资源的浪费，作为DBA，应该对碎片处理给予高度重视，作为日常运维的基本工作。重建碎片化严重的表是段空间收缩的主要方法，也可以使用shrink命令进行空间收缩，oracle的EM管理器或者OEM12C云管理器中提供了段指导方案进行向导式操作，也可以通过sql查询进行手动操作。

关键词：空间碎片；统计信息；段指导；shrink

1 概述

随着IT信息化的快速发展，企业的业务系统越来越庞大，信息结构越来越复杂，在数据库某些特定的业务表中，不可避免的要进行大批量的、相当频繁的delete，insert操作，以至于数据库的物理存储结构形成大量细小的，分散的空间，这些空间碎片如果没有得到及时的收缩，会直接导致业务表不断申请新的空间，形成超大表。其所在的表空间使用率直线攀升，造成存储资源紧缺。另外，这些碎片化严重的表也会影响数据库的性能，尤其是需要进行全表扫描的SQL语句。

处理碎片化严重的表，通用方法是重建该对象或者用EXP/IMP导出导入，也可以使用move table的方法，从ORACLE 10G开始，更好的方法是利用segment advisor和shrink命令进行收缩操作。本文主要研究ORACLE 数据库空间收缩的原理与优势。

2 技术原理介绍

自动段空间管理（ASSM），它首次出现在Oracle 9i数据库里。有了ASSM，空闲空间列表freelist被位图取代，它是一个二进制的数组，能够迅速、有效地管理存储扩展和剩余区块（free block），因此能够改善分段存储本质，ASSM表空间上创建的段还有另外一个称呼叫Bitmap Managed Segments（BMB段），带有ASSM的本地管理表空间会略掉任何为PCTUSED、NEXT和FREELISTS所指定的值。注：shrink命令只适用于ASSM的表空间。

segment shrink分为两个阶段：

①数据重组（compact）：通过一系列insert、delete操作，将数据尽量排列在段的前面。在这个过程中需要在表上加RX锁，即只在需要移动的行上加锁。由于涉及rowid的改变，需要enable row movement，同时要disable基于rowid的trigger，这一过程对业务影响比较小。

②HWM调整：第二阶段是调整HWM位置，释放空闲数据块。此过程需要在表上加X锁，会造成表上的所有DML语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。

3 shrink的特性

①shrink只适用于ASSM的表空间，需要表打开row movement功能。

②shrink表同时可以维护该表上的索引。

③shrink表只在本表内移动数据，不需要多余的空间。

4 查询碎片表可以释放的空间

4.1 使用段指导

em管理器和OEM12c中都提供的段指导功能，通过向导可以以表空间或者schema为单位进行段分析，通过生成的指导方案进行空间的收缩，由于该向导对系统资源尤其是I/O影响比较大，建议在业务不忙时进行（如下图1）。

4.2 手动sql查询

①更新统计信息。

EXEC DBMS_STATS.GATHER_TABLE_STATS （'schema_name'， 'table_name'）；

②查询某用户下非分区表段碎片信息。

select d.table_name，

d.tablespace_name，

trunc（（b.blocks-d.EMPTY_BLOCKS）*8/1024） total_MB，

trunc（d.NUM_ROWS*AVG_ROW_LEN/1024/1024） used_MB，

to_char（d.LAST_ANALYZED，'YYYYMMDDHH24MI'） analyzed

from

（select owner，segment_name，blocks from dba_segments

where owner='schema_name'）b，

dba_tables d

where d.OWNER='schema_name'

and d.table_name=b.SEGMENT_NAME

and PARTITIONED='NO'；

③查询某用户下分区表段碎片信息。

select d.table_name，

b.PARTITION_NAME，

trunc（（b.locks-d.EMPTY_BLOCKS）*8/1024） Total_MB，

trunc（d.NUM_ROWS*AVG_ROW_LEN/1024/1024） Used_MB，

to_char（d.LAST_ANALYZED，'YYYYMMDDHH24MI'） analyzed

from

（select owner，segment_name，PARTITION_NAME，blocks from dba_segments

where owner='schema_name'）b，

dba_tab_partitions d

where d.table_owner='schema_name'

and b.OWNER=d.table_owner

and d.table_name=b.SEGMENT_NAME

and b.PARTITION_NAME=d.PARTITION_NAME；

5 收缩段空间

非分区表

alter table "table_name" enable row movement；

alter table "table_name" shrink space COMPACT；

alter table "table_name" shrink space；

分区表

alter table "table_name" enable row movement；

alter table "table_name" modify partition "parttion_name" shrink space COMPACT；

alter table "table_name" shrink space；

6 案例说明

测试中对test1表插入大量数据，导致表空间ZHYU不断增长，空间空闲率非常低，然后对test1做频繁删除，插入工作，从而查处test1的段占用空间和实际数据量存在很大差距，通过段收缩，将浪费的空间释放到ZHYU，提高了ZHYU的空闲率。

6.1 插入前的表空间实用情况

6.2 建测试表，插入记录

create table test1（id number，name varchar2（30））；

begin

for i in 1..10000000

loop

insert into test1 values（i，'test'）；

end loop；

end；

/

PL/SQL procedure successfully completed.

用上面的语句创建一张大表test1，生成1千万条记录，用于测试碎片收缩原理。

6.3 反复进行删除，插入操作

执行多次删除操作：

Delete from test where rownum<3000000；

执行插入操作：

begin

for i in 1..1000000

loop

insert into test1 values（i，'test'）；

end loop；

end；

然后更新统计信息。

6.4 查询到test1的实际使用量

利用上面提到的“查询某用户下非分区表段碎片信息”语句检查当前使用率：

TABLE_NAME Tablespace Name TOTAL_MB USED_MB ANALYZED

———————————————————————————————————

TEST1 ZHYU 208 28 201503161105

可见实际只有28M数据，却占用了208M的空间。

6.5 对test1进行段收缩

SQL> alter table test1 enable row movement；

SQL> alter table test1 shrink space compact；

SQL> alter table test1 shrink space；

TABLE_NAME Tablespace Name TOTAL_MB USED_MB ANALYZED

———————————————————————————————————

TEST1 ZHYU 45 28 201503161105

经过shrink方法进行段空间收缩后，占用空间降低到45M，比之前的200多M大大降低。

6.6 表空间使用率降低

通过OEM12c查看当前表空间使用率：

7 總结

在大业务系统中，对大表频繁删除，更新操作必然造成空间的浪费，可以使用段收缩的方式压缩表的占用空间并提高sql效率，收缩操作比较费时，做好在业务不忙时或者特定的维护窗口下进行。

参考文献：

[1]Overview of Database Fragmentation in Oracle 7 （文档 ID 1012431.6）.

[2]如何执行对数据库的健康状况检查 （文档 ID 1548891.1）.