最初的问题是这个帖子:
请大家仔细看那些测试的例子. 看了Tom的解释,始终觉得牵强. 开始以为可能是bug 经过观察和测试,终于发现了Nologging的秘密我们知道,Nologging只在很少情况下生效
通常,DML操作总是要生成redo的这个我们不多说.
关于Nologging和append,一直存在很多误解.
经过一系列研究,终于发现了Nologging的真相.我们来看一下测试:
1.Nologging的设置跟数据库的运行模式有关
a.数据库运行在非归档模式下:
SQL> archive log list;Database log mode No Archive ModeAutomatic archival EnabledArchive destination /opt/oracle/oradata/hsjf/archiveOldest online log sequence 155Current log sequence 157SQL> @redoSQL> create table test as select * from dba_objects where 1=0;Table created.SQL> select * from redo_size; VALUE---------- 63392SQL> SQL> insert into test select * from dba_objects;10470 rows created.SQL> select * from redo_size; VALUE---------- 1150988SQL> SQL> insert /*+ append */ into test select * from dba_objects;10470 rows created.SQL> select * from redo_size; VALUE---------- 1152368SQL> select (1152368 -1150988) redo_append,(1150988 -63392) redo from dual;REDO_APPEND REDO----------- ---------- 1380 1087596SQL> drop table test;Table dropped.
我们看到在Noarchivelog模式下,对于常规表的insert append只产生少量redo
b.在归档模式下
SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area 235999908 bytesFixed Size 451236 bytesVariable Size 201326592 bytesDatabase Buffers 33554432 bytesRedo Buffers 667648 bytesDatabase mounted.SQL> alter database archivelog;Database altered.SQL> alter database open;Database altered.SQL> @redoSQL> create table test as select * from dba_objects where 1=0;Table created.SQL> select * from redo_size; VALUE---------- 56288SQL> SQL> insert into test select * from dba_objects;10470 rows created.SQL> select * from redo_size; VALUE---------- 1143948SQL> SQL> insert /*+ append */ into test select * from dba_objects;10470 rows created.SQL> select * from redo_size; VALUE---------- 2227712SQL> select (2227712 -1143948) redo_append,(1143948 -56288) redo from dual;REDO_APPEND REDO----------- ---------- 1083764 1087660SQL> drop table test;Table dropped.
我们看到在归档模式下,对于常规表的insert append产生和insert同样的redo
此时的insert append实际上并不会有性能提高. 但是此时的append是生效了的通过Logmnr分析日志得到以下结果:
SQL> select operation,count(*) 2 from v$logmnr_contents 3 group by operation;OPERATION COUNT(*)-------------------------------- ----------COMMIT 17DIRECT INSERT 10470 INTERNAL 49START 17 1
我们注意到这里是DIRECT INSERT,而且是10470条记录,也就是每条记录都记录了redo.
2.对于Nologging的table的处理
a. 在归档模式下:
SQL> create table test nologging as select * from dba_objects where 1=0;Table created.SQL> select * from redo_size; VALUE---------- 2270284SQL> SQL> insert into test select * from dba_objects;10470 rows created.SQL> select * from redo_size; VALUE---------- 3357644SQL> SQL> insert /*+ append */ into test select * from dba_objects;10470 rows created.SQL> select * from redo_size; VALUE---------- 3359024SQL> select (3359024 -3357644) redo_append,(3357644 - 2270284) redo from dual;REDO_APPEND REDO----------- ---------- 1380 1087360SQL> drop table test;Table dropped.
我们注意到,只有append才能减少redo
b.在非归档模式下:
SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area 235999908 bytesFixed Size 451236 bytesVariable Size 201326592 bytesDatabase Buffers 33554432 bytesRedo Buffers 667648 bytesDatabase mounted.SQL> alter database noarchivelog;Database altered.SQL> alter database open;Database altered.SQL> @redoSQL> create table test nologging as select * from dba_objects where 1=0;Table created.SQL> select * from redo_size; VALUE---------- 56580SQL> SQL> insert into test select * from dba_objects;10470 rows created.SQL> select * from redo_size; VALUE---------- 1144148SQL> SQL> insert /*+ append */ into test select * from dba_objects;10470 rows created.SQL> select * from redo_size; VALUE---------- 1145528SQL> select (1145528 -1144148) redo_append,(1144148 -56580) redo from dual;REDO_APPEND REDO----------- ---------- 1380 1087568SQL>
同样只有append才能减少redo的生成.
这就是通常大家认识的情况.
-The End-