move和shrink的redo size的一点探讨

我们先了解一下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也没有影响了。