Oracle中shrink space命令详解[转]－－还示测试
我们接下来讨论一下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<20000;
Table created
SQL> create table my_objects1 tablespace HWM as select * from all_objects where rownum<20000;
Table created
SQL> 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 on my_objects (object_id);
delete from my_objects where object_name like '%C%';
delete from my_objects where object_name like '%U%';
现在我们来shrink table my_objects：
SQL> alter table my_objects enable row movement;
Table altered
SQL> alter table my_objects shrink space;
Table altered
SQL> select index_name,status from user_indexes where index_name='I_MY_OBJECTS';
INDEX_NAME STATUS
------------------------------ --------
I_MY_OBJECTS VALID
我们发现，table my_objects上的index的状态为VALID，估计shrink在移动行数据时，也一起维护了index上相应行的数据rowid的信息。我们认为，这是对于move操作后需要rebuild index的改进。但是如果一个table上的index数量较多，我们知道，维护index的成本是比较高的，shrink过程中用来维护index的成本也会比较高。
b. shrink时对table的lock
在对table进行shrink时，会对table进行怎样的锁定呢？当我们对table MY_OBJECTS进行shrink操作时，查询v$locked_objects视图可以发现，table MY_OBJECTS上加了row-X (SX) 的lock：
SQL>select OBJECT_ID, SESSION_ID,ORACLE_USERNAME,LOCKED_MODE from v$locked_objects;
OBJECT_ID SESSION_ID ORACLE_USERNAME LOCKED_MODE
---------- ---------- ------------------ -----------
55422 153 DLINGER 3
SQL> select object_id from user_objects where object_name = 'MY_OBJECTS';
OBJECT_ID
----------
55422
那么，当table在进行shrink时，我们对table是可以进行DML操作的。
c. shrink对空间的要求
我们在前面讨论了shrink的数据的移动机制，既然oracle是从后向前移动行数据，那么，shrink的操作就不会像move一样，shrink不需要使用额外的空闲空间
source:http://www.poluoluo.com/jzxy/200812/54271_5.html
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<20000;
Table created
SQL> create table my_objects1 tablespace HWM as select * from all_objects where rownum<20000;
Table created
SQL> 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 on my_objects (object_id);
delete from my_objects where object_name like '%C%';
delete from my_objects where object_name like '%U%';
现在我们来shrink table my_objects：
SQL> alter table my_objects enable row movement;
Table altered
SQL> alter table my_objects shrink space;
Table altered
SQL> select index_name,status from user_indexes where index_name='I_MY_OBJECTS';
INDEX_NAME STATUS
------------------------------ --------
I_MY_OBJECTS VALID
我们发现，table my_objects上的index的状态为VALID，估计shrink在移动行数据时，也一起维护了index上相应行的数据rowid的信息。我们认为，这是对于move操作后需要rebuild index的改进。但是如果一个table上的index数量较多，我们知道，维护index的成本是比较高的，shrink过程中用来维护index的成本也会比较高。
b. shrink时对table的lock
在对table进行shrink时，会对table进行怎样的锁定呢？当我们对table MY_OBJECTS进行shrink操作时，查询v$locked_objects视图可以发现，table MY_OBJECTS上加了row-X (SX) 的lock：
SQL>select OBJECT_ID, SESSION_ID,ORACLE_USERNAME,LOCKED_MODE from v$locked_objects;
OBJECT_ID SESSION_ID ORACLE_USERNAME LOCKED_MODE
---------- ---------- ------------------ -----------
55422 153 DLINGER 3
SQL> select object_id from user_objects where object_name = 'MY_OBJECTS';
OBJECT_ID
----------
55422
那么，当table在进行shrink时，我们对table是可以进行DML操作的。
c. shrink对空间的要求
我们在前面讨论了shrink的数据的移动机制，既然oracle是从后向前移动行数据，那么，shrink的操作就不会像move一样，shrink不需要使用额外的空闲空间
source:http://www.poluoluo.com/jzxy/200812/54271_5.html
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/27036311/viewspace-757433/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- Docker启动失败：Failed to start Docker Application Container Engine 解决办法
- MySQL配置文件
- 如履薄冰
- Windows 批处理之DATE命令的使用方法
- nginx 常见参数以及重定向参数配置
- Oracle 低版本客户端连接 18c 报ORA-28040 和 ORA-01017 错误的解决方法
- Linux(CentOS)下安装Redis(redis-4.0.1)
- “穷清华，富北大不要命的中科大”究竟是什么意思
- CentOS7.1安装Oracle 12.1客户端以及cx_Oracle
- 深度分析Linux下双网卡绑定七种模式 多网卡的7种bond模式原理
