oracle 碎片高的表,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) || '%' 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) || '%' 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) || '%' 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,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;