nologging与append测试
早前解决提升批量插入性能时的做的测试，翻出来，贴一下。
在对数据进行批量insert时，正确使用nologging和append，不仅能够极大的操作提高效率同时也能免去不必要的redo日志。通过以下4个实验看他们的正确使用。
环境：
OS：WindowsXP
DB：Oracle9.2.0.1
1，实验一：归档模式下，创建表不使用nologging
代码
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination E:\oracle\ora92\RDBMS
Oldest online log sequence 12
Next log sequence to archive 14
Current log sequence 14
SQL> create table ttt as select object_id,object_name from dba_objects where 1=0;
Table created.
SQL> column sid format 9999
SQL> column name format a50
SQL> column value format 999999999
SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b
2 where a.statistic#=b.statistic# and a.name like 'redo size%';
SID NAME VALUE
----- -------------------------------------------------- ----------
9 redo size 1469088
SQL> /
SID NAME VALUE
----- -------------------------------------------------- ----------
9 redo size 1469088
SQL> insert into ttt select object_id,object_name from dba_objects;
30899 rows created.
SQL> commit;
Commit complete.
SQL> column sid format 9999
SQL> column name format a50
SQL> column value format 999999999
SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b
2 where a.statistic#=b.statistic# and a.name like 'redo size%';
SID NAME VALUE
----- -------------------------------------------------- ----------
9 redo size 2775240
SQL> /
SID NAME VALUE
----- -------------------------------------------------- ----------
9 redo size 2775240
SQL> insert /*+APPEND*/into ttt select object_id,object_name from dba_objects;
30899 rows created.
SQL> commit;
Commit complete.
SQL> column sid format 9999
SQL> column name format a50
SQL> column value format 999999999
SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b
2 where a.statistic#=b.statistic# and a.name like 'redo size%';
SID NAME VALUE
----- -------------------------------------------------- ----------
9 redo size 4019400
SQL> /
SID NAME VALUE
----- -------------------------------------------------- ----------
9 redo size 4019400
SQL> select (2775240-1469088) noappend,(4019400-2775240) append from dual;
NOAPPEND APPEND
---------- ----------
1306152 1244160
总结：在归档模式下，创建表时不使用nologging参数，对日后的表操作没有实质性影响，加与不加apped参数生成的redo日志基本相同，效率得不到提升。
2，实验二：归档模式下，创建表使用nologging
代码
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination E:\oracle\ora92\RDBMS
Oldest online log sequence 12
Next log sequence to archive 14
Current log sequence 14
SQL> create table tttt nologging as select object_id,object_name from dba_objects;
Table created.
SQL> column sid format 9999
SQL> column name format a50
SQL> column value format 999999999
SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b
2 where a.statistic#=b.statistic# and a.name like 'redo size%';
SID NAME VALUE
----- -------------------------------------------------- ----------
9 redo size 7066492
SQL> /
SID NAME VALUE
----- -------------------------------------------------- ----------
9 redo size 7066492
SQL> insert into tttt select object_id,object_name from dba_objects;
30900 rows created.
SQL> commit;
Commit complete.
SQL> column sid format 9999
SQL> column name format a50
SQL> column value format 999999999
SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b
2 where a.statistic#=b.statistic# and a.name like 'redo size%';
SID NAME VALUE
----- -------------------------------------------------- ----------
9 redo size 8345292
SQL> /
SID NAME VALUE
----- -------------------------------------------------- ----------
9 redo size 8345292
SQL> insert /*+APPEND*/into tttt select object_id,object_name from dba_objects;
30900 rows created.
SQL> commit;
Commit complete.
SQL> column sid format 9999
SQL> column name format a50
SQL> column value format 999999999
SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b
2 where a.statistic#=b.statistic# and a.name like 'redo size%';
SID NAME VALUE
----- -------------------------------------------------- ----------
9 redo size 8399484
SQL> /
SID NAME VALUE
----- -------------------------------------------------- ----------
9 redo size 8399484
SQL> select (8399484-8345292) append,(8345292-7066492) noappend from dual;
APPEND NOAPPEND
---------- ----------
54192 1278800
3，实验三：非归档模式下，创建表不使用nologging
代码
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination E:\oracle\ora92\RDBMS
Oldest online log sequence 12
Current log sequence 14
SQL> create table tt as select object_id,object_name from dba_objects where 1=0;
Table created.
SQL> column sid format 9999
SQL> column name format a50
SQL> column value format 999999999
SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b
2 where a.statistic#=b.statistic# and a.name like 'redo size%';
SID NAME VALUE
----- -------------------------------------------------- ----------
9 redo size 3416124
SQL> /
SID NAME VALUE
----- -------------------------------------------------- ----------
9 redo size 3442132
SQL> /
SID NAME VALUE
----- -------------------------------------------------- ----------
9 redo size 3442132
SQL> insert /*+APPEND*/into tt
2 select object_id,object_name from dba_objects;
30898 rows created.
SQL> commit;
Commit complete.
SQL> column sid format 9999
SQL> column name format a50
SQL> column value format 999999999
SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b
2 where a.statistic#=b.statistic# and a.name like 'redo size%';
SID NAME VALUE
----- -------------------------------------------------- ----------
9 redo size 3467448
SQL> column sid format 9999
SQL> column name format a50
SQL> column value format 999999999
SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b
2 where a.statistic#=b.statistic# and a.name like 'redo size%';
SID NAME VALUE
----- -------------------------------------------------- ----------
9 redo size 3519424
SQL> insert into tt select object_id,object_name from dba_objects;
30898 rows created.
SQL> commit;
Commit complete.
SQL> column sid format 9999
SQL> column name format a50
SQL> column value format 999999999
SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b
2 where a.statistic#=b.statistic# and a.name like 'redo size%';
SID NAME VALUE
----- -------------------------------------------------- ----------
9 redo size 4798068
SQL> select (3467448-3442132) append,(4798068-3519424) noappend from dual;
APPEND NOAPPEND
---------- ----------
25316 1278644
4，实验四：非归档模式下，创建表使用nologging
代码
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination E:\oracle\ora92\RDBMS
Oldest online log sequence 12
Current log sequence 14
SQL> create table tt nologging as select object_id,object_name from dba_objects where 1=0;
Table created.
SQL> column sid format 9999
SQL> column name format a50
SQL> column value format 999999999
SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b
2 where a.statistic#=b.statistic# and a.name like 'redo size%';
SID NAME VALUE
----- -------------------------------------------------- ----------
9 redo size 246108
SQL> insert into tt select object_id,object_name from dba_objects;
30900 rows created.
SQL> commit;
Commit complete.
SQL> column sid format 9999
SQL> column name format a50
SQL> column value format 999999999
SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b
2 where a.statistic#=b.statistic# and a.name like 'redo size%';
SID NAME VALUE
----- -------------------------------------------------- ----------
9 redo size 1552440
SQL> /
SID NAME VALUE
----- -------------------------------------------------- ----------
9 redo size 1552440
SQL> insert /*+APPEND*/into tt select object_id,object_name from dba_objects;
30900 rows created.
SQL> commit;
Commit complete.
SQL> column sid format 9999
SQL> column name format a50
SQL> column value format 999999999
SQL> select b.sid,a.name,a.value from v$sysstat a,v$mystat b
2 where a.statistic#=b.statistic# and a.name like 'redo size%';
SID NAME VALUE
----- -------------------------------------------------- ----------
9 redo size 1580736
SQL> /
SID NAME VALUE
----- -------------------------------------------------- ----------
9 redo size 1580736
SQL> select (1580736-1552440) append,(1552440-246108) noappend from dual;
APPEND NOAPPEND
---------- ----------
28296 1306332
小结：
非归档模式下,不管创建表时nologging参数是否存在,只有使用append参数才能提高效率,减少redo生成量；
归档模式下,nologging和append参数同时使用，才能够提升效率，减少redo生成量。
