Nologging到底何时才能生效?

 查看数据库版本和系统：
SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

归档方式查看：

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     40
Current log sequence           42

 

（1）使用STATICS收集信息的测试步骤和结果：

SQL> set autotrace on statistics

SQL> create table test as select * from all_objects where 1=2;

Table created.

 

SQL> insert into test  select * from all_objects;

49562 rows created.

Statistics
----------------------------------------------------------
       7267  recursive calls
       4069  db block gets
      37726  consistent gets
          0  physical reads
    5582676  redo size
        678  bytes sent via SQL*Net to client
        579  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         21  sorts (memory)
          0  sorts (disk)
      49562  rows processed

 

--使用HINT附加

SQL>  insert /*+ append */ into test  select * from all_objects;

49562 rows created.

Statistics
----------------------------------------------------------
       5491  recursive calls
        771  db block gets
      35087  consistent gets
          0  physical reads
      11384  redo size
        664  bytes sent via SQL*Net to client
        594  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
      49562  rows processed

修改表LOG模式

SQL> alter table test nologging;

Table altered.

SQL> insert /*+ append */ into test  select * from all_objects;

49562 rows created.

Statistics
----------------------------------------------------------
       5661  recursive calls
        746  db block gets
      35245  consistent gets
          0  physical reads
       9992  redo size
        664  bytes sent via SQL*Net to client
        593  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
      49562  rows processed

 

加索引的情况：

SQL> create index ind_test on test (object_id);

Index created.

 

SQL> insert /*+ append */ into test  select * from all_objects;

49563 rows created.

 

Statistics
----------------------------------------------------------
       5939  recursive calls
       8312  db block gets
      36157  consistent gets
        712  physical reads
    7715620  redo size
        665  bytes sent via SQL*Net to client
        593  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          9  sorts (memory)
          1  sorts (disk)
      49563  rows processed

 

 

（2）使用统计视图显示回滚段大小

这个测试结果可疑的很，注意不要使用auto trace来查看redo size，这个值是有偏差的.

看看另外一个测试结果:首先建立这样一个视图给所有用户用:

SQL>

create or replace view redo_size
as
select value    from v$mystat, v$statname
 where v$mystat.statistic# = v$statname.statistic#
      and v$statname.name = 'redo size';

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

View created.

 

 

SQL> create table test2 as select * from dba_objects where 1=2;
 
Table created

 
SQL> select * from redo_size;
 
     VALUE
----------
     36284

 
SQL> insert into test2 select * from dba_objects;
 
50009 rows inserted

 
SQL> select * from redo_size;
 
     VALUE
----------
   5673156

 
SQL>  insert /*+ append */ into test2 select * from dba_objects;
 
50009 rows inserted

 
SQL> select * from redo_size;
 
     VALUE
----------
   5682932

 

 

SQL> select (5682932 -5673156) redo2,(5673156 -36284) redo1 from dual;
 
     REDO2      REDO1
---------- ----------
      9776    5636872

 

 

SQL> alter table test2 nologging;
 
Table altered

 
SQL> select * from redo_size;
 
     VALUE
----------
   5685500

 
SQL> insert /*+ append */ into test2 select * from dba_objects;
 
50009 rows inserted

 
SQL> select * from redo_size;
 
     VALUE
----------
   5698760
 

SQL> select (4491892 -3408128) redo2,(3408128 -2320828) redo1 ,(5698760 -5685500) redo3 from dual;
 
     REDO2      REDO1      REDO3
---------- ---------- ----------
   1083764    1087300      13260

EYGLE大师的结论和实验：http://www.eygle.com/faq/Nologging&append.htm

 

 

总结后就是一句话：

在归档模式下，对于常规表的insert append产生和insert同样的redo，此时的insert append实际上并不会有性能提高.

只有在表Nologging的情况下并且append才能减少redo，提升性能。

在非归档模式下，同样只有在表Nologging的情况下的append才能减少redo的生成.