Oracle ACS资深顾问罗敏老罗技术核心感悟：关于数据库碎片管理

关于数据库碎片管理

各行各业的数据库容量急剧增长是目前IT系统一个普遍的现象，除去正常业务增长因素，大量碎片的存在是导致这种现象的重要因素。其实，大多数DBA们都深知碎片的存在和带来的危害，但大家却很少进行计划性、系统化的碎片整理。为什么呢？通常的原因是：不了解如何评估碎片的严重性；不知道Oracle提供了哪些技术手段；业务系统高可用性太高，也不敢以在线方式进行碎片的整理… …

本章就将围绕碎片管理这个话题展开专题讨论，包括在表空间、表、索引等不同级别和对象方面进行碎片指标的介绍，以及Oracle公司提供的多种碎片整理技术，最后将结合一个案例，介绍碎片管理的实施过程。

希望本章能为大家在数据库空间管理方面起到一个抛砖引玉的作用，更希望各行各业有更多的客户能将碎片管理作为数据库运维中一项制度化、常态化的工作。

数据库空间碎片问题

10GB被压缩到1GB!

若干年前作为Oracle技术顾问，本人非常有幸参与了国家某基础数据库的建设工作。当2012年再次拜访该客户时，我向客户DBA询问了该库的最新容量，他告诉我已经增长到十几个TB了，而且说已经在考虑如何对数据库进行历史数据迁移等瘦身计划。凭借经验，我认为除了正常的业务高速增长因素之外，数据库一定存在大量碎片。于是，我让客户先查查碎片情况，别着急马上实施工程庞大的数据库瘦身计划。

果不其然，几天之后，突然接到客户DBA电话：

“罗老师，我们按你推荐的方法对系统进行了一次碎片分析，并对一张10GB的大表通过重建方式，进行了碎片整理，结果被压缩到1GB！”。

“哈哈，按照这个压缩比例，你们的库可能被压到几个TB，甚至1个TB”。我在电话里也不无得意道。

是的，根据个人对国内多个行业Oracle数据库系统的观察，大多数核心数据库空间在急剧膨胀。例如，以下就是某银行一个关键业务系统数据库空间增长情况和趋势分析：

排除正常业务增长因素，大量碎片的存在是空间急剧膨胀的重要根源。而现状是大多数DBA们很少进行系统的碎片分析，更没有计划性、周期性、常态化地进行空间碎片整理了。于是，我们听到的更多是空间不断增长和性能下降的抱怨，还有每年不断增加的存储采购。

何谓碎片

客户经常会遇到这种问题：明明 dba_free_space等视图显示数据库有足够的空闲空间，但却遇到ora-1547、ora-1562、ora-1653等表示无法分配空间或空间不足的错误。通常而言，这就是遇到空间碎片问题了。

Oracle在表空间、表、索引等不同层面和对象方面都可能存在碎片。在表空间级，如果频繁进行创建、删除表和索引等DDL操作，会导致大量碎片。表空间级碎片通常有两种：一种是不连续的空闲空间，导致Oracle无法分配一个完整的extent，Oracle也无法对这种不连续的空闲空间进行合并（coalesce）操作。另一种是空闲空间相邻，Oracle SMON进程会定期进行合并（coalesce）操作，在一定程度上可以缓解碎片问题。

在表级，大量DML操作会导致大量碎片，特别是进行大量Delete操作之后，又进行大量Insert 操作，很容易产生碎片，这是因为Oracle的Delayed block cleanout特性所导致。所谓Delayed block cleanout特性，是指当对表进行DML操作之后，Oracle只会先将相应的数据块标识为修改状态，而不会立即真正在磁盘上执行这些操作，除非马上访问（Select）这些数据块。这样，当表的记录被Delete，空间可能还没有被真正释放，马上进行Insert操作，Oracle将不会将新记录插入到刚被Delete记录所占的空间，而是插入到新的extent之中，这样将导致表的空间急剧增长。

同样地，大量DML操作也会导致索引产生大量碎片，而且在原理上索引更容易产生碎片。例如，由于无法指定索引的PCTUSED属性，这样当一条记录被删除后，Oracle并不会重用该记录所对应的索引空间。而且针对update操作，Oracle在索引上实际是进行Delete和Insert和操作，非常容易产生碎片。

空间碎片带来的问题和风险

空间浪费

首先，碎片带来的问题是空间的无谓浪费，导致客户在存储设备方面进行了很多不必要的扩容和投入。例如，根据我们的初步估算，如果技术方案和实施手段得当，上述银行系统至少能节省出1/3以上的空间。

访问效率的低下

无论是联机交易应用，还是批处理应用，大量碎片的存在，都将导致访问效率低下，甚至极度恶化。如果是联机交易应用，由于索引碎片较多，将导致索引访问效率下降，极大影响以索引访问为主的联机交易应用性能。如果是后台批处理应用，由于表的碎片较多，特别是碎片表的高水位标志（HWM）较高，而实际记录数并不多，将导致以全表扫描、全分区扫描为显著特征的批处理应用效率低下，并且浪费大量内存和I/O资源。大量碎片的存在，也将导致RMAN备份和逻辑备份、统计信息采集等后台作业效率低下。

空间管理需求分析

如何有效加强各系统的空间管理，特别是合理运用Oracle相关技术有效解决碎片问题，将不仅是数据库系统目前急待解决的问题，而且也是客户运维团队一项长期、艰巨的工作任务。

以下是我们在日常工作中与客户运维团队的沟通中，了解到客户针对空间管理，特别是在碎片管理方面提出的需求：

合理评估空间问题

首先，希望Oracle服务团队在表空间、表、索引等不同层面和不同数据对象方面，提供评估空间问题，特别是碎片问题的合理方法、指标和评估标准。

合理选择技术方案

其次，客户希望Oracle服务团队在Oracle众多空间管理，特别是碎片管理方面的技术手段中，结合实际系统特点，在技术有效性、对生产系统影响、技术成熟性等方面进行综合平衡，提出针对表空间、表、索引等不同层面和不同数据对象的碎片管理技术方案。

合理的实施计划

在上述技术方案基础上，根据客户系统特点，制定合理的的实施计划，以适当的资源投入，达到既合理解决空间问题，同时又能将对生产系统的影响降至最小等目的。

实施效果评估分析

在上述实施计划基础上，选择典型系统实施之后，希望Oracle服务团队能进行空间管理，特别是碎片整理的整体效果评估，以及具体的量化比较和分析，评估内容包括：碎片整理的有效性、技术运用手段的合理性、对生产系统的影响等内容。

空间管理的制度化和常态化建议

随着业务的不断发展，数据库系统出现空间问题，特别是碎片问题是不可避免的问题。客户希望Oracle服务团队在现有空间管理技术方案和实施基础上，不断完善技术方案，并总结实施经验，在数据库系统的空间管理制度化和常态化方面，提供建设性的建议，协助客户建立空间管理的一整套制度和方法。

碎片评估方法

表空间碎片评估方法

事实上，Oracle提供了多种方法和计算公式来评估表空间碎片情况。以下介绍几种：

FSFI值

以下语句将按表空间计算FSFI（Free Space Fragmentation Index）值：

select a.tablespace_name,
       sqrt(max(a.blocks) / sum(a.blocks)) * (100 / sqrt(sqrt(count(a.blocks)))) FSFI
  from dba_free_space a, dba_tablespaces b
  where a.tablespace_name = b.tablespace_name
 and b.contents not in ('TEMPORARY','UNDO')
 group by a.tablespace_name
 order by FSFI;

如果FSFI值 < 30%，则该表空间的碎片较多。

按表空间显示连续的空闲空间

运行如下的Oracle公司提供的脚本，可按表空间显示连续的空闲空间：

========

Script : tfstsfgm

========

SET ECHO off 

REM NAME:TFSTSFRM.SQL 

REM USAGE:"@path/tfstsfgm" 

REM ------------------------------------------------------------------------ 

REM REQUIREMENTS: 

REM    SELECT ON DBA_FREE_SPACE 

REM ------------------------------------------------------------------------ 

REM PURPOSE: 

REM    The following is a script that will determine how many extents 

REM    of contiguous free space you have in Oracle as well as the  

REM total amount of free space you have in each tablespace. From  

REM    these results you can detect how fragmented your tablespace is.  

REM   

REM    The ideal situation is to have one large free extent in your  

REM    tablespace. The more extents of free space there are in the  

REM    tablespace, the more likely you  will run into fragmentation  

REM    problems. The size of the free extents is also  very important.  

REM    If you have a lot of small extents (too small for any next   

REM    extent size) but the total bytes of free space is large, then  

REM    you may want to consider defragmentation options.  

REM ------------------------------------------------------------------------ 

REM DISCLAIMER: 

REM    This script is provided for educational purposes only. It is NOT  

REM    supported by Oracle World Wide Technical Support. 

REM    The script has been tested and appears to work as intended. 

REM    You should always run new scripts on a test instance initially. 

REM ------------------------------------------------------------------------ 

REM Main text of script follows: 




create table SPACE_TEMP (   

 TABLESPACE_NAME        CHAR(30),   

 CONTIGUOUS_BYTES       NUMBER)   

/   




declare   

  cursor query is select *   

          from dba_free_space   

                  order by tablespace_name, block_id;   

  this_row        query%rowtype;   

  previous_row    query%rowtype;   

total           number;   




begin   

  open query;   

  fetch query into this_row;   

  previous_row := this_row;   

  total := previous_row.bytes;   

  loop   

 fetch query into this_row;   

     exit when query%notfound;   

     if this_row.block_id = previous_row.block_id + previous_row.blocks then   

        total := total + this_row.bytes;   

        insert into SPACE_TEMP (tablespace_name)   

                  values (previous_row.tablespace_name);   

     else   

        insert into SPACE_TEMP values (previous_row.tablespace_name,   

               total);   

        total := this_row.bytes;   

     end if;   

previous_row := this_row;   

  end loop;   

  insert into SPACE_TEMP values (previous_row.tablespace_name,   

                           total);   

end;   

.   

/   




set pagesize 60   

set newpage 0   

set echo off   

ttitle center 'Contiguous Extents Report'  skip 3   

break on "TABLESPACE NAME" skip page duplicate   

spool contig_free_space.lis   

rem   

column "CONTIGUOUS BYTES"       format 999,999,999,999   

column "COUNT"                  format 999999   

column "TOTAL BYTES"            format 999,999,999,999   

column "TODAY"   noprint new_value new_today format a1   

rem   

select TABLESPACE_NAME  "TABLESPACE NAME",   

       CONTIGUOUS_BYTES "CONTIGUOUS BYTES"   

from SPACE_TEMP   

where CONTIGUOUS_BYTES is not null   

order by TABLESPACE_NAME, CONTIGUOUS_BYTES desc;   




select tablespace_name, count(*) "# OF EXTENTS",   

         sum(contiguous_bytes) "TOTAL BYTES"    

from space_temp   

group by tablespace_name;   




spool off   




drop table SPACE_TEMP   

/   

===================================

Sample output from the above script:

===================================

Contiguous Extents Report 







TABLESPACE NAME        CONTIGUOUS BYTES 

------------------------------ ---------------- 

RBS                 52,426,752 

RBS                 2,662,400 

RBS                  798,720 

RBS                   266,240 




... 




TABLESPACE NAME         CONTIGUOUS BYTES 

------------------------------ ---------------- 

USERS                  20,480 

USERS                  16,384 

USERS                  10,240 

USERS                  10,240 

USERS                  10,240 

USERS                   4,096 







TABLESPACE_NAME        # OF EXTENTS  TOTAL BYTES 

------------------------------ ------------ ------------ 

RBS                     15   56,154,112 

SYSTEM                   10     927,744 

TEMP                   5     665,600 

TOOLS                  10   89,397,248 

USERS                   6      71,680

如脚本中的注释所言，一个表空间的extent数量越多，并且extent都比较小，则该表空间碎片现象比较严重。

表碎片评估方法

在表级，Oracle也提供了多种方法和计算公式来评估表的碎片情况。以下介绍一种按统计信息进行评估和分析的方法：

col frag format 999999.99
col owner format a30;
col table_name format a30;
select * from (
select a.owner,
 a.table_name,
 a.num_rows,
 a.avg_row_len * a.num_rows,
 sum(b.bytes),
 (a.avg_row_len * a.num_rows) / sum(b.bytes) frag
 from dba_tables a, dba_segments b
 where a.table_name = b.segment_name
and a.owner= b.owner
 and a.owner not in
 ('SYS', 'SYSTEM', 'OUTLN', 'DMSYS', 'TSMSYS', 'DBSNMP', 'WMSYS',
 'EXFSYS', 'CTXSYS', 'XDB', 'OLAPSYS', 'ORDSYS', 'MDSYS', 'SYSMAN')
 group by a.owner,a.table_name,a.avg_row_len, a.num_rows
 having a.avg_row_len * a.num_rows / sum(b.bytes) < 0.7
 order by sum(b.bytes) desc)
where rownum <= 100;

上述语句将以倒排序方式，显示碎片率最高的前100个表。上述方法主要基于相关统计信息进行，不排除由于统计信息采集和更新不及时而导致的误差。

索引碎片评估方法

Oracle在索引碎片分析方面提供了多种方法和计算公式。以下介绍两种：

按BLEVEL分析

col tablespace_name format a20;
col owner format a10;
col index_name format a30; 
select id.tablespace_name,
 id.owner,
 id.index_name,
 id.blevel,
 sum(sg.bytes) / 1024 / 1024,
 sg.blocks,
 sg.extents
 from dba_indexes id, dba_segments sg
 where id.owner = sg.owner
 and id.index_name = sg.segment_name
 and id.tablespace_name = sg.tablespace_name
 and id.owner not in
 ('SYS', 'SYSTEM', 'USER', 'DBSNMP', 'ORDSYS', 'OUTLN')
 and sg.extents > 100
 and id.blevel >= 3
 group by id.tablespace_name,
 id.owner,
 id.index_name,
 id.blevel,
 sg.blocks,
 sg.extents
having sum(sg.bytes) / 1024 / 1024 > 100;

上述语句将显示索引高度Blevel >=3，并且索引大小超过100M的索引。

Analyze index方法

analyze index <Index_name> validate structure;

select DEL_LF_ROWS * 100 / decode(LF_ROWS, 0, 1, LF_ROWS) PCT_DELETED from index_stats;

上述语句中PCT_DELETED的含义为索引被删除项与索引项总数的所占比例，如果PCT_DELETED>=20%，则说明该索引碎片严重。

由于上述analyze语句会对被分析索引产生锁，即在生产系统运行会产生一定影响，因此我们建议优先考虑按BLEVEL分析方法。

Automatic Segment Advisor

简介

Oracle从10g开始推出针对空间进行自动分析的工具：Automatic Segment Advisor。该工具通过分析AWR数据中相关数据段的空间使用情况和增长统计信息，以及取样数据，分析数据段的可回收空间。该工具缺省情况下将在数据库的维护窗口自动运行，也可根据客户需要手工运行。限于篇幅，我们在此不讨论手工运行方式的详细内容了。

Automatic Segment Advisor可提供如下具体建议：

如果发现数据数据段有大量空闲空间，并且是ASSM（Automatic Segment Space Management）管理模式，则推荐采用shrink技术。如果无法使用shrink技术，如不是ASSM管理模式，则推荐采用在线重定义技术（online table redefinition）。
如果发现某些表可以通过OLTP等压缩算法进行压缩，使得空间大大节省，该工具将提供相应的建议。
如果发现一个表的chain row超过一定阀值（Threshold），将相关建议记录在相关数据字典之中。

查看Automatic Segment Advisor分析结果

通过以下一些脚本，可查看Automatic Segment Advisor分析结果，例如以下是查看Finding结果：

/*+ 查看Automatic Segment Advisor的Finding结果 */
select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message 
 from dba_advisor_findings af, dba_advisor_objects ao
 where ao.task_id = af.task_id
 and ao.object_id = af.object_id

TASK_NAME SEGNAME PARTITION TYPE MESSAGE
------------------ ------------ --------------- ---------------- --------------------------
Manual_Employees EMPLOYEES TABLE The free space in the obje
 ct is less than 10MB.
 
Manual_Salestable4 SALESTABLE4 SALESTABLE4_P1 TABLE PARTITION Perform shrink, estimated
 savings is 74444154 bytes.
 
Manual_Salestable4 SALESTABLE4 SALESTABLE4_P2 TABLE PARTITION The free space in the obje
 ct is less than 10MB.

/*+ 只查询可以进行shrink操作的对象 */
select f.impact, o.type, o.attr1, o.attr2, f.message, f.more_info
 from dba_advisor_findings f, dba_advisor_objects o
 where f.object_id = o.object_id
 and f.task_name = o.task_name
 and f.message like '%shrink%'
 order by f.impact desc;

以下脚本查看Recommendations结果：

/*+ 查看Automatic Segment Advisor的Recommendations结果 */
select tablespace_name, segment_name, segment_type, partition_name,
recommendations, c1 from
table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));


TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------------------ --------------
PARTITION_NAME
------------------------------
RECOMMENDATIONS
-----------------------------------------------------------------------------
C1
-----------------------------------------------------------------------------
TVMDS_ASSM ORDERS1 TABLE PARTITION
ORDERS1_P2
Perform shrink, estimated savings is 57666422 bytes.
alter table "STEVE"."ORDERS1" modify partition "ORDERS1_P2" shrink space

上述脚本不仅告诉客户哪些表、索引存在碎片，而且还预估能回收多少空间，甚至还将产生推荐的空间回收语句，例如：alter table “STEVE”.”ORDERS1″ modify partition “ORDERS1_P2” shrink space。

建议根据Auatomatic Segment Advisor工具的分析结果，并结合上述手工脚本产生的结果进行综合评估，确定需要进行碎片整理的表、索引等对象清单和实施策略。

碎片整理方法

表空间级碎片整理方法

技术简介

Oracle可通过如下命令进行表空间相邻空闲空间的压缩（Coalesce），达到碎片整理的目的：

SQL> alter tablespace <表空间名> Coalesce;

技术特点

该技术主要针对传统的基于字典管理的表空间，而在基于位图管理的本地化管理的表空间中，Oracle可自动进行相邻空闲空间的压缩。

表碎片整理方法1：数据exp/imp及Data Pump技术

技术简介

Oracle的传统技术Exp/Imp，以及10g开始提供的增强版Data Pump技术，可将存在碎片的表和索引进行数据逻辑卸出，再通过drop或truncate该表，以及数据加载操作，将该表重新创建为一个物理结构紧凑的表和索引，从而达到表和索引碎片压缩的目的。

技术特点

数据exp/imp及Data Pump技术的特点是时间较长，而且在数据卸出和加载期间，为保证数据逻辑一致性，访问该表的应用将被停止。另外，访问该表的索引也需要重新创建。该类技术不需要数据库额外的空间，但需要一定的文件系统空间。

表碎片整理方法2：CTAS技术

技术简介

CTAS技术即如下技术：

SQL> create table <新表名> as select … from <旧表名> …
SQL> drop table <旧表名>;
SQL> rename table <新表名> to <旧表名>;

该技术除了没有将数据卸出和加载之外，其它方面与上述exp/imp及Data Pump技术原理相当。

技术特点

与exp/imp及Data Pump技术特点相当，但需要额外的数据库空间。

表碎片整理方法3：Move Tablespace技术

技术简介

Move Tablespace技术即：

SQL> alter table <表名> move tablespace <表空间名>;

技术特点

相比上述exp/imp、Data Pump和CTAS技术，Move Tablespace技术更快，而且该表的索引并没有删除，但索引状态将变为INVALID，需要重建（rebuild）。其它方面技术特点与上述技术相当，但不需要额外的数据库空间。

表碎片整理方法4：Shrink技术

技术简介

上述传统的碎片整理技术，均需要业务停顿。相比之下，Oracle 10g开始提供的段收缩（segment shrink）技术，则具有明显优势。以下是该技术示意图：

即shrink技术可将表和索引的高水位（HWM）下的碎片进行有效压缩，并将高水位（HWM）进行回退。该技术基本语法如下：

SQL> alter table <表名> enable row movement;
SQL> alter table <表名> shrink space cascade; --- 压缩表及相关数据段并下调HWM
SQL> alter table <表名> shrink space compact; --- 只压缩数据不下调HWM
SQL> alter table <表名> shrink space; --- 下调HWM;

即该技术可一次性压缩数据并下调HWM，也可分为两个阶段进行：第一阶段，在业务高峰时，只压缩数据但不下调HWM。第二阶段，在业务非高峰时，再下调HWM，并释放空闲空间。

技术特点

该技术的特点和优势如下：

可以在线进行，不影响正常应用访问。
只能在ASSM（Automatic Segment Space Managment）管理的表空间上进行。该类表空间也必须是基于本地管理模式的。
shrink操作适合于如下数据段
普通堆表和IOT表
索引
分区和子分区
物化视图和物化视图日志
在对表进行shrink操作时，对应的索引自动进行维护
在对表进行shrink操作时，不会触发相应的触发器
不需要额外的空间

表碎片整理方法5：Online Redefinition技术

技术简介

Oracle在9i R2之后，提供了一个新的在线重新构造和重新定义表结构的功能：DBMS_REDEFINITION。该技术在几乎不中断业务的情况下，通过创建一个中间表，并通过内部机制，保证原表与中间表的数据同步，最后通过一个切换操作，完成表结构的在线重新定义。

以下是其原理图：

以下给出一个通过该方法进行碎片整理的简单例子：

1．检查HR.EMPLOYEES是否具有按主键进行Online Redefinition能力
BEGIN
 DBMS_REDEFINITION.CAN_REDEF_TABLE('hr','employees',
 DBMS_REDEFINITION.CONS_USE_PK);
END;
/

2．创建一张与HR.EMPLOYEES同结构的临时表HR.INT_EMPLOYEES
create table hr.int_employees tablespace users as select * from hr.employees where 1=2;

3．对HR.EMPLOYEES表启动Online Redefinition
BEGIN
 DBMS_REDEFINITION.START_REDEF_TABLE('hr', 'employees','int_employees',
 '',dbms_redefinition.cons_use_pk);
END;
/

4．复制该表的索引、限制、触发器等依赖对象
DECLARE
num_errors PLS_INTEGER;
BEGIN
 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('hr', 'employees','int_employees',
 DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/

5．检查是否有除Primary、constraints之外的错误
select object_name, base_table_name, ddl_txt from
 DBA_REDEFINITION_ERRORS;

6．手工同步数据，该步骤为可选操作
BEGIN 
 DBMS_REDEFINITION.SYNC_INTERIM_TABLE('hr', 'employees','int_employees');
END;
/

7．完成Online Redefinition操作
BEGIN
 DBMS_REDEFINITION.FINISH_REDEF_TABLE('hr', 'employees','int_employees');
END;
/

除碎片管理之外，联机在线重定义技术还具有广泛的应用空间，例如：

修改普通表或Cluster表的存储参数。
将普通表或Cluster表移动到新的表空间。
增加、修改和删除字段。
将普通表转换为分区表，或反之。
修改分区结构
针对单个分区，修改物理属性。例如，将指定分区移动到新的表空间。
为并行查询语句增加支持。
修改物化视图日志表或Streams Advanced Queue表的物理属性。
重建普通表或Cluster表，从而减少碎片。
将普通表转换为按索引组织的表（IOT），或反之。
将普通关系表转换为包含对象字段的表，或反之。
将面向对象的表转换为普通关系表或包含对象字段的表，或反之。
技术特点

Online Redefinition与shrink功能相似，该技术的最大特点是在不中断相关业务情况下，可联机在线进行碎片整理。而且根据Oracle实施经验，以及其它客户实施经验，由于Online Redefinition技术进行的碎片整理是重建该表，因此相比shrink技术，碎片整理效果更明显，但需要消耗一定的数据库空间，而且技术实施比shrink复杂。

索引碎片整理方法1：索引Rebuild和Coalesce技术

技术简介

为有效降低索引碎片，Oracle提供了Rebuild和Coalesce两种技术，例如：

SQL> alter index <索引名> rebuild online parallel 4 nologging;

SQL> alter table <索引名> coalesce;

即Rebuild Index可以在线、并行、不产生日志方式进行。

技术特点

以下是Oracle总结的两种技术特点和差异性：

Rebuild Index	Coalesce Index

可快速将索引迁移到新的表空间	不能够将索引迁移到新的表空间
更高的成本：需要额外空间	更低的成本：不需要额外空间
创建新索引树，压缩索引高度	压缩同一个索引枝下的叶节点空间
在不需要删除索引情况下，可快速修改索引存储和表空间参数	快速释放叶节点空间

由于Rebuild Index在碎片整理方面优点更多，因此，在空间足够的情况下，建议尽量采用Rebuild Index技术。

索引碎片整理方法2：Shrink技术

技术简介

Shrink技术同样适合于索引数据的压缩。技术原理实际上等同于Coalesce Index。

技术特点

具有上述Coalesce Index的技术特点。

技术手段比较

以下是综合上述各种技术手段原理、特点而进行的比较分析，其中技术特点着重在能否在线、空间需求，以及技术复杂度三个方面。

序号	技术手段	适应场景	在线	空间需求	技术复杂度	说明

1.	表空间Coalesce	表空间	是	不需要	低	只适合于字典管理表空间
2.	数据exp/imp及Data Pump技术	表	否	需要	高	需要文件系统空间
3.	CTAS技术	表	否	需要	低
4.	Move Tablespace技术	表	否	不需要	低
5.	Shrink技术	表、索引	是	不需要	中等
6.	Online Redefinition技术	表	是	需要	高
7.	Rebuild Index	索引	是	需要	低
8.	Coalesce Index	索引	否	不需要	低

技术手段推荐

以下是根据上述技术原理及特点，并结合某项目具体需求和特点，我们建议采取的技术实施思路：

由于该项目已经全部采用本地化表空间，因此无需采用表空间级碎片整理技术。
针对表的碎片整理，优先考虑shrink技术。
针对索引的碎片整理，优先考虑Rebuild Index技术。
如果shrink技术实施效果不理想，或者有一定局限性，则采用Online Redefinition技术。
如果因空间不够，导致Rebuild Index技术无法实施，再考虑Coalesce Index技术。

实施策略、组合和流程

碎片问题不仅造成空间浪费，而且极大地影响性能。碎片整理的上述技术其实不太复杂，但毕竟碎片整理是对生产系统数据展开的，而且现在关键应用系统的高可用性要求都非常高。因此，如何有效地展开碎片整理技术方案的制定，特别是选择测试和试点系统就尤显重要。以下就是针对某客户的碎片管理需求，以及Oracle相关技术特点，我们建议采取的实施策略：

测试和试点系统选择

首先建议选择典型系统进行测试和试点。例如可考虑选择碎片较严重，高可用性略低的系统，如非联机交易型历史数据查询等系统。

如有可能，先期在试点系统的测试演练环境进行。但根据我们了解，测试演练环境与生产系统虽然数据库逻辑结构相同，但基本上是通过Exp/Imp从生产系统构造的系统，因此测试演练环境的物理结构，特别是碎片情况与生产系统大不一样。另外，测试演练环境更缺乏生产系统真实的DML操作。因此，即便在演练环境进行了测试工作，直接在生产系统实施碎片整理，仍然将面临一定的难度和风险。

技术手段

针对表的碎片整理，优先考虑shrink技术。
针对索引的碎片整理，优先考虑Rebuild Index技术。

版本和补丁问题

与shrink相关的Bug

上述主要运用的shrink技术是10g新特性，建议在实施之前，对shrink本身可能存在的问题提前进行分析和防范。

以下是11.2.0.4 patchset中修复的与shrink技术相关的Bug：

6653934	Dump / block corruption from ONLINE segment shrink with ROWDEPENDENCIES
6761624	ALTER INDEX .. SHRINK SPACE can deadlock with concurrent sessions
5215712	OERI [kcbrbrl_1] on table shrink space cascade in ASSM
5768710	ALTER TABLE SHRINK slow with LOB
6494048	Dump [ktsk_get_prevl2] from ALTER INDEX .. SHRINK SPACE

可见，在该项目大量现有10.2.0.4版本上，还可能包含上述与shrink相关的Bug。而在10.2.0.5之上的PSU 3中，没有再发现修复的与shrink相关的Bug。

因此，在该项目碎片管理方案实施中，建议考虑在已经升级到10.2.0.5版本的系统上进行。如果欲在10.2.0.4系统进行，建议先升级到10.2.0.5之后，再实施shrink技术。

与Rebuild Index相关的Bug

以下是10.2.0.5 patchset中修复的与Rebuild Index技术相关的Bug：

7329252+	ORA-8102/ORA-1499/OERI[kdsgrp1] Index corruption after rebuild index ONLINE
3611750	ORA-1450 from online rebuild of index
7326645	ORA-903 / ORA-933 / ORA-911 during ONLINE index creation/rebuild
7697802	Bitmap index rebuilds slower with larger pga_aggregate_target
6767655	Dump [kauxs_do_journal] from concurrent DML while rebuilding index ONLINE
4598439	DBMS_STATS.DELETE_SCHEMA_STATS does not delete index stats after index rebuild
6674374	Optimize rebuild of Text index can fail with concurrent DML

可见，在该项目大量现有10.2.0.4版本上，还可能包含上述与Rebuild Index相关的Bug。而在10.2.0.5之上的PSU 3中，没有再发现修复的与Rebuild Index相关的Bug。

同样理由，建议在该项目碎片管理方案实施中，考虑在已经升级到10.2.0.5版本的系统上进行。如果欲在10.2.0.4系统进行，建议先升级到10.2.0.5之后，再实施Rebuild Index技术。

与Online Redefinition相关的Bug

以下是10.2.0.5 patchset中修复的与Rebuild Index技术相关的Bug：

6679303	ORA-932 from SELECT of LONG RAW altered to CLOB/BLOB with online redefinition
7007594	ORA-600 [12261] at DBMS_REDEFINITION.FINISH_REDEF_TABLE of partitioned table
7573151	Wrong results after online redefinition of table in RAC (with DBMS_REDEFINITION)
7257038	Using DBMS_REDEFINITION on a Streams table causes “missing streams mvdd”
6335679	Cannot use online redefinition with a disabled VPD policy on a table

可见，在该项目大量现有10.2.0.4版本上，也包含上述与Online Redefinition相关的Bug。而在10.2.0.5之上的PSU 3中，没有再发现修复的与Rebuild Index相关的Bug。

同样理由，在该项目碎片管理方案实施中，如果欲实施Online Redefinition技术，建议在已经升级到10.2.0.5版本的系统上进行。如果欲在10.2.0.4系统进行，建议先升级到10.2.0.5之后，再实施Online Redefinition技术。

最佳实践经验

实施时间点建议

尽管shrink和Rebuild Index均可以在不中断应用的情况下在线进行，但为保险起见，如果业务允许，还是尽量避免在业务高峰时进行。

Shrink技术实施建议

可考虑将shrink技术分两步进行：首先，先压缩数据但不下调HWM。其次，再下调HWM并释放空闲空间。

Rebuild Index技术实施建议

由于Rebuild Index出现问题的情况基本为ONLINE方式，因此如果业务允许，建议Rebuild Index以非ONLINE方式进行。

实施流程图

以下是综合上述各方面建议，而归纳总结的该项目碎片管理实施流程：

各阶段工作细节

以下是上述实施流程图中各阶段的工作内容和提交物：

上述方案的详细实施细节，考虑到篇幅就不在本书展开了。

15.5本章参考资料及进一步读物

本章参考资料及进一步读物：

序号	资料类别	资料名称	资料概述

1.	Oracle联机文档	《Oracle Administrator’s Guide》第18章之“Reclaiming Wasted Space”小节	该小节专门讲述了如何回收空闲空间，特别是介绍了Automatic Segment Advisor的使用。
2.	My Oracle Support	《Overview of Database Fragmentation in Oracle 7 [ID 1012431.6]》	虽然这是一篇基于Oracle 7的文档，但在数据库碎片原理方面并没有发生变化。该文档介绍的SYSTEM表空间碎片、表和索引碎片等原理，可以帮助我们了解碎片的基本原理和处理方法。
3.	My Oracle Support	《Master Note: Troubleshooting Oracle Tablespace Management (Doc ID 1522807.1)》	这是一篇有关表空间管理方面问题的资料汇集地，当然也包括表空间碎片管理的文章。
4.	My Oracle Support	《Script to Detect Tablespace Fragmentation [ID 1020182.6]》	这就是一个检测表空间碎片情况的官方脚本。
5.	My Oracle Support	《Various Aspects of Fragmentation [ID 186826.1]》	如果此文档标题翻译成中文，应该是“碎片问题面面观”，可见此文档内容的丰富性。
6.	My Oracle Support	《Script to Report Extents and Contiguous Free Space [ID 162994.1]》	这是一个检测扩展块和连续空闲空间情况的官方脚本。
7.	My Oracle Support	《Script: Computing Table Size [ID 70183.1]》	如何计算表的空间使用情况？这是Oracle通过VSIZE函数进行表的空间使用情况计算的官方脚本。
8.	My Oracle Support	《Script to Report Tablespace Free and Fragmentation (Doc ID 1019709.6)》	这是Oracle公司进行表空间碎片分析的另一个官方脚本。
9.	My Oracle Support	《Script: To Report Information on Indexes (Doc ID 1019722.6)》	这是Oracle公司进行索引碎片分析的一个官方脚本。该脚本是通过“analyze index <Index_name> validate structure”命令进行索引分析，但该命令是要锁表的，小心点！
10.	My Oracle Support	《Script to Report Space Distribution and utilization (Doc ID 135677.1)》	该文档的脚本对表空间使用情况进行了详细分析，特别是建议对Fragment输出结果高的表空间，可以进行COALESCE操作。

Oracle ACS资深顾问罗敏老罗技术核心感悟：关于数据库碎片管理

关于数据库碎片管理

数据库空间碎片问题

10GB被压缩到1GB!

何谓碎片

空间碎片带来的问题和风险

空间管理需求分析

碎片评估方法

表空间碎片评估方法

表碎片评估方法

索引碎片评估方法

Automatic Segment Advisor

碎片整理方法

表空间级碎片整理方法

表碎片整理方法1：数据exp/imp及Data Pump技术

表碎片整理方法2：CTAS技术

表碎片整理方法3：Move Tablespace技术

表碎片整理方法4：Shrink技术

表碎片整理方法5：Online Redefinition技术

索引碎片整理方法1：索引Rebuild和Coalesce技术

索引碎片整理方法2：Shrink技术

技术手段比较

技术手段推荐

实施策略、组合和流程

测试和试点系统选择

技术手段

版本和补丁问题

最佳实践经验

实施流程图

各阶段工作细节

15.5本章参考资料及进一步读物

Comments

Leave a Reply Cancel reply

Oracle ACS资深顾问罗敏 老罗技术核心感悟：关于数据库碎片管理

关于数据库碎片管理

数据库空间碎片问题

10GB被压缩到1GB!

何谓碎片

空间碎片带来的问题和风险

空间管理需求分析

碎片评估方法

表空间碎片评估方法

表碎片评估方法

索引碎片评估方法

Automatic Segment Advisor

碎片整理方法

表空间级碎片整理方法

表碎片整理方法1：数据exp/imp及Data Pump技术

表碎片整理方法2：CTAS技术

表碎片整理方法3：Move Tablespace技术

表碎片整理方法4：Shrink技术

表碎片整理方法5：Online Redefinition技术

索引碎片整理方法1：索引Rebuild和Coalesce技术

索引碎片整理方法2：Shrink技术

技术手段比较

技术手段推荐

实施策略、组合和流程

测试和试点系统选择

技术手段

版本和补丁问题

最佳实践经验

实施流程图

各阶段工作细节

15.5本章参考资料及进一步读物

Comments

Leave a Reply Cancel reply

Oracle ACS资深顾问罗敏老罗技术核心感悟：关于数据库碎片管理