Oracle碎片简约处理

1 概要

碎片简单理解就是在大量使用DML操作数据库时，其产生一些不能被再次使用的碎小空间，根据每种不同的碎片他们的产生也是有区别的，主要包涵一下几个层次。

|--disk-level fragmention

|----tablespace-level fragmentation

|------segment-level fragmentation

|--------block-level fragmentation

|----------row-level fragmentation

|----------index leaf block-level fragmentation

2 具体处理方式

注：所有操作使用sysdba权限操作，操作之前请将正在使用的数据库备份（切记）。

一、 方式一（SQL执行的方式结果为推荐式）

SELECT 

'Segment Advice --------------------------'|| chr(10) || 

'TABLESPACE_NAME : ' || tablespace_name || chr(10) || 

'SEGMENT_OWNER : ' || segment_owner || chr(10) || 

'SEGMENT_NAME : ' || segment_name || chr(10) || 

'ALLOCATED_SPACE : ' || allocated_space || chr(10) || 

'RECLAIMABLE_SPACE: ' || reclaimable_space || chr(10) || 

'RECOMMENDATIONS : ' || recommendations || chr(10) || 

'SOLUTION 1 : ' || c1 || chr(10) || 

'SOLUTION 2 : ' || c2 || chr(10) || 

'SOLUTION 3 : ' || c3 Advice 

FROM 

TABLE(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'))

说明：

1. 执行脚本后请查阅详情请查看数据编辑器;

2. 遇到有‘movement’sql语句的solution请优先执行。

3. 验证方式，执行如下语句：

   SELECT D.TABLESPACE_NAME,  

     SPACE || 'M' "SUM_SPACE(M)",  

/*\*04*\.  */     BLOCKS "SUM_BLOCKS",  

/*05.  */     SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",  

/*06.*/       ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'  

/*07. */         "USED_RATE(%)",  

/*08.*/       FREE_SPACE || 'M' "FREE_SPACE(M)"  /*09. */ FROM (  SELECT TABLESPACE_NAME,  /*10.    */             ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,  /*11.   */              SUM (BLOCKS) BLOCKS  /*12.   */         FROM DBA_DATA_FILES  /*13.  */      GROUP BY TABLESPACE_NAME) D,  /*14. */      (  SELECT TABLESPACE_NAME,  /*15. */                ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE  /*16.  */          FROM DBA_FREE_SPACE  /*17. */       GROUP BY TABLESPACE_NAME) F  /*18. */WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)  /*19. */UNION ALL                                                           --如果有临时表空间   /*20. */SELECT D.TABLESPACE_NAME,  /*21.*/       SPACE || 'M' "SUM_SPACE(M)",  /*22.*/       BLOCKS SUM_BLOCKS,  /*23.   */    USED_SPACE || 'M' "USED_SPACE(M)",  /*24. */      ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",  /*25. */      NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"  /*26. */ FROM (  SELECT TABLESPACE_NAME,  /*27. */                ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,  /*28.   */              SUM (BLOCKS) BLOCKS  /*29.   */         FROM DBA_TEMP_FILES  /*30. */       GROUP BY TABLESPACE_NAME) D,  /*31. */      (  SELECT TABLESPACE_NAME,  /*32.  */               ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,  /*33. */                ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE  /*34.   */         FROM V$TEMP_SPACE_HEADER  /*35. */       GROUP BY TABLESPACE_NAME) F  /*36.*/ WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)  /*37.*/ ORDER BY 1;  

二、 方式二（全面处理方式）

2.1 表空间级碎片

1. 查询系统表空间使用情况

执行如下语句：

select 'alter tablespace '||tablespace_name||' coalesce;'from (

select tablespace_name,sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) FSFI

from dba_free_space

group by tablespace_name having sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) <30 and 

tablespace_name not in('SYS','SYSTEM','DBSNMP','SYSMAN','XDB'))

在一个有着足够有效自由空间，且FSFI 值超过30的表空间中，很少会遇见有效自由空间的问题。当一个空间将要接近可比参数时，就需要做碎片整理了（DMT空间可以整理，如果是LMT就无法整理，SMON 会将相邻的自由范围自动合并）

2. 操作方式

执行上一步查询的SQL语句。

3. 收缩空闲表空间

select /*+ ordered use_hash(a,c) */

  'alter database datafile '''||a.file_name||''' resize '

   ||round(a.filesize - (a.filesize - c.hwmsize-100) *0.8)||'M;',

  a.filesize,

  c.hwmsize

from 

(

select file_id,file_name,round(bytes/1024/1024) filesize from dba_data_files

) a,

(

select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents

group by file_id) c

where a.file_id = c.file_id

  and a.filesize - c.hwmsize > 100;

执行查询出的SQL语句。

2.2 段级碎片

2.2.1  表级碎片

这里主要是对表碎片的整理与空间的收缩两方面，以达到碎片的整理目的。可通过两种方式进行操作，请根据实际需要选择。

以下两种方式选其一即可。

1. 空间收缩优于数据增长，碎片清理不彻底

a) 原理

操作项

执行语句

shrink必须开启行迁移功能。

alter table table_name enable row movement ;

保持HWM，相当于把块中数据打结实了

alter table table_name shrink space compact;

回缩表与降低HWM

alter table table_name shrink space;

回缩表与相关索引，降低HWM

alter table table_name shrink space cascade;

重新编译失效对象

SQL>@?/rdbms/admin/utlrp.sql

b) 实施步骤

i. 执行如下语句：

select 'alter table '||OWNER||'.'||TABLE_NAME||' enable row movement;<br>'||'alter table '||OWNER||'.'||TABLE_NAME||' shrink space compact; <br>'||'alter table '||OWNER||'.'||TABLE_NAME||' shrink space; <br>'||'alter table '||OWNER||'.'||TABLE_NAME||' shrink space cascade;<br>'|| 'alter table '||OWNER||'.'||TABLE_NAME||' disable row movement;' from (

SELECT OWNER, SEGMENT_NAME TABLE_NAME, SEGMENT_TYPE, 

GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) WASTE_PER,

ROUND(BYTES/1024, 2) TABLE_KB, NUM_ROWS, 

BLOCKS, EMPTY_BLOCKS, HWM HIGHWATER_MARK, AVG_USED_BLOCKS,

CHAIN_PER, EXTENTS, MAX_EXTENTS, ALLO_EXTENT_PER,

DECODE(GREATEST(MAX_FREE_SPACE - NEXT_EXTENT, 0), 0,'N','Y') CAN_EXTEND_SPACE, 

NEXT_EXTENT, MAX_FREE_SPACE,

O_TABLESPACE_NAME TABLESPACE_NAME

FROM 

(SELECT A.OWNER OWNER, A.SEGMENT_NAME, A.SEGMENT_TYPE, A.BYTES,

B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS, 

A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,

DECODE( ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0),

0, 1,

ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0)

) + 2 AVG_USED_BLOCKS,

ROUND(100 * (NVL(B.CHAIN_CNT, 0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER,

ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER,A.EXTENTS EXTENTS,

A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT NEXT_EXTENT, B.TABLESPACE_NAME O_TABLESPACE_NAME

FROM SYS.DBA_SEGMENTS A,

SYS.DBA_TABLES B,

SYS.TS$ C

WHERE A.OWNER =B.OWNER and 

SEGMENT_NAME = TABLE_NAME and

SEGMENT_TYPE = 'TABLE' AND

B.TABLESPACE_NAME = C.NAME

UNION ALL

SELECT A.OWNER OWNER, SEGMENT_NAME || '.' || B.PARTITION_NAME, SEGMENT_TYPE, BYTES,

B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS, 

A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,

DECODE( ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)))/C.BLOCKSIZE, 0),

0, 1,

ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)))/C.BLOCKSIZE, 0)

) + 2 AVG_USED_BLOCKS,

ROUND(100 * (NVL(B.CHAIN_CNT,0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER,

ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER, A.EXTENTS EXTENTS, 

A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT,

B.TABLESPACE_NAME O_TABLESPACE_NAME

FROM SYS.DBA_SEGMENTS A,

SYS.DBA_TAB_PARTITIONS B,

SYS.TS$ C,

SYS.DBA_TABLES D

WHERE A.OWNER = B.TABLE_OWNER and 

SEGMENT_NAME = B.TABLE_NAME and

SEGMENT_TYPE = 'TABLE PARTITION' AND

B.TABLESPACE_NAME = C.NAME AND

D.OWNER = B.TABLE_OWNER AND

D.TABLE_NAME = B.TABLE_NAME AND

A.PARTITION_NAME = B.PARTITION_NAME),

(SELECT TABLESPACE_NAME F_TABLESPACE_NAME,MAX(BYTES)

MAX_FREE_SPACE

FROM SYS.DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME)

WHERE F_TABLESPACE_NAME = O_TABLESPACE_NAME AND

GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0)/GREATEST(NVL(HWM, 1), 1) ), 2), 0) > 25

AND OWNER not in  ('SYS','SYSTEM','DBSNMP','SYSMAN','XDB') AND BLOCKS > 128

ORDER BY 10 DESC, 1 ASC, 2 ASC);

ii. 导出为HTML格式，执行查询出的SQL语句。

iii. SQL>@?/rdbms/admin/utlrp.sql  #SQLPlus运行

2. 数据增长优于空间收缩，碎片清理彻底

a) 原理

操作项

执行语句

整理表

alter table table_name move stroage(initial 初始大小)

整理对应表索引

alter index index_name rebuild nologging online

重新编译失效对象

SQL>@?/rdbms/admin/utlrp.sql

b) 实施步骤

i. 整理表

select 'alter table '||OWNER||'.'||TABLE_NAME||' move;' from (

SELECT OWNER, SEGMENT_NAME TABLE_NAME, SEGMENT_TYPE, 

GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) WASTE_PER,

ROUND(BYTES/1024, 2) TABLE_KB, NUM_ROWS, 

BLOCKS, EMPTY_BLOCKS, HWM HIGHWATER_MARK, AVG_USED_BLOCKS,

CHAIN_PER, EXTENTS, MAX_EXTENTS, ALLO_EXTENT_PER,

DECODE(GREATEST(MAX_FREE_SPACE - NEXT_EXTENT, 0), 0,'N','Y') CAN_EXTEND_SPACE, 

NEXT_EXTENT, MAX_FREE_SPACE,

O_TABLESPACE_NAME TABLESPACE_NAME

FROM 

(SELECT A.OWNER OWNER, A.SEGMENT_NAME, A.SEGMENT_TYPE, A.BYTES,

B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS, 

A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,

DECODE( ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0),

0, 1,

ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0)

) + 2 AVG_USED_BLOCKS,

ROUND(100 * (NVL(B.CHAIN_CNT, 0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER,

ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER,A.EXTENTS EXTENTS,

A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT NEXT_EXTENT, B.TABLESPACE_NAME O_TABLESPACE_NAME

FROM SYS.DBA_SEGMENTS A,

SYS.DBA_TABLES B,

SYS.TS$ C

WHERE A.OWNER =B.OWNER and 

SEGMENT_NAME = TABLE_NAME and

SEGMENT_TYPE = 'TABLE' AND

B.TABLESPACE_NAME = C.NAME

UNION ALL

SELECT A.OWNER OWNER, SEGMENT_NAME || '.' || B.PARTITION_NAME, SEGMENT_TYPE, BYTES,

B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS, 

A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,

DECODE( ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)))/C.BLOCKSIZE, 0),

0, 1,

ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)))/C.BLOCKSIZE, 0)

) + 2 AVG_USED_BLOCKS,

ROUND(100 * (NVL(B.CHAIN_CNT,0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER,

ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER, A.EXTENTS EXTENTS, 

A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT,

B.TABLESPACE_NAME O_TABLESPACE_NAME

FROM SYS.DBA_SEGMENTS A,

SYS.DBA_TAB_PARTITIONS B,

SYS.TS$ C,

SYS.DBA_TABLES D

WHERE A.OWNER = B.TABLE_OWNER and 

SEGMENT_NAME = B.TABLE_NAME and

SEGMENT_TYPE = 'TABLE PARTITION' AND

B.TABLESPACE_NAME = C.NAME AND

D.OWNER = B.TABLE_OWNER AND

D.TABLE_NAME = B.TABLE_NAME AND

A.PARTITION_NAME = B.PARTITION_NAME),

(SELECT TABLESPACE_NAME F_TABLESPACE_NAME,MAX(BYTES)

MAX_FREE_SPACE

FROM SYS.DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME)

WHERE F_TABLESPACE_NAME = O_TABLESPACE_NAME AND

GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0)/GREATEST(NVL(HWM, 1), 1) ), 2), 0) > 25

AND OWNER not in  ('SYS','SYSTEM','DBSNMP','SYSMAN','XDB') AND BLOCKS > 128

ORDER BY 10 DESC, 1 ASC, 2 ASC);

执行查询的SQL语句。

ii. 整理索引

select 'alter index ' || OWNER || '.' || segment_name || 'rebuild nologgin online;'from (select * from SYS.DBA_SEGMENTS where segment_type = 'INDEX');

执行查询的SQL语句。

iii. SQL>@?/rdbms/admin/utlrp.sql  #SQLPlus运行

2.2.2  索引级碎片

1. 分析索引是否有碎片

执行语句：analyze index index_name validate structure;

注：index_name可以执行语句查看：

select * from dba_segments where segment_type = 'INDEX' and owner in ('MW_APP','MW_SYS','STATDBA','DICTDBA')

a) 索引分析完成后，在index_stats查看其分析结果，执行如下语句：

select height, blocks, lf_blks, lf_rows, br_blks, br_rows  , del_lf_rows from index_stats

b) 索引整理原则如下：

i. 删除的行数如占总的行数的30%，即del_lf_rows / lf_rows > 0.3，那就考虑索引碎片整理；

ii. 如果”hight“大于4，可以考虑碎片整理；

iii. 如果索引的行数（LF_rows）远远小于‘LF_BLKS’ ，那就说明有了一个大的删除动作，需要整理碎片。

c) 索引碎片整理方法

alter index index_name rebuild nologging online ;

2. 分析处理系统索引扩展次数大于10次

SELECT   'alter index ' || owner || '.' || segment_name || ' rebuild nologgin online;'

  FROM   (  SELECT   COUNT ( * ),

                     owner,

                     segment_name,

                     t.tablespace_name

              FROM   dba_extents t

             WHERE   t.segment_type = 'INDEX'

                     AND t.owner NOT IN ('SYS','SYSTEM','DBSNMP','SYSMAN','XDB')

          GROUP BY   owner, segment_name, t.tablespace_name

            HAVING   COUNT ( * ) > 10

          ORDER BY   COUNT ( * ) DESC);

执行查询的SQL语句。