Oracle 11g表的碎片率诊断

Love The Way You Lie 2022-07-12 02:08 260阅读 0赞

在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;