【fragmentation】ORACLE中碎片小结

碎片的产生总的来讲是因为不断插入，修改使得产生一些小的无法利用的空闲空间。碎片根据产生的位置不同分多种情况，其层级关系如下：

disk-level fragmention

  tablespace-level fragmentation

    segment-level fragmentation

       block-level fragmentation

           row-level fragmentation

       index leaf block-level fragmentation

我们这里针对tablespace-level fragmentation（table,index）进行介绍

1.表空间的碎片整理

本地盘区管理出现之前使用DMT, 数据段segment的扩展extent会参考pctincrease 来增长，区是一段连续的空间，这会导致一个表空间上出现大大小小的extents，过多extents会造成表空间上的碎片过多，即时在free space很大的情况下却可能无法放下一个连续的大的extents，例如free space有1G ，但是每个free extents实际都不超过16MB ,这时候如果要分配一个32MB的extents也做不到。

因此对于DMT 有必要定期做alter tablespace  coalesce的合并操作， 将小的extents合并成大的extents，减少碎片。这是表空间的碎片整理。

而在LOCAL Management extents下分配的extent 或者是 uniform 固定大小的例如 1MB-128M，或者是autoallocate 系统自动分配的从 8* blocks 到更多。表空间碎片的问题已经减少了。10g中除了个别现象和DMT基本可以忽略表空间碎片这个话题了。

2. 段的碎片整理

2.1 检测表的碎片情况

一般来说，高水位线以下空闲的空间占总空间的30%以上时就需要进行碎片整理。其脚本如下：

REM LOCATION:   Object Management\Tables\Reports
REM FUNCTION:   Tables that may benefit from a rebuild.
REM TESTED ON:  10.2.0.3, 11.1.0.6 (Will not work versions < 10.1.)
REM PLATFORM:   non-specific
REM REQUIRES:   dba_tables, dba_segments, v$parameter.
REM             Tables must be analyzed.
REM NOTE:       The use of the FIRST_ROWS hint is needed to bypass a bug in 10g
REM             when querying the VALUE column if v$parameter. It can be removed
REM             if you are using this query with Oracle 11g, as this problem
REM             has been corrected. Reference Metalink note 1016476.102 for more info.
REM
REM  This is a part of the Knowledge Xpert for Oracle Administration library.
REM  Copyright (C) 2008 Quest Software
REM  All rights reserved.
REM
REM ******************** Knowledge Xpert for Oracle Administration ********************
UNDEF ENTER_OWNER_NAME
UNDEF ENTER_TABLE_NAME

SET pages 56 lines 132 newpage 0 verify off echo off feedback off
SET serveroutput on
-- when the table is > 10 blocks and
-- total space usage up to the  HWM is < 30% of the size of the HWM, we will consider the table 
-- a candidate for shrinking. 

BEGIN
   DBMS_OUTPUT.put_line
      ('This report produces a list of tables that may benefit from compacting.'
      );
   DBMS_OUTPUT.put_line (CHR (13));

   FOR space_usage IN
      (SELECT /*+ FIRST_ROWS */
              dba_tables.owner, dba_tables.table_name,
              dba_tables.blocks blocks_below,
              dba_segments.blocks total_blocks,
              dba_tables.num_rows * dba_tables.avg_row_len total_data_size,
              ROUND ((  100
                      * (dba_tables.num_rows * dba_tables.avg_row_len)
                      / (GREATEST (dba_tables.blocks, 1) * v$parameter.VALUE)
                     ),
                     3
                    ) hwm_full,
              ROUND ((  100
                      * (dba_tables.num_rows * dba_tables.avg_row_len)
                      / (GREATEST (dba_segments.blocks, 1) * v$parameter.VALUE
                        )
                     ),
                     3
                    ) space_full
         FROM dba_tables, v$parameter, dba_segments
        WHERE dba_tables.owner NOT IN ('SYS', 'SYSTEM')
          AND dba_tables.owner LIKE UPPER ('&&ENTER_OWNER_NAME')
          AND dba_tables.table_name LIKE UPPER ('&&ENTER_TABLE_NAME')
          AND dba_tables.owner = dba_segments.owner
          AND dba_tables.table_name = dba_segments.segment_name
          AND v$parameter.NAME = LOWER ('db_block_size')
          AND (  100
               * (dba_tables.num_rows * dba_tables.avg_row_len)
               / (GREATEST (dba_segments.blocks, 1) * v$parameter.VALUE)
              ) < 30
          AND dba_segments.blocks > 10)
   LOOP
      DBMS_OUTPUT.put_line (   'Candidate table is '
                            || space_usage.owner
                            || '.'
                            || space_usage.table_name
                           );
      DBMS_OUTPUT.put_line (   'Which is using  '
                            || space_usage.space_full
                            || '% of allocated space. '
                           );
      DBMS_OUTPUT.put_line (   'Which is using  '
                            || space_usage.hwm_full
                            || '% of allocated space to the HWM. '
                           );
      DBMS_OUTPUT.put_line ('You can use this script to compact the table:');
      DBMS_OUTPUT.put_line (   'alter table '
                            || space_usage.owner
                            || '.'
                            || space_usage.table_name
                            || ' enable row movement; '
                           );
      DBMS_OUTPUT.put_line (   'alter table '
                            || space_usage.owner
                            || '.'
                            || space_usage.table_name
                            || ' shrink space cascade; '
                           );
      DBMS_OUTPUT.put_line (CHR (13));
   END LOOP;
END;

根据返回的提示信息，来确定是否需要进行碎片的整理

2.2 碎片整理的方法

表的碎片整理的方法有很多种，根据其不同的特性来决定使用哪一种方法进行碎片的整理

1）  alter table xxx move;

2）  alter table xxx shrink;

3）  imp/exp

3.索引的碎片

一个索引块中的数据如果不是全部被删除，那么此块中的空闲空间将不会被重用。如果此块中的数据全部被删除了，那么此块就可以被重用。随着，表的DML操作，索引块中存在的空闲空间使得数据更加分散，扫描索引需要更多次的I/O，降低查询的性能。

3.1 判断索引中是否存在碎片

analyze index index_name validate structure或validate index index_name

analyze index index1 validate structure：是用来分析索引的数据块是否有坏块，以及根据分析得到的

数据（存放在index_stats）來判断索引是否需要重新建立。

运行命令后，然后在视图 index_stats查询，这个视图记录索引的一些信息，这个视图只有一个记录，所以在同一时间

只能分析一个索引。

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

2）如果”hight“大于4，可以考虑碎片整理

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

3.2 索引碎片整理方法

1）recreate index

2.）alter indexskate.idx_test rebuild nologging parallel 4 online ;

ballontt
2013/11/25

---The End---