48、Oracle 10g的自动段空间管理（ASSM）

从10g开始,oracle开始提供Shrink的命令,假如我们的表空间中支持自动段空间管理 (ASSM),就可以使

用这个特性缩小段,即降低HWM.这里需要强调一点,10g的这个新特性,仅对ASSM表空间有效,否则会报

ORA-10635: Invalid segment or tablespace type.

在这里,我们来讨论如和对一个ASSM的segment回收浪费的空间.

同样,我们用系统视图all_objects来在tablespace ASSM上创建测试表my_objects,这一小节的内容,实验环

境为oracle10.1.0.2:

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod

PL/SQL Release 10.1.0.2.0 - Production

CORE 10.1.0.2.0 Production

TNS for 32-bit Windows: Version 10.1.0.2.0 - Production

NLSRTL Version 10.1.0.2.0 – Production

SQL> select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT,

2 ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT

3 from dba_tablespaces where TABLESPACE_NAME = 'ASSM';

TABLESPACE_NAME BLOCK_SIZE EXTENT_MANAGEMENT ALLOCATION_TYPE

SEGMENT_SPACE_MANAGEMENT

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

ASSM 8192 LOCAL UNIFORM AUTO

SQL> create table my_objects tablespace assm

2 as select * from all_objects;

Table created 然后我们随机地从table MY_OBJECTS中删除一部分数据:

SQL> select count(*) from my_objects;

COUNT(*)

----------

47828

SQL> delete from my_objects where object_name like '%C%';

16950 rows deleted

SQL> delete from my_objects where object_name like '%U%';

4503 rows deleted

SQL> delete from my_objects where object_name like '%A%';

6739 rows deleted 现在我们使用show_space和show_space_assm来看看my_objects的数据存储状况:

SQL> exec show_space('MY_OBJECTS','DLINGER');

Total Blocks............................680

Total Bytes.............................5570560

Unused Blocks...........................1

Unused Bytes............................8192

Last Used Ext FileId....................6

Last Used Ext BlockId...................793

Last Used Block.........................4

PL/SQL 过程已成功完成.

SQL> exec show_space_assm('MY_OBJECTS','DLINGER');

free space 0-25% Blocks:................0

free space 25-50% Blocks:...............205

free space 50-75% Blocks:...............180

free space 75-100% Blocks:..............229

Full Blocks:............................45

Unformatted blocks:.....................0

PL/SQL 过程已成功完成. 这里,table my_objects的HWM下有679个block,其中,free space为25-

50%的block有205个,free space为50-75%的block有180个,free space为75-100%的block有229个,full

space的block只有45个,这种情况下,我们需要对这个table的现有数据行进行重组.

要使用assm上的shink,首先我们需要使该表支持行移动,可以用这样的命令来完成:

alter table my_objects enable row movement;

现在,就可以来降低my_objects的HWM,回收空间了,使用命令:

alter table bookings shrink space;

我们具体的看一下实验的结果:

SQL> alter table my_objects enable row movement;

表已更改.

SQL> alter table my_objects shrink space;

表已更改.

SQL> exec show_space('MY_OBJECTS','DLINGER');

Total Blocks............................265

Total Bytes.............................2170880

Unused Blocks...........................2

Unused Bytes............................16384

Last Used Ext FileId....................6

Last Used Ext BlockId...................308

Last Used Block.........................3

PL/SQL 过程已成功完成.

SQL> exec show_space_assm('MY_OBJECTS','DLINGER');

free space 0-25% Blocks:................0

free space 25-50% Blocks:...............1

free space 50-75% Blocks:...............0

free space 75-100% Blocks:..............0

Full Blocks:............................249

Unformatted blocks:.....................0

PL/SQL 过程已成功完成. 在执行玩shrink命令后,我们可以看到,table my_objects的HWM现在降到了

264的位置,而且HWM下的block的空间使用状况,full space的block有249个,free space 为25-50%

Block只有1个.

我们接下来讨论一下shrink的实现机制,我们同样使用讨论move机制的那个实验来观察.

SQL> create table TEST_HWM (id int ,name char(2000)) tablespace ASSM;

Table created 往table test_hwm中插入如下的数据:

insert into TEST_HWM values (1,'aa');

insert into TEST_HWM values (2,'bb');

insert into TEST_HWM values (2,'cc');

insert into TEST_HWM values (3,'dd');

insert into TEST_HWM values (4,'ds');

insert into TEST_HWM values (5,'dss');

insert into TEST_HWM values (6,'dss');

insert into TEST_HWM values (7,'ess');

insert into TEST_HWM values (8,'es');

insert into TEST_HWM values (9,'es');

insert into TEST_HWM values (10,'es');

我们来看看这个table的rowid和block的ID和信息:

SQL> select rowid , id,name from TEST_HWM;

ROWID ID NAME

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

AAANhqAAGAAAAFHAAA 1 aa

AAANhqAAGAAAAFHAAB 2 bb

AAANhqAAGAAAAFHAAC 2 cc

AAANhqAAGAAAAFIAAA 3 dd

AAANhqAAGAAAAFIAAB 4 ds

AAANhqAAGAAAAFIAAC 5 dss

AAANhqAAGAAAAFJAAA 6 dss

AAANhqAAGAAAAFJAAB 7 ess

AAANhqAAGAAAAFJAAC 8 es

AAANhqAAGAAAAFKAAA 9 es

AAANhqAAGAAAAFKAAB 10 es

11 rows selected

SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS

2 from dba_extents where segment_name='TEST_HWM' ;

EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS

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

0 6 6 324 5

1 6 6 329 5 然后从table test_hwm中删除一些数据:

delete from TEST_HWM where id = 2; delete from TEST_HWM where id = 4; delete from TEST_HWM

where id = 3; delete from TEST_HWM where id = 7; delete from TEST_HWM where id = 8;

观察table test_hwm的rowid和blockid的信息:

SQL> select rowid , id,name from TEST_HWM;

ROWID ID NAME

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

AAANhqAAGAAAAFHAAA 1 aa

AAANhqAAGAAAAFIAAC 5 dss

AAANhqAAGAAAAFJAAA 6 dss

AAANhqAAGAAAAFKAAA 9 es

AAANhqAAGAAAAFKAAB 10 es

SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS

2 from dba_extents where segment_name='TEST_HWM' ;

EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS

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

0 6 6 324 5

1 6 6 329 5 从以上的信息,我们可以看到,在table test_hwm中,剩下的数据是分布在

AAAAFH,AAAAFI,AAAAFJ,AAAAFK这样四个连续的block中.

SQL> exec show_space_assm('TEST_HWM','DLINGER');

free space 0-25% Blocks:................0

free space 25-50% Blocks:...............1

free space 50-75% Blocks:...............3

free space 75-100% Blocks:..............3

Full Blocks:............................0

Unformatted blocks:.....................0 通过show_space_assm我们可以看到目前这四个block的空间使用状况

,AAAAFH,AAAAFI,AAAAFJ上各有一行数据,我们猜测free space为50-75%的3个block是这三个

block,那么free space为25-50%的1个block就是AAAAFK了,剩下free space为 75-100% 的3个block,是

HWM下已格式化的尚未使用的block.(关于assm下hwm的移动我们前面已经详细地讨论过了,在extent不

大于于16个block时,是以一个extent为单位来移动的)

然后,我们对table my_objects执行shtink的操作:

SQL> alter table test_hwm enable row movement;

Table altered

SQL> alter table test_hwm shrink space;

Table altered

SQL> select rowid ,id,name from TEST_HWM;

ROWID ID NAME

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

AAANhqAAGAAAAFHAAA 1 aa

AAANhqAAGAAAAFHAAB 10 es

AAANhqAAGAAAAFHAAD 9 es

AAANhqAAGAAAAFIAAC 5 dss

AAANhqAAGAAAAFJAAA 6 dss

SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS

2 from dba_extents where segment_name='TEST_HWM' ;

EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS

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

0 6 6 324 5

1 6 6 329 5 当执行了shrink操作后,有意思的现象出现了.我们来看看oracle是如何移动行数据的,这里的情

况和move已经不太一样了.我们知道,在move操作的时候,所有行的rowid都发生了变化,table所位于的

block的区域也发生了变化,但是所有行物理存储的顺序都没有发生变化,所以我们得到的结论是,oracle以

block为单位,进行了block间的数据copy.那么shrink后,我们发现,部分行数据的rowid发生了变化,同时

,部分行数据的物理存储的顺序也发生了变化,而table所位于的block的区域却没有变化,这就说明

,shrink只移动了table其中一部分的行数据,来完成释放空间,而且,这个过程是在table当前所使用的

block中完成的.

那么Oracle具体移动行数据的过程是怎样的呢 我们根据这样的实验结果,可以来猜测一下:

Oracle是以行为单位来移动数据的.Oracle从当前table存储的最后一行数据开始移动,从当前table最先使用

的block开始搜索空间,所以,shrink之前,rownum=10的那行数据(10,es),被移动到block AAAAFH上

,写到(1,aa)这行数据的后面,所以(10,es)的rownum和rowid同时发生改变.然后是(9,es)这行数据

,重复上述过程.这是oracle从后向前移动行数据的大致遵循的规则,那么具体移动行数据的的算法是比较复

杂的,包括向ASSM的table中insert数据使用block的顺序的算法也是比较复杂的,大家有兴趣的可以自己来研

究,在这里我们不多做讨论.

我们还可以在shrink table的同时shrink这个table上的index:

alter table my_objects shrink space cascade;

同样地,这个操作只有当table上的index也是ASSM时,才能使用.

关于日志的问题,我们对比了同样数据量和分布状况的两张table,在move和shrink下生成的redo

size(table上没有index的情况下):

SQL> select tablespace_name,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces

2 where tablespace_name in('ASSM','HWM');

TABLESPACE_NAME SEGMENT_SPACE_MANAGEMENT

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

ASSM AUTO

HWM MANUAL

SQL> create table my_objects tablespace ASSM as select * from all_objects where rownum create table my_objects1 tablespace HWM as select * from all_objects where rownum select bytes/1024/1024 from user_segments where segment_name = 'MY_OBJECTS';

BYTES/1024/1024

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

2.1875

SQL> delete from my_objects where object_name like '%C%';

7278 rows deleted

SQL> delete from my_objects1 where object_name like '%C%';

7278 rows deleted

SQL> delete from my_objects where object_name like '%U%';

2732 rows deleted

SQL> delete from my_objects1 where object_name like '%U%';

2732 rows deleted

SQL> commit;

Commit complete

SQL> alter table my_objects enable row movement;

Table altered

SQL> select value from v$mystat, v$statname

2 where v$mystat.statistic# = v$statname.statistic#

3 and v$statname.name = 'redo size';

VALUE

----------

27808792

SQL> alter table my_objects shrink space;

Table altered

SQL> select value from v$mystat, v$statname

2 where v$mystat.statistic# = v$statname.statistic#

3 and v$statname.name = 'redo size';

VALUE

----------

32579712

SQL> alter table my_objects1 move;

Table altered

SQL> select value from v$mystat, v$statname

2 where v$mystat.statistic# = v$statname.statistic#

3 and v$statname.name = 'redo size';

VALUE

----------

32676784 对于table my_objects,进行shrink,产生了32579712 – 27808792=4770920,约4.5M的redo

;对table my_objects1进行move,产生了32676784-32579712= 97072,约95K的redo size.那么,与

move比较起来,shrink的日志写要大得多.

Shrink的几点问题:

a. shrink后index是否需要rebuild:

因为shrink的操作也会改变行数据的rowid,那么,如果table上有index时,shrink table后index会不会变为

UNUSABLE呢 我们来看这样的实验,同样构建my_objects的测试表:

create table my_objects tablespace ASSM as select * from all_objects where rownum<20000;

create index i_my_objects _disibledevent= 'MY_OBJECTS';

OBJECT_ID

----------

55422 那么,当table在进行shrink时,我们对table是可以进行DML操作的.

c. shrink对空间的要求

我们在前面讨论了shrink的数据的移动机制,既然oracle是从后向前移动行数据,那么,shrink的操作就不会像

move一样,shrink不需要使用额外的空闲空间. 2008-7-29 9:56:59