关于MOVE的一系列redo size的实验

我们首先需要了解一下move的机制：

http://blog.youkuaiyun.com/dlinger/archive/2004/07/12/39667.aspx

现在看看tablespace的信息：

SQL> select tablespace_name,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces where tablespace_name in('ASSM','HWM');

 

TABLESPACE_NAME                SEGMENT_SPACE_MANAGEMENT

 

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

 

ASSM                                          AUTO

 

HWM                                           MANUAL

 

Oracle版本信息：

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

 

 

1．归档模式下： segment ASSM

 

SQL> create table my_objects  tablespace ASSM as

select * from all_objects where rownum<20000;

 

Table created

 

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

 

7278 rows deleted

 

SQL> commit;

 

Commit complete

 

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

 

2732 rows deleted

 

SQL> commit;

 

Commit complete

 

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

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

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

 

     VALUE

----------

   5991164

 

SQL> alter table my_objects move;

 

Table altered

 

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

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

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

 

     VALUE

----------

   7220956

 

SQL> select 7220956-5991164 from dual;

 

7220956-5991164

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

        1229792

 

redo size = 1229792 约 1M

 

 

 

2.归档模式下：segment FLM

 

SQL> create table my_objects tablespace HWM 

as select * from all_objects where rownum<20000;

 

Table created

 

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

 

7278 rows deleted

 

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

 

2732 rows deleted

 

SQL> commit;

 

Commit complete

 

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

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

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

 

     VALUE

----------

   5929756

 

SQL> alter table my_objects move;

 

Table altered

 

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

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

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

 

     VALUE

----------

   7146592

 

SQL> select 7146592 - 5929756 from dual;

 

7146592-5929756

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

        1216836

 

Redo size = 1216836约 1M

我们发现，在归档模式下，segment不管是ASSM还是FLM，move产生的日志是一样多的。

 

3.非归档模式下：segment ASSM

 

SQL> create table my_objects  tablespace ASSM

as select * from all_objects where rownum<20000;

 

Table created

 

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

 

7278 rows deleted

 

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

 

2732 rows deleted

 

SQL> commit;

 

Commit complete

 

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

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

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

 

     VALUE

----------

   3754152

 

SQL> alter table my_objects move;

 

Table altered

 

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

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

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

 

     VALUE

----------

   4020876

 

SQL> select 4020876-3754152 from dual;

 

4020876-3754152

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

         266724

 

Redo size = 266724 约250K

 

 

4.非归档模式下：segment FLM

 

SQL> create table my_objects tablespace HWM 

as select * from all_objects where rownum<20000;

 

Table created

 

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

 

7278 rows deleted

 

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

 

2732 rows deleted

 

SQL> commit;

 

Commit complete

 

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

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

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

 

     VALUE

----------

   7747688

 

SQL> alter table my_objects move;

 

Table altered

 

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

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

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

 

     VALUE

----------

   7829084

 

SQL> select 7829084 - 7747688  from dual;

 

7829084-7747688

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

          81396

 

Redo size = 81396  约80K

 

通过这几个实验，我们发现，对table进行move操作时，在非归档模式下生成的redo size要比归档模式下少的多;非归档模式下，在FLM下比ASSM下生成的redo size也要少一些.

 

                    归档模式                   非归档模式

FLM               约 1M                       约80K

ASSM             约 1M                       约250K

5．我们再来看看在归档模式下table为nologging的情况，这里用FLM来测试：

SQL> create table my_objects tablespace HWM as select * from

all_objects where rownum<20000;

 

Table created

 

SQL> alter table my_objects nologging;

 

Table altered

 

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

 

7278 rows deleted

 

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

 

2732 rows deleted

 

SQL> commit;

 

Commit complete

 

SQL> select value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic#  and  v$statname.name = 'redo size';

 

     VALUE

----------

  10874352

 

SQL> alter table my_objects move;

 

Table altered

 

SQL> select value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic#  and  v$statname.name = 'redo size';

 

     VALUE

----------

  10955704

 

SQL> select 10955704-10874352 from dual;

 

10955704-10874352

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

            81352

 

Redo size = 81352  约80K

这时，redo size的生成量和非归档模式的FLM下一样了。

 

我们发现，table move操作生成的日志在归档和非归档模式以及nologging下的redo size的结果和insert append生成redo size的情况非常类似。

http://blog.youkuaiyun.com/dlinger/archive/ 2004/07/14 /41359.aspx

http://www.itpub.net/showthread.php?threadid=242761

关于move和shrink的日志生成对比，参见：

http://blog.youkuaiyun.com/dlinger/archive/ 2004/07/14 /41364.aspx