Oracle 表碎片

Oracle 表碎片

---

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


一：准备测试数据，使用SCOTT用户，创建T1表，插入约30W的数据，并根据object_id创建普通索引，表占存储空间34M左右，索引占6M左右的存储空间
SQL> conn /as sysdba
已连接。
SQL> select default_tablespace from dba_users where username='SCOTT';
 
DEFAULT_TABLESPACE
------------------------------------------------------------
USERS
 
SQL> conn SCOTT/tiger
已连接。
 
SQL> create table t1 (object_id number(10), object_name varchar(25));

Table created.

SQL> insert into t1 values ('1', 'user');

1 row created.

SQL> insert into t1 values ('2', 'test');

1 row created.

SQL> insert into t1 select * from t1;

SQL> commit;

SQL> create index idx_t1_id on t1(object_id);

 
SQL> exec dbms_stats.gather_table_stats('SCOTT','T1',CASCADE=>TRUE);
PL/SQL 过程已成功完成。
 
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,
(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                                  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> select count(*) from t1;
 
  COUNT(*)
----------
       396
 
SQL>  exec dbms_stats.gather_table_stats('SCOTT','T1',CASCADE=>TRUE);
PL/SQL 过程已成功完成。
 
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,
(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> exec dbms_stats.gather_table_stats('SCOTT','T1',CASCADE=>TRUE);
PL/SQL 过程已成功完成。
 
这个时候，只剩下0.1M的无用功了，执行计划中，全表扫描也只需要消耗CPU 3
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                                  .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



查看Oracle碎片比较多的表
select t.table_name,   
       avg_row_len * num_rows / (1 - pct_free / 100) / 8192 actual_block,   
       alloc_block,   
       1 - ((avg_row_len * num_rows / (1 - pct_free / 100) / 8192) / alloc_block) percen,   
       'alter table '||t.table_name||'enable row movement'||';' enable_row,   
       'alter table '||t.table_name||'shrink space'||';' ddl_shrink   
  from (select segment_name, sum(blocks) alloc_block   
          from user_extents   
        having sum(blocks) > 8   
         group by segment_name) a,   
       user_tables t   
 where a.segment_name = t.table_name   
   and num_rows is not null   
   and num_rows > 0   
 order by 4 desc;  




SELECT OWNER, SEGMENT_NAME TABLE_NAME, SEGMENT_TYPE TYPE,                                            
      GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) "WASTE_PER(%)",  
      ROUND(BYTES/1024/1024, 2) "Total(M)", 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 BLOCKS > 128    
AND OWNER in (user_name1,user_name2)                                                               
ORDER BY 1 ASC, 4 DESC; 

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

转载于:http://blog.itpub.net/29500582/viewspace-1402915/