在归档模式和非归档模式下,设定表为logging和nologging,测量普通的insert 和insert /*+append*/生成redo大小。第一次测试我对下列结果报怀疑态度,于是又重新测试一遍,结果差不多,想想是测试的数量太少。如果是在正式环境上做数据迁移,最好就选择最后一种模式。
模式 | 普通insert下redo生成量(Byte) | insert/*+append*/下redo生成量(Byte) |
非归档模式(表logging) | 5706324 | 88340 |
非归档模式(表nologging) | 5685864 | 63348 |
归档模式(表logging) | 5686196 | 63424 |
归档模式(表nologging) | 5685364 | 63348 |
1.非归档模式
sql> create view m_redo as 2 select value 3 from v$sysstat,v$statname 4 where v$sysstat.statistic# =v$statname.statistic# 5 and v$statname.name ='redo size'; sql> create table test as select * from dba_objects where 1=0;
1.1表为logging
sql> select * from m_redo; VALUE ---------- 9236752 sql> insert into test select * from dba_objects;
已创建50325行。
sql> commit; sql> select * from m_redo; VALUE ---------- 14943076 sql> select (14943076-9236752)/1024/1024||'M' from dual; (14943076-9236752)/10 --------------------- 5.441974639892578125M sql> truncate table test; sql> select * from m_redo; VALUE ---------- 15048424 sql> insert /*+append*/ into test select * from dba_objects;
sql> commit; sql> select * from m_redo; VALUE ---------- 15136764 sql> select (15136764-15048424)/1024/1024||'M' from dual; (15136764-15048424)/ -------------------- .084247589111328125M
1.2表为nologging
sql> truncate table test; sql> alter table test nologging; sql> select * from m_redo; VALUE ---------- 15190864 sql> insert into test select * from dba_objects;
sql> commit; sql> select * from m_redo; VALUE ---------- 20876728 sql> select (20876728-15190864)/1024/1024||'M' from dual; (20876728-15190864)/ -------------------- 5.42246246337890625M sql> truncate table test; sql> select * from m_redo; VALUE ---------- 20937256 sql> insert /*+append*/ into test select * from dba_objects; 已创建50325行。 sql> commit; sql> select * from m_redo; VALUE ---------- 21000604 sql> select (21000604-20937256)/1024/1024||'M' from dual; (21000604-20937256)/ -------------------- .060413360595703125M
2.归档模式
在重新开一个session
sql> conn test/test
已连接。
sql>shutdown immediate sql>startup mount sql>alter database archivelog sql> alter database open;
数据库已更改。
sql> truncate table test;
表被截断。
sql> select * from m_redo; VALUE ---------- 130848 sql> insert into test select * from dba_objects;
已创建50325行。
sql> commit; sql> select * from m_redo; VALUE ---------- 5817044 sql> select (5817044-130848)/1024/1024||'M' from dual; (5817044-130848)/1024 --------------------- 5.422779083251953125M sql> truncate table test; sql> select * from m_redo; VALUE ---------- 5879644 sql> insert /*+append*/ into test select * from dba_objects;
sql> commit; sql> select * from m_redo; VALUE ---------- 5943068 sql> select (5943068-5879644)/1024/1024||'M' from dual; (5943068-5879644 ---------------- .06048583984375M
sql> truncate table test; sql> alter table test nologging;
表已更改。
sql> select * from m_redo; VALUE ---------- 5990988 sql> insert into test select * from dba_objects;
sql> commit; sql> select * from m_redo; VALUE ---------- 11676352 sql> select (11676352-5990988)/1024/1024||'M' from dual; (11676352-5990988)/10 --------------------- 5.421985626220703125M sql> truncate table test;。 sql> select * from m_redo; VALUE ---------- 11736328 sql> insert /*+append*/ into test select * from dba_objects;
sql> commit; sql> select * from m_redo; VALUE ---------- 11799676 sql> select (11799676-11736328)/1024/1024||'M' from dual; (11799676-11736328)/ -------------------- .060413360595703125M