表碎片整理测试、原理与实施风险

2020-08-18 00:00:00 数据执行业务方式碎片

表碎片测试

[root@dg2 ~]# cat 20191207FragmentTest.txt

create tablespace testlin datafile '/u01/app/oracle/oradata/prod/testlin01.dbf' size 20m;

create table scott.testlin tablespace testlin as select * from all_objects;

exec dbms_stats.gather_table_stats('SCOTT','TESTLIN',cascade=>true);

此时都是满块，没有任何DML操作，都是插入数据，随后测试满块和随机删除数据后表段空间使用情况

**************************************************************************************************************************

drop table scott.testlin purge;

alter table scott.testlin enable row movement;

alter table scott.testlin shrink space;

create table scott.testlin tablespace testlin as select * from all_objects;

drop table scott.testlin purge

select table_name,blocks,num_rows,last_analyzed,round((1-(round((avg_row_len*num_rows),2)/1024/1024)/(round((blocks*8),2)/1024)),2)*100||'%' freerto from dba_tables where owner='SCOTT' and blocks>0 and table_name='TESTLIN';

set serveroutput on

declare

v_unformatted_blocks number;

v_unformatted_bytes number;

v_fs1_blocks number;

v_fs1_bytes number;

v_fs2_blocks number;

v_fs2_bytes number;

v_fs3_blocks number;

v_fs3_bytes number;

v_fs4_blocks number;

v_fs4_bytes number;

v_full_blocks number;

v_full_bytes number;

begin

dbms_space.space_usage ('&owner', '&tab_name', 'TABLE', v_unformatted_blocks,

v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,

v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);

dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);

dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);

dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);

dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);

dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);

dbms_output.put_line('Full Blocks = '||v_full_blocks);

end;

**************************************************************************************************************************

满块两种方式统计结果

SYS@prod> select table_name,blocks,num_rows,last_analyzed,round((1-(round((avg_row_len*num_rows),2)/1024/1024)/(round((blocks*8),2)/1024)),2)*100||'%' freerto from dba_tables where owner='SCOTT' and blocks>0 and table_name='TESTLIN';

TABLE_NAME BLOCKS NUM_ROWS LAST_ANAL FREERTO

------------------------------ ---------- ---------- --------- -----------------------------------------

TESTLIN 1242 84989 07-DEC-19 18%

SYS@prod> set serveroutput on

SYS@prod> declare

2 v_unformatted_blocks number;

3 v_unformatted_bytes number;

4 v_fs1_blocks number;

5 v_fs1_bytes number;

6 v_fs2_blocks number;

7 v_fs2_bytes number;

8 v_fs3_blocks number;

9 v_fs3_bytes number;

10 v_fs4_blocks number;

11 v_fs4_bytes number;

12 v_full_blocks number;

13 v_full_bytes number;

14 begin

15 dbms_space.space_usage ('SCOTT', 'TESTLIN', 'TABLE', v_unformatted_blocks,

16 v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,

17 v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);

18 dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);

19 dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);

20 dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);

21 dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);

22 dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);

23 dbms_output.put_line('Full Blocks = '||v_full_blocks);

24 end;

25 /

Unformatted Blocks = 0

FS1 Blocks = 0

FS2 Blocks = 0

FS3 Blocks = 0

FS4 Blocks = 0

Full Blocks = 1214

PL/SQL procedure successfully completed.

第二种方式更科学（ASSM方式），种属于MSSM方式

SYS@prod> delete from scott.testlin where mod(object_id,3) = 0;

28325 rows deleted.

SYS@prod> exec dbms_stats.gather_table_stats('SCOTT','TESTLIN',cascade=>true);

PL/SQL procedure successfully completed.

继续使用两种方式计算碎片

TABLE_NAME BLOCKS NUM_ROWS LAST_ANAL FREERTO

------------------------------ ---------- ---------- --------- -----------------------------------------

TESTLIN 1242 56664 07-DEC-19 45%

SYS@prod> declare

2 v_unformatted_blocks number;

3 v_unformatted_bytes number;

4 v_fs1_blocks number;

5 v_fs1_bytes number;

6 v_fs2_blocks number;

7 v_fs2_bytes number;

8 v_fs3_blocks number;

9 v_fs3_bytes number;

10 v_fs4_blocks number;

11 v_fs4_bytes number;

12 v_full_blocks number;

13 v_full_bytes number;

14 begin

15 dbms_space.space_usage ('SCOTT', 'TESTLIN', 'TABLE', v_unformatted_blocks,

16 v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,

17 v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);

18 dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);

19 dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);

20 dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);

21 dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);

22 dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);

23 dbms_output.put_line('Full Blocks = '||v_full_blocks);

24 end;

25 /

Unformatted Blocks = 0

FS1 Blocks = 0

FS2 Blocks = 1213

FS3 Blocks = 0

FS4 Blocks = 0

Full Blocks = 1

PL/SQL procedure successfully completed.

确实显示1213个块25-50%空间free,碎片严重

此时查询段大小，空间没有释放。

SYS@prod> select blocks from dba_segments where segment_name='TESTLIN';

BLOCKS

----------

1280

在消除碎片之前，创建索引，测试shrink space对索引的影响

SYS@prod> create index scott.idx_testlin_id on scott.testlin(object_id);

Index created.

SYS@prod> select index_name,status from dba_indexes where owner='SCOTT';

INDEX_NAME STATUS

------------------------------ --------

PK_DEPT VALID

PK_EMP VALID

IDX_TESTLIN_ID VALID

下面执行shrink space

SYS@prod> alter table scott.testlin enable row movement;

Table altered.

SYS@prod> alter table scott.testlin shrink space;

Table altered.

种方式

TABLE_NAME BLOCKS NUM_ROWS LAST_ANAL FREERTO

------------------------------ ---------- ---------- --------- -----------------------------------------

TESTLIN 1242 56664 07-DEC-19 45%

此时需要先收集统计信息，在执行该查询，否则剩余空间率不变

收集统计信息再收集

第二种方式

SYS@prod> declare

2 v_unformatted_blocks number;

3 v_unformatted_bytes number;

4 v_fs1_blocks number;

5 v_fs1_bytes number;

6 v_fs2_blocks number;

7 v_fs2_bytes number;

8 v_fs3_blocks number;

9 v_fs3_bytes number;

10 v_fs4_blocks number;

11 v_fs4_bytes number;

12 v_full_blocks number;

13 v_full_bytes number;

14 begin

15 dbms_space.space_usage ('SCOTT', 'TESTLIN', 'TABLE', v_unformatted_blocks,

16 v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,

17 v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);

18 dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);

19 dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);

20 dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);

21 dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);

22 dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);

23 dbms_output.put_line('Full Blocks = '||v_full_blocks);

24 end;

25 /

Unformatted Blocks = 0

FS1 Blocks = 0

FS2 Blocks = 2

FS3 Blocks = 0

FS4 Blocks = 0

Full Blocks = 807

含义如下：

unformatted_blocks : Total number of blocks unformatted

fs1_blocks : Number of blocks having at least 0 to 25% free space

fs2_blocks : Number of blocks having at least 25 to 50% free space

fs3_blocks : Number of blocks having at least 50 to 75% free space

fs4_blocks : Number of blocks having at least 75 to free space

ful1_blocks : Total number of blocks full in the segment

PL/SQL procedure successfully completed.

比较准确，此时807个块是满块，碎片消除

再次查询段大小

SYS@prod> select blocks from dba_segments where segment_name='TESTLIN';

BLOCKS

----------

832

SYS@prod> exec dbms_stats.gather_table_stats('SCOTT','TESTLIN',cascade=>true);

PL/SQL procedure successfully completed.

TABLE_NAME BLOCKS NUM_ROWS LAST_ANAL FREERTO

------------------------------ ---------- ---------- --------- -----------------------------------------

TESTLIN 809 56664 07-DEC-19 16%

此时在表段testlin几乎满块的情况下freerto依然16%，这个计算是不准确的，只能作为参考

下面提高采样率，发现freerto依然不变，再次这种方法判断碎片严重程度不准确，好使用第二种方法判断碎片严重程度以及碎片整理的效果。

SYS@prod> exec dbms_stats.gather_table_stats('SCOTT','TESTLIN',estimate_percent=>100);

PL/SQL procedure successfully completed.

TABLE_NAME BLOCKS NUM_ROWS LAST_ANAL FREERTO

------------------------------ ---------- ---------- --------- -----------------------------------------

TESTLIN 809 56664 07-DEC-19 16%

下面查询下表的定义，发现 PCTFREE 10,也就是freerto少为10%，此时应该是满块，索引，从测试看这个数值在15-20之间可以不用考虑碎片，》20可以认为有碎片

碎片占空间大约为(freerto-20%)表段大小

SYS@prod> select dbms_metadata.get_ddl('TABLE','TESTLIN','SCOTT') from dual;

DBMS_METADATA.GET_DDL('TABLE','TESTLIN','SCOTT')

--------------------------------------------------------------------------------

CREATE TABLE "SCOTT"."TESTLIN"

( "OWNER" VARCHAR2(30) NOT NULL ENABLE,

"OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,

"SUBOBJECT_NAME" VARCHAR2(30),

"OBJECT_ID" NUMBER NOT NULL ENABLE,

"DATA_OBJECT_ID" NUMBER,

"OBJECT_TYPE" VARCHAR2(19),

"CREATED" DATE NOT NULL ENABLE,

"LAST_DDL_TIME" DATE NOT NULL ENABLE,

"TIMESTAMP" VARCHAR2(19),

"STATUS" VARCHAR2(7),

"TEMPORARY" VARCHAR2(1),

"GENERATED" VARCHAR2(1),

"SECONDARY" VARCHAR2(1),

"NAMESPACE" NUMBER NOT NULL ENABLE,

"EDITION_NAME" VARCHAR2(30)

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "TESTLIN" ENABLE ROW MOVEMENT

计算碎片的方式

HWM= dba_segments.blocks - dba_tables.empty_blocks

Non_empty=dba_tables.blocks

Fragment=HWM – Non_empty

Note: empty blocks are higher than HWM, so delete will not change empty blocks.

select dba_segments.blocks segment_blocks, dba_tables.empty_blocks, dba_tables.blocks as table_blocks, (dba_segments.blocks- dba_tables.empty_blocks)- dba_tables.blocks as Fragment_blocks from dba_segments, dba_tables where dba_segments.segment_name='TESTLIN' and dba_tables.table_name=dba_segments.segment_name;

表碎片整理的原理以及风险

segment shrink分为两个阶段：

1、数据重组(compact):通过一系列insert、delete操作，将数据尽量排列在段的前面。在这个过程中需要在表上加RX锁，即只在需要移动的行上加锁。由于涉及到rowid的改变，需要enable row movement.同时要disable基于rowid的trigger.这一过程对业务影响比较小。

2、HWM调整：第二阶段是调整HWM位置，释放空闲数据块。此过程需要在表上加X锁，会造成表上的所有DML语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。

shrink space语句两个阶段都执行。

shrink space compact只执行个阶段。

如果系统业务比较繁忙，可以先执行shrink space compact重组数据,然后在业务不忙的时候再执行shrink space降低HWM释放空闲数据块。

shrink必须开启行迁移功能。

alter table table_name enable row movement ;

测试shrink space期间的全表扫描，会报invalid rowid错误，对生产库还是有影响

如果系统业务比较繁忙，可以先执行shrink space compact重组数据,然后在业务不忙的时候再执行shrink space降低HWM释放空闲数据块。

相关文章