Oracle 11g表的碎片率诊断

 在Oracle中，有些表由于频繁的插入和删除数据，导致高水位过高，表的碎片也很高，如何判断呢？

drop table test purge;

create table test as select * from dba_objects;
insert into test select * from test;
insert into test select * from test;
commit;


select trunc(real_size('TEST')/1024/1024, 2) real_size,
       bytes /1024/1024||'M' seg_size,
       trunc((1 - real_size('TEST')/ bytes) * 100, 2) || '%' frag_ratio
  from user_segments s where s.segment_name='TEST';
 REAL_SIZE SEG_SIZE    FRAG_RATIO
---------- ----------- -------------
      32.8 33M         .58%
      
select object_type, count(1)
  from test
 group by object_type
 having count(1) >5000
 order by count(1) desc;      
OBJECT_TYPE           COUNT(1)
------------------- ----------
SYNONYM                 111492
JAVA CLASS               91668
VIEW                     20656
INDEX                    15604
TABLE                    11580
TYPE                     11292
PACKAGE                   5632
PACKAGE BODY              5344

--可以看到随着数据的不断删除，碎片率在升高
delete from test where object_type in('SYNONYM','JAVA CLASS');
commit;
select trunc(real_size('TEST')/1024/1024, 2) real_size,
       bytes /1024/1024||'M' seg_size,
       trunc((1 - real_size('TEST')/ bytes) * 100, 2) || '%' frag_ratio
  from user_segments s where s.segment_name='TEST';
 REAL_SIZE SEG_SIZE    FRAG_RATIO
---------- ----------- -------------
      12     33M         63.62%
delete from test where object_type in('VIEW','INDEX','TABLE','TYPE');
commit;
select trunc(real_size('TEST')/1024/1024, 2) real_size,
       bytes /1024/1024||'M' seg_size,
       trunc((1 - real_size('TEST')/ bytes) * 100, 2) || '%' frag_ratio
  from user_segments s where s.segment_name='TEST';
 REAL_SIZE SEG_SIZE    FRAG_RATIO
---------- ----------- -------------

      6.59     33M         80.02%  

--把表收缩一下，碎片率下降了

alter table test enable row movement;
alter table test shrink space;
select trunc(real_size('TEST')/1024/1024, 2) real_size,
       bytes /1024/1024||'M' seg_size,
       trunc((1 - real_size('TEST')/ bytes) * 100, 2) || '%' frag_ratio
  from user_segments s where s.segment_name='TEST';
 REAL_SIZE SEG_SIZE    FRAG_RATIO
---------- ----------- -------------
     3.2     3.25M      1.53%

附录是Tom kyte提供的检查脚本：

CREATE OR REPLACE FUNCTION REAL_SIZE(
P_SEGNAME IN VARCHAR2,
P_OWNER IN VARCHAR2 DEFAULT USER,
P_TYPE IN VARCHAR2 DEFAULT 'TABLE')
RETURN NUMBER AUTHID CURRENT_USER AS
L_TOTAL_BLOCKS NUMBER;
L_TOTAL_BYTES NUMBER;
L_UNUSED_BLOCKS NUMBER;
L_UNUSED_BYTES NUMBER;
L_LASTUSEDEXTFILEID NUMBER;
L_LASTUSEDEXTBLOCKID NUMBER;
L_LAST_USED_BLOCK NUMBER;
L_UNFORMATTED_BLOCKS NUMBER;
L_UNFORMATTED_BYTES NUMBER;
L_FS1_BLOCKS NUMBER;
L_FS1_BYTES NUMBER;
L_FS2_BLOCKS NUMBER;
L_FS2_BYTES NUMBER;
L_FS3_BLOCKS NUMBER;
L_FS3_BYTES NUMBER;
L_FS4_BLOCKS NUMBER;
L_FS4_BYTES NUMBER;
L_FULL_BLOCKS NUMBER;
L_FULL_BYTES NUMBER;
T_TOTAL_BYTES NUMBER;
T_FS_BYTES NUMBER;
P_PART_NAME VARCHAR2(30);

BEGIN
DBMS_SPACE.SPACE_USAGE(
P_OWNER,
P_SEGNAME,
P_TYPE,
L_UNFORMATTED_BLOCKS,
L_UNFORMATTED_BYTES,
L_FS1_BLOCKS,
L_FS1_BYTES,
L_FS2_BLOCKS,
L_FS2_BYTES,
L_FS3_BLOCKS,
L_FS3_BYTES,
L_FS4_BLOCKS,
L_FS4_BYTES,
L_FULL_BLOCKS,
L_FULL_BYTES,
P_PART_NAME);

DBMS_SPACE.UNUSED_SPACE(
P_OWNER,
P_SEGNAME,
P_TYPE,
L_TOTAL_BLOCKS,
L_TOTAL_BYTES,
L_UNUSED_BLOCKS,
L_UNUSED_BYTES,
L_LASTUSEDEXTFILEID,
L_LASTUSEDEXTBLOCKID,
L_LAST_USED_BLOCK,
P_PART_NAME);

T_FS_BYTES := L_FS1_BYTES * 0.25 / 2 + L_FS2_BYTES * (0.5 + 0.25) / 2 +L_FS3_BYTES * (0.75 + 0.5) / 2 +L_FS4_BYTES * (1 + 0.75) / 2 + L_UNUSED_BYTES;
T_TOTAL_BYTES := L_TOTAL_BYTES;
RETURN T_TOTAL_BYTES-T_FS_BYTES;

EXCEPTION
WHEN OTHERS THEN
RETURN 1;
END;