数据库在日常使用过程中，不断的insert，delete，update操作，导致表和索引出现碎片是在所难免的事情，碎片多了，sql的执行效率自然就差了，道理很简单，高水位线（HWL）下的许多数据块都是无数据的，但全表扫描的时候要扫描到高水位线的数据块，也就是说oracle要做许多的无用功！因此oracle提供了shrink space碎片整理功能。对于索引，可以采取rebuild online的方式进行碎片整理，一般来说，经常进行DML操作的对象DBA要定期进行维护，同时注意要及时更新统计信息！

一：准备测试数据，使用HR用户，创建T1表，插入约30W的数据，并根据object_id创建普通索引，表占存储空间34M左右，索引占6M左右的存储空间 

 
  
     
   
  1. SQL> conn /as sysdba 
    2.  
   
  2. 已连接。 
    3.  
   
  3. SQL> select default_tablespace from dba_users where username='HR'; 
    4.  
   
  4.  
    5.  
   
  5. DEFAULT_TABLESPACE 
    6.  
   
  6. ------------------------------------------------------------ 
    7.  
   
  7. USERS 
    8.  
   
  8.  
    9.  
   
  9. SQL> conn hr/hr 
    10.  
   
  10. 已连接。 
    11.  
   
  11.  
    12.  
   
  12. SQL> insert into t1 select * from t1; 
    13.  
   
  13. 已创建 74812 行。 
    14.  
   
  14.  
    15.  
   
  15. SQL> insert into t1 select * from t1; 
    16.  
   
  16. 已创建 149624 行。 
    17.  
   
  17.  
    18.  
   
  18. SQL> commit; 
    19.  
   
  19. 提交完成。 
    20.  
   
  20.  
    21.  
   
  21. SQL> create index idx_t1_id on t1(object_id); 
    22.  
   
  22. 索引已创建。 
    23.  
   
  23.  
    24.  
   
  24. SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE); 
    25.  
   
  25. PL/SQL 过程已成功完成。 
    26.  
   
  26.  
    27.  
   
  27. SQL> select count(1) from t1; 
    28.  
   
  28.  
    29.  
   
  29.   COUNT(1) 
    30.  
   
  30. ---------- 
    31.  
   
  31.     299248 
    32.  
   
  32.  
    33.  
   
  33. SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='T1'; 
    34.  
   
  34. SUM(BYTES)/1024/1024 
    35.  
   
  35. -------------------- 
    36.  
   
  36.              34.0625 
    37.  
   
  37.  
    38.  
   
  38. SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='IDX_T1_ID'; 
    39.  
   
  39. SUM(BYTES)/1024/1024 
    40.  
   
  40. -------------------- 
    41.  
   
  41.                    6 
    42.  
  
 
  
 
 
  
二：估算表在高水位线下还有多少空间可用，这个值应当越低越好，表使用率越接近高水位线，全表扫描所做的无用功也就越少!
 
  
DBMS_STATS包无法获取EMPTY_BLOCKS统计信息，所以需要用analyze命令再收集一次统计信息
 
  
 
  
     
   
  1. SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1'; 
    2.  
   
  2.  
    3.  
   
  3.     BLOCKS EMPTY_BLOCKS   NUM_ROWS 
    4.  
   
  4. ---------- ------------ ---------- 
    5.  
   
  5.       4302            0     299248 
    6.  
   
  6.  
    7.  
   
  7. SQL> analyze table t1 compute statistics; 
    8.  
   
  8. 表已分析。 
    9.  
   
  9.  
    10.  
   
  10. SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1'; 
    11.  
   
  11.  
    12.  
   
  12.     BLOCKS EMPTY_BLOCKS   NUM_ROWS 
    13.  
   
  13. ---------- ------------ ---------- 
    14.  
   
  14.       4302           50     299248 
    15.  
   
  15.  
    16.  
   
  16. SQL> col table_name for a20 
    17.  
   
  17. SQL> SELECT TABLE_NAME, 
    18.  
   
  18.   2         (BLOCKS * 8192 / 1024 / 1024) - 
    19.  
   
  19.   3         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB" 
    20.  
   
  20.   4    FROM USER_TABLES 
    21.  
   
  21.   5   WHERE table_name = 'T1'; 
    22.  
   
  22.  
    23.  
   
  23. TABLE_NAME           Data lower than HWM in MB 
    24.  
   
  24. -------------------- ------------------------- 
    25.  
   
  25. T1                                  5.07086182 
    26.  
  
 
  
三: 查看执行计划，全表扫描大概需要消耗CPU 1175
 
  
 
  
     
   
  1. SQL> explain plan for select * from t1; 
    2.  
   
  2. 已解释。 
    3.  
   
  3.  
    4.  
   
  4. SQL> select * from table(dbms_xplan.display); 
    5.  
   
  5.  
    6.  
   
  6. PLAN_TABLE_OUTPUT 
    7.  
   
  7. -------------------------------------------------------------------------------- 
    8.  
   
  8. Plan hash value: 3617692013 
    9.  
   
  9. -------------------------------------------------------------------------- 
    10.  
   
  10. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
    11.  
   
  11. -------------------------------------------------------------------------- 
    12.  
   
  12. |   0 | SELECT STATEMENT  |      |   299K|    28M|  1175   (1)| 00:00:15 | 
    13.  
   
  13. |   1 |  TABLE ACCESS FULL| T1   |   299K|    28M|  1175   (1)| 00:00:15 | 
    14.  
   
  14. -------------------------------------------------------------------------- 
    15.  
  
 
  
四：删除大部分数据，收集统计信息，全表扫描依然需要消耗CPU 1168
 
  
 
  
     
   
  1. SQL> delete from t1 where object_id>100; 
    2.  
   
  2. 已删除298852行。 
    3.  
   
  3.  
    4.  
   
  4. SQL> commit; 
    5.  
   
  5. 提交完成。 
    6.  
   
  6.  
    7.  
   
  7. SQL> select count(*) from t1; 
    8.  
   
  8.  
    9.  
   
  9.   COUNT(*) 
    10.  
   
  10. ---------- 
    11.  
   
  11.        396 
    12.  
   
  12.  
    13.  
   
  13. SQL>  exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE); 
    14.  
   
  14. PL/SQL 过程已成功完成。 
    15.  
   
  15.  
    16.  
   
  16. SQL> analyze table t1 compute statistics; 
    17.  
   
  17. 表已分析。 
    18.  
   
  18.  
    19.  
   
  19. SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1'; 
    20.  
   
  20.  
    21.  
   
  21.     BLOCKS EMPTY_BLOCKS   NUM_ROWS 
    22.  
   
  22. ---------- ------------ ---------- 
    23.  
   
  23.       4302           50        396 
    24.  
   
  24.  
    25.  
   
  25.  
    26.  
   
  26. SQL> explain plan for select * from t1; 
    27.  
   
  27. 已解释。 
    28.  
   
  28.  
    29.  
   
  29. SQL> select * from table(dbms_xplan.display); 
    30.  
   
  30.  
    31.  
   
  31. PLAN_TABLE_OUTPUT 
    32.  
   
  32. ------------------------------------------------------------------------------ 
    33.  
   
  33. Plan hash value: 3617692013 
    34.  
   
  34. -------------------------------------------------------------------------- 
    35.  
   
  35. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
    36.  
   
  36. -------------------------------------------------------------------------- 
    37.  
   
  37. |   0 | SELECT STATEMENT  |      |   396 | 29700 |  1168   (1)| 00:00:15 | 
    38.  
   
  38. |   1 |  TABLE ACCESS FULL| T1   |   396 | 29700 |  1168   (1)| 00:00:15 | 
    39.  
   
  39. -------------------------------------------------------------------------- 
    40.  
  
 
  
五：估算表在高水位线下还有多少空间是无数据的，但在全表扫描时又需要做无用功的数据
 
  
 
  
     
   
  1. SQL> SELECT TABLE_NAME, 
    2.  
   
  2.   2         (BLOCKS * 8192 / 1024 / 1024) - 
    3.  
   
  3.   3         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB" 
    4.  
   
  4.   4    FROM USER_TABLES 
    5.  
   
  5.   5   WHERE table_name = 'T1'; 
    6.  
   
  6.  
    7.  
   
  7. TABLE_NAME           Data lower than HWM in MB 
    8.  
   
  8. -------------------- ------------------------- 
    9.  
   
  9. T1                                  33.5791626 
    10.  
  
 
  
六：对表进行碎片整理，重新收集统计信息
 
  
 
  
     
   
  1. SQL> alter table t1 enable row movement; 
    2.  
   
  2. 表已更改。 
    3.  
   
  3.  
    4.  
   
  4. SQL> alter table t1 shrink space cascade; 
    5.  
   
  5. 表已更改。 
    6.  
   
  6.  
    7.  
   
  7. SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='T1'; 
    8.  
   
  8.  
    9.  
   
  9. SUM(BYTES)/1024/1024 
    10.  
   
  10. -------------------- 
    11.  
   
  11.                 .125 
    12.  
   
  12.  
    13.  
   
  13. SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='IDX_T1_ID 
    14.  
   
  14. '; 
    15.  
   
  15.  
    16.  
   
  16. SUM(BYTES)/1024/1024 
    17.  
   
  17. -------------------- 
    18.  
   
  18.                .0625 
    19.  
   
  19.  
    20.  
   
  20. SQL> SELECT TABLE_NAME, 
    21.  
   
  21.   2         (BLOCKS * 8192 / 1024 / 1024) - 
    22.  
   
  22.   3         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB" 
    23.  
   
  23.   4    FROM USER_TABLES 
    24.  
   
  24.   5   WHERE table_name = 'T1'; 
    25.  
   
  25.  
    26.  
   
  26. TABLE_NAME           Data lower than HWM in MB 
    27.  
   
  27. -------------------- ------------------------- 
    28.  
   
  28. T1                                  33.5791626 
    29.  
   
  29.  
    30.  
   
  30. SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE); 
    31.  
   
  31. PL/SQL 过程已成功完成。 
    32.  
   
  32.  
    33.  
   
  33. 这个时候，只剩下0.1M的无用功了，执行计划中，全表扫描也只需要消耗CPU 3 
    34.  
   
  34. SQL> SELECT TABLE_NAME, 
    35.  
   
  35.   2         (BLOCKS * 8192 / 1024 / 1024) - 
    36.  
   
  36.   3         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB" 
    37.  
   
  37.   4    FROM USER_TABLES 
    38.  
   
  38.   5   WHERE table_name = 'T1'; 
    39.  
   
  39.  
    40.  
   
  40. TABLE_NAME           Data lower than HWM in MB 
    41.  
   
  41. -------------------- ------------------------- 
    42.  
   
  42. T1                                  .010738373 
    43.  
   
  43.  
    44.  
   
  44.  
    45.  
   
  45. SQL> select * from table(dbms_xplan.display); 
    46.  
   
  46.  
    47.  
   
  47. PLAN_TABLE_OUTPUT 
    48.  
   
  48. -------------------------------------------------------------------------------- 
    49.  
   
  49. Plan hash value: 3617692013 
    50.  
   
  50. -------------------------------------------------------------------------- 
    51.  
   
  51. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
    52.  
   
  52. -------------------------------------------------------------------------- 
    53.  
   
  53. |   0 | SELECT STATEMENT  |      |   396 | 29700 |     3   (0)| 00:00:01 | 
    54.  
   
  54. |   1 |  TABLE ACCESS FULL| T1   |   396 | 29700 |     3   (0)| 00:00:01 | 
    55.  
   
  55. -------------------------------------------------------------------------- 
    56.  
   
  56.  
    57.  
   
  57. 总共只有5个块，空块却有50个，明显empty_blocks信息过期 
    58.  
   
  58. SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='T1'; 
    59.  
   
  59.  
    60.  
   
  60.     BLOCKS EMPTY_BLOCKS   NUM_ROWS 
    61.  
   
  61. ---------- ------------ ---------- 
    62.  
   
  62.          5           50        396 
    63.  
   
  63.  
    64.  
   
  64. SQL> analyze table t1 compute statistics; 
    65.  
   
  65. 表已分析。 
    66.  
   
  66.  
    67.  
   
  67. SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='T1'; 
    68.  
   
  68.  
    69.  
   
  69.  
    70.  
   
  70.     BLOCKS EMPTY_BLOCKS   NUM_ROWS 
    71.  
   
  71. ---------- ------------ ---------- 
    72.  
   
  72.          5            3        396 
    73.  
  
 
  
参考：http://surachartopun.com/2011/08/determine-hwm-and-reduce-it-by-shrink.html
 
  
关于如何确定哪些表需要进行碎片整理，可以使用附件中的脚本去查询，具体请参考：http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/OTNN18/Default.aspx