Oracle碎片整理

DATA LOWER THAN HWM:
SELECT TABLE_NAME,(BLOCKS * 8192 / 1024 / 1024) - (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB" FROM USER_TABLES WHERE table_name = '&1';


select tablespace_name,sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) FSFI from dba_free_space group by tablespace_name order by tablespace_name;


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


一：准备测试数据，使用HR用户，创建T1表，插入约30W的数据，并根据object_id创建普通索引，表占存储空间34M左右，索引占6M左右的存储空间      
   SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE);    
   SQL> select count(1) from t1; 
    
     COUNT(1) 
   ---------- 
       299248 
    
   SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='T1'; 
   SUM(BYTES)/1024/1024 
   -------------------- 
                34.0625 
    
   SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='IDX_T1_ID'; 
   SUM(BYTES)/1024/1024 
   -------------------- 
                      6 




二：估算表在高水位线下还有多少空间可用，这个值应当越低越好，表使用率越接近高水位线，全表扫描所做的无用功也就越少!
   DBMS_STATS包无法获取EMPTY_BLOCKS统计信息，所以需要用analyze命令再收集一次统计信息
   SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1'; 
    
       BLOCKS EMPTY_BLOCKS   NUM_ROWS 
   ---------- ------------ ---------- 
         4302            0     299248 
    
   SQL> analyze table t1 compute statistics; 
   表已分析。 
    
   SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1'; 
    
       BLOCKS EMPTY_BLOCKS   NUM_ROWS 
   ---------- ------------ ---------- 
         4302           50     299248 
    
   SQL> col table_name for a20 
   SQL> SELECT TABLE_NAME, 
     2         (BLOCKS * 8192 / 1024 / 1024) - 
     3         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB" 
     4    FROM USER_TABLES 
     5   WHERE table_name = 'T1'; 
    
   TABLE_NAME           Data lower than HWM in MB 
   -------------------- ------------------------- 
   T1                                  5.07086182 




三: 查看执行计划，全表扫描大概需要消耗CPU 1175
   SQL> explain plan for select * from t1; 
   SQL> select * from table(dbms_xplan.display); 
    
   PLAN_TABLE_OUTPUT 
   -------------------------------------------------------------------------------- 
   Plan hash value: 3617692013 
   -------------------------------------------------------------------------- 
   | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
   -------------------------------------------------------------------------- 
   |   0 | SELECT STATEMENT  |      |   299K|    28M|  1175   (1)| 00:00:15 | 
   |   1 |  TABLE ACCESS FULL| T1   |   299K|    28M|  1175   (1)| 00:00:15 | 
   -------------------------------------------------------------------------- 




四：删除大部分数据，收集统计信息，全表扫描依然需要消耗CPU 1168
   SQL> delete from t1 where object_id>100; 
   已删除298852行。    
   SQL> commit;       
   SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE);    
   SQL> analyze table t1 compute statistics; 。    
   SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1'; 
    
       BLOCKS EMPTY_BLOCKS   NUM_ROWS 
   ---------- ------------ ---------- 
         4302           50        396 
    
    
   SQL> explain plan for select * from t1;  
   SQL> select * from table(dbms_xplan.display); 
    
   PLAN_TABLE_OUTPUT 
   ------------------------------------------------------------------------------ 
   Plan hash value: 3617692013 
   -------------------------------------------------------------------------- 
   | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
   -------------------------------------------------------------------------- 
   |   0 | SELECT STATEMENT  |      |   396 | 29700 |  1168   (1)| 00:00:15 | 
   |   1 |  TABLE ACCESS FULL| T1   |   396 | 29700 |  1168   (1)| 00:00:15 | 
   -------------------------------------------------------------------------- 




五：估算表在高水位线下还有多少空间是无数据的，但在全表扫描时又需要做无用功的数据
   SQL> SELECT TABLE_NAME,(BLOCKS * 8192 / 1024 / 1024) - (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB" FROM USER_TABLES WHERE table_name = 'T1'; 
    
   TABLE_NAME           Data lower than HWM in MB 
   -------------------- ------------------------- 
   T1                                  33.5791626 




六：对表进行碎片整理，重新收集统计信息
   SQL> alter table t1 enable row movement; 
   SQL> alter table t1 shrink space cascade;     
   SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='T1'; 
    
   SUM(BYTES)/1024/1024 
   -------------------- 
                   .125 
    
   SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='IDX_T1_ID 
   '; 
    
   SUM(BYTES)/1024/1024 
   -------------------- 
                  .0625 
    
   SQL> SELECT TABLE_NAME, 
     2         (BLOCKS * 8192 / 1024 / 1024) - 
     3         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB" 
     4    FROM USER_TABLES 
     5   WHERE table_name = 'T1'; 
    
   TABLE_NAME           Data lower than HWM in MB 
   -------------------- ------------------------- 
   T1                                  33.5791626 
    
   SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE); 
   PL/SQL 过程已成功完成。 
    
   这个时候，只剩下0.1M的无用功了，执行计划中，全表扫描也只需要消耗CPU 3 
   SQL> SELECT TABLE_NAME, 
     2         (BLOCKS * 8192 / 1024 / 1024) - 
     3         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB" 
     4    FROM USER_TABLES 
     5   WHERE table_name = 'T1'; 
    
   TABLE_NAME           Data lower than HWM in MB 
   -------------------- ------------------------- 
   T1                                  .010738373 
    
    
   SQL> select * from table(dbms_xplan.display); 
    
   PLAN_TABLE_OUTPUT 
   -------------------------------------------------------------------------------- 
   Plan hash value: 3617692013 
   -------------------------------------------------------------------------- 
   | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
   -------------------------------------------------------------------------- 
   |   0 | SELECT STATEMENT  |      |   396 | 29700 |     3   (0)| 00:00:01 | 
   |   1 |  TABLE ACCESS FULL| T1   |   396 | 29700 |     3   (0)| 00:00:01 | 
   -------------------------------------------------------------------------- 
    
   总共只有5个块，空块却有50个，明显empty_blocks信息过期 
   SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='T1'; 
    
       BLOCKS EMPTY_BLOCKS   NUM_ROWS 
   ---------- ------------ ---------- 
            5           50        396 
    
   SQL> analyze table t1 compute statistics; 
   SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='T1'; 
    
    
       BLOCKS EMPTY_BLOCKS   NUM_ROWS 
   ---------- ------------ ---------- 
            5            3        396