Oracle数据库表碎片整理

  Oracle数据库表碎片整理
 
一、Oracle数据库表是否需要碎片整理，看个例子，有人是这么说的
 由于oracle的数据表的存储不像mysql(innodb)那样使用的聚集组织表(IOT）存放数据，而是使用的是称为堆(HEAP)的方式来存放数据．
 数据行被存放到的块是随机的．进行全表扫描的时候读取出来的数据行没有按一定的方式进行排序.所以ORACLE可以对任意有空闲空间的数据块进行数据插入.
 也就是说空闲空间的数据块还会被使用，从这个意义上讲，是不需要碎片整理

SQL> exec dbms_stats.gather_table_stats('TDWCMSDB','TDWCMS_PRODUCT');
     analyze table t1 compute statistics;
SQL> select table_name,num_rows,blocks,num_rows/blocks from dba_tables where table_name='TDWCMS_PRODUCT' and owner='TDWCMSDB';

TABLE_NAME                       NUM_ROWS     BLOCKS NUM_ROWS/BLOCKS
------------------------------ ---------- ---------- ---------------
TDWCMS_PRODUCT                     120380       4378      27.4965738

当前该表拥有120380行，4378个数据块，平均每个块大概是存放了27行数据.
在使用脚本随机删除2000行数据后， 平均每个块存放的行数 明显变小了，
然后在随机插入数据2000行后，平均每个块存放的行数 又回到了27行，也就是数据库块中的空闲空间被使用了。

看吧，这个例子说的空闲空间，其实指的是 “表段已经申请到的空间的重复使用"，的而且不是真正的 “表空间的Free Space”。


二、碎片是如何产生的
空间结构关系：
表空间（Tablespace）、段（Segment）、范围（Extent）、自由空间（Free Space）、数据块（Data Block）
其中数据块包括：
  Unformatted Blocks 已申请来未格式化的
  Unused Blocks      已格式化未使用的   
  Used Blocks   
      部分已使用的
      全部已使用的,已使用满的   Full Blocks

1.理论知识：
  表在建立时（逻辑上的段），根据initial_extent参数来初始化范围（extent）表在建立时（逻辑上的段），根据initial_extent参数来初始化范围（extent）,
在这些初始范围充满数据时，段会请求增加另一个范围。这样的扩展过程会一直继续下去，直到达到最大的范围值，或者在表空间中已经没有自由空间用于下一个范围。
最理想的状态就是一个段的数据可被存在单一的一个范围中。这样，所有的数据存储时靠近段内其它数据，并且寻找数据可少用一些指针。
但是一个段包含多个范围的情况是大量存在的，没有任何措施可以保证这些范围是相邻存储的，当要满足一个空间要求时，数据库不再合并相邻的自由范围（除非别无选择），
而是寻找表空间中最大的自由范围来使用。这样将逐渐形成越来越多的离散的、分隔的、较小的自由空间，即碎片。

2.碎片对系统的影响
  碎片越来越多时，找到一个足够大的自由范围已变得越来越困难,导致系统性能减弱
  SMON （系统监控）后台进程周期性地合并部分自由范围Extent（如表空间的 pctincrease 为非 0 ）,但始终有一部分自由范围无法得以自动合并，浪费了大量的表空间

3.自由范围的碎片计算
  自由范围数量、最大自由范围尺寸，
  FSFI--Free Space Fragmentation Index （自由空间碎片索引）值来直观体现：
  FSFI=100*SQRT(max(extent)/sum(extents))*1/SQRT(SQRT(count(extents)))
  可以看出FSFI 的最大可能值为 100 （一个理想的单文件表空间），随着范围的增加， FSFI 值缓慢下降，而随着最大范围尺寸的减少， FSFI 值会迅速下降。
 
  当FSFI<=30的时候建议整理碎片,
SQL> select tablespace_name,sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) FSFI
     FROM dba_free_space
     group by tablespace_name order by 1;
 
  TABLESPACE_NAME                      FSFI
------------------------------ ----------
SYSAUX                         29.2920889
SYSTEM                         84.0498358
TDCMS_DATA                            100
TDMC_DATA                             100
TDMDOLOG_DATA                  29.2747955
TDSP_DATA                             100
TDTM_DATA                             100
TDWCMSDB                       13.3218369
TDWLDB_DATA                    50.3387017
TDWLDB_IDX                            100
TDWL_DATA                             100

TABLESPACE_NAME                      FSFI
------------------------------ ----------
TDWNET_DATA                           100
TDYWDSDB                       84.0512657
UNDOTBS1                       19.8632222
USERS                          56.1681666


rem FSFI Value Compute rem fsfi.sql
column FSFI format 999,99
select tablespace_name,sqrt(max(blocks)/sum(blocks))* (100/sqrt(sqrt(count(blocks)))) FSFI
  from dba_free_space
 group by tablespace_name
order by 1;
spool fsfi.rep;
/
spool off;


4.自由范围的碎片整理
  1）表空间的 pctincrease 值为非 0。
     可以将表空间的缺省存储参数 pctincrease 改为非 0 ，一般将其设为 1 ，如：
     alter tablespace temp default storage(pctincrease 1);这样SMON 便会将自由范围自动合并。
  2） 也可以手工合并自由范围：
     alter tablespace temp coalesce。
    
5.如果段的碎片过多， 将其数据压缩到一个范围的最简单方法便是用正确的存储参数将这个段重建，然后将旧表中的数据插入到新表，同时删除旧表。
  这个过程可以用 Import/Export （输入 / 输出）工具来完成
  Export （）命令有一个（压缩）标志，这个标志在读表时会引发 Export 确定该表所分配的物理空间量，
  它会向输出转储文件写入一个新的初始化存储参数 -- 等于全部所分配空间。若这个表关闭， 则使用 Import （）工具重新生成。
  这样，它的数据会放入一个新的、较大的初始段中。
  exp user/password file=exp.dmp compress=Y grants=Y indexes=Y tables=(table1,table2);
  imp user/password file=exp.dmp commit=Y buffer=64000 full=Y

 
 
 
 
 
SQL> l
    SELECT segment_name,
                 SUM(bytes)/1024/1024 "SizeM",
                 SUM(blocks)  blocks,
                 COUNT(1) extent_count,
                 MIN(bytes)/1024/1024 "inital_Extent(M)",
                 MAX(bytes)/1024/1024 "max_Extent(M)"
    FROM dba_extents
    WHERE segment_name = 'TDWCMS_PRODUCT_DAY'
    AND owner = 'TDWCMSDB'
   group by segment_name
SQL> /

SEGMENT_NAME                        SizeM     BLOCKS EXTENT_COUNT inital_Extent(M) max_Extent(M)
------------------------------ ---------- ---------- ------------ ---------------- -------------
TDWCMS_PRODUCT_DAY                   6850     876800          300            .0625            64

SQL> l
    SELECT TABLE_NAME, (BLOCKS * 8192  - NUM_ROWS * AVG_ROW_LEN) / 1024 / 1024 "Data lower than HWM in MB"
    FROM DBA_TABLES
    WHERE table_name = 'TDWCMS_PRODUCT_DAY'
    AND owner = 'TDWCMSDB'
SQL> /

TABLE_NAME           Data lower than HWM in MB
-------------------- -------------------------
TDWCMS_PRODUCT_DAY                  1046.17308


exp \'/ as sysdba \' file=TDWCMS_PRODUCT_DAY.dmp compress=Y grants=Y indexes=Y tables=(TDWCMSDB.TDWCMS_PRODUCT_DAY);
imp \'/ as sysdba \' file=TDWCMS_PRODUCT_DAY.dmp commit=Y buffer=64000 full=Y  IGNORE=Y

因为物化视图和虚列 在exp时，报错，所以imp时加  IGNORE=Y
分区表exp/imp时是正常的。
exp/imp时最好把同步备库的操作暂停，因为要产生大量的log

exec dbms_stats.gather_table_stats('TDWCMSDB','TDWCMS_PRODUCT');

SQL>    SELECT segment_name,
               SUM(bytes)/1024/1024 "SizeM",
               SUM(blocks)  blocks,
               COUNT(1) extent_count,
               MIN(bytes)/1024/1024 "inital_Extent(M)",
               MAX(bytes)/1024/1024 "max_Extent(M)"
        FROM dba_extents
        WHERE segment_name = 'TDWCMS_PRODUCT_DAY'
        AND owner = 'TDWCMSDB'
   group by segment_name  

SEGMENT_NAME              SizeM     BLOCKS EXTENT_COUNT inital_Extent(M) max_Extent(M)
-------------------- ---------- ---------- ------------ ---------------- -------------
TDWCMS_PRODUCT_DAY         6835     874880          152                7            64
初始extent从62K变成7M，extent数量从300个减少到152个。看来单个表进行段空间整理，受空闲空间碎片化程度限制，所以是不彻底的。必须对整个表空间所有表做exp才能更好的降低extent数量。

用exp方式，不会导致主键索引失效
  SELECT * FROM user_objects WHERE status <> 'VALID';
  SELECT * FROM user_indexes  WHERE status <> 'VALID';


理想目标：
 1）每个表都是只有一个范围（Extent） ，减少extent中block数  ， 需要重建表，或exp/imp方式
 2）空闲空间都是在一起
 
 收缩段，减少段的extent数量，消除部分行迁移，消除空间碎片，使数据更紧密
 
 
 
 alter table move 或shrink space可以收缩段，用来消除部分行迁移，消除空间碎片，使数据更紧密，但move 跟shrink space还是有区别的。
Move会移动高水位，但不会释放申请的空间，是在高水位以下(below HWM)的操作; move以block为单位，进行了block间的数据copy。
而shrink space 同样会移动高水位，但也会释放申请的空间，是在高水位上下(below and above HWM)都有的操作;以行为单位移动数据。
MOVE并不算真正意义上的压缩空间，只会压缩HWM以下的空间，消除碎片。我们一般建表时没有指定initial参数(默认是8个BLOCK)，也就感觉不到这个差异。而SHRINK SPACE真正做到了对段的压缩，包括初始分配的也压了，所以它是blow and above HWM操作。
至于需要哪种方法，得看你的需求来了，需要分析表的增长情况，要是以后还会达到以前的HWM高度，那显然MOVE是更合适的，因为SHRINK SPACE还需要重新申请之前放掉的空间，无疑增加了操作。

MOVE和SRINK SPACE两种操作需要的时长都很长，特别是大表，而且存在锁表的情况，因此一方面需要在数据库资源充足的情况下执行，
经试验增加执行时的并行度后，extent数量明显翻了近3倍，alter table xxx move parallel 8; 因此这个并行也是不可行的。


alter table TB_SPIDER_INFO_LOG enable row movement;
alter table TB_SPIDER_INFO_LOG shrink space cascade;
alter table TB_SPIDER_INFO_LOG disable row movement;





1，获取表的段顾问建议，判断表的优化意见。
select
     'Segment Advice -----------------'         || chr(10) ||
     'tablespace_name :' || tablespace_name    || chr(10) ||
     'segment_owner :' || segment_owner    || chr(10) ||
     'segment_owner :' || 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'));
 
 
2.extent较多的段
SQL> SELECT segment_name,
                   SUM(bytes)/1024/1024 "SizeM",
                   SUM(blocks)  blocks,
                   COUNT(1) extent_count,
                   MIN(bytes)/1024/1024 "inital_Extent(M)",
               MAX(bytes)/1024/1024 "max_Extent(M)"
      FROM dba_extents
     WHERE owner = 'TDWCMSDB'
       group by segment_name  
      ORDER BY extent_count DESC
   /
oracle
SEGMENT_NAME                   SizeM     BLOCKS EXTENT_COUNT inital_Extent(M) max_Extent(M)
------------------------- ---------- ---------- ------------ ---------------- -------------
TDWCMS_CHANNELITEM_LOG           314      40192          427            .0625             8
TDMDO_FEE_LOG                588.875      75376          338            .0625             8
IX_TDMDO_USER_FEE_MONTH         2731     349568          239            .0625            64
TDMDO_USER_FEE_MONTH            2214     283392          229            .0625            64
IX_TDMDO_FEE_LOG            215.8125      27624          199            .0625             8
IX_TDMDO_FEE_LOG_CH_ID       144.875      18544          192            .0625             8
TDWCMS_YDLOGO_AREA_DAY           901     115328          187            .0625             8

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/807718/viewspace-2126587/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/807718/viewspace-2126587/