我们先了解一下move和shrink的机制:
http://blog.csdn.net/dlinger/archive/2004/07/12/39667.aspx
http://blog.csdn.net/dlinger/archive/2004/07/15/42491.aspx
测试环境:
oracle: 10.1.0.2.0? os:win2000 server?
非归档模式下的测试:
关于日志的问题,我们对比了同样数据量和分布状况的两张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的日志写要大得多。
move生成的日志量,在归档和非归档模式下是不一样的,具体信息,参考:
http://blog.csdn.net/dlinger/archive/2004/08/04/61874.aspx
使用logmnr来分析move和shrink的操作:
move:
SQL> select operation,count(*)?? from v$logmnr_contents group by operation;
OPERATION COUNT(*)
-------------------------------- ----------
COMMIT 36
DDL 1
DELETE 1
INTERNAL 137
START 37
UNSUPPORTED 46
UPDATE 32
7 rows selected
shrink:
SQL> select operation,count(*) from v$logmnr_contents group by operation;
OPERATION COUNT(*)
-------------------------------- ----------
COMMIT 171
DDL 1
DELETE 6248
INSERT 6248
INTERNAL 96
START 171
UNSUPPORTED 33
UPDATE 26
8 rows selected
我们看到,shrink的操作中,比move多了很多的delete和insert的操作,
这应该是shrink生成的redo size较多的原因了。
那么,我们在归档模式下进行shrink操作,生成的redo size和非归档的一样,
说明shrink不像move那样,受是否归档的影响,推测这也是因为shrink操作多是delete和insert的原因吧。
那么。table nologging的状态应该对shrink的redo size也没有影响了。