nologging和insert /*+append*/

 

alter table test nologging
insert /*+ append */ into test select

 

 

ask tom上有过一篇文章，是说Oracle实际上需要满足表是nologging和insert /*+append*/两个条件才真正实现nologging的

 

 

在insert数据量很大的时候（千万级），减少redo的产生对性能应该有很大的提高。
这是一个使用append和nologging对redo产生情况的实验。

结论：
-------------------------------
一、非归档模式下：
没有优化前    （1281372  redo size）
1、单一的使用nologging参数，对redo的产生没有什么影响。  （1214836  redo size）
2、单一的使用append提示，redo的减少很显著              （43872  redo size）
3、nologging+append，更显著                             （1108  redo size）

二、归档模式下：
没有优化前：           
1、单独使用nologging参数，（1231904  redo size）
2、单独使用append提示，  （1245804  redo size）
3、nologging + append，     （3748  redo size）

a、使用nologging参数并不代表在dml操作中，oracle不产生redo，只是对于指定表的更新数据不产生redo，但是oracle还是要记录这些操作，所以无论怎么优化，dml操作肯定要产生redo，但是使用这些参数对redo size的影响还是非常可观的。
b、单独使用nologging参数，对redo size没有多少影响，只有和append配合时，才能产生效果。
c、单独使用append提示，对redo的产生影响很大，这是我到现在都不明白的道理，按说append是绕过freelists，直接去寻找新块，能减少对freelists的争用，为什么会少这么多redo呢？
d、归档模式和非归档模式下，参数影响不一样，尤其是单独使用append参数时，看来oracle对归档模式下出于安全考虑还是要多一些。
文章出处：

 

 

1.Nologging的设置跟数据库的运行模式有关

a.数据库运行在非归档模式下:

SQL> archive log list;

Database log mode              No Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/oradata/hsjf/archive
Oldest online log sequence     155
Current log sequence           157

SQL> @redo

SQL> create table test as select * from dba_objects where 1=0;

Table created.

SQL> select * from redo_size;

     VALUE
----------
     63392

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

     VALUE
----------
   1150988

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

     VALUE
----------
   1152368

SQL> select (1152368 -1150988) redo_append,(1150988 -63392) redo from dual;

REDO_APPEND       REDO
----------- ----------
       1380    1087596

SQL> drop table test;

Table dropped. 

我们看到在Noarchivelog模式下,对于常规表的insert append只产生少量redo

b.在归档模式下

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  235999908 bytes

Fixed Size                   451236 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> @redo

SQL> create table test as select * from dba_objects where 1=0;

Table created.

SQL> select * from redo_size;

     VALUE
----------
     56288

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

     VALUE
----------
   1143948

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

     VALUE
----------
   2227712

SQL> select (2227712 -1143948) redo_append,(1143948 -56288) redo from dual;

REDO_APPEND       REDO
----------- ----------
    1083764    1087660

SQL> 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                                   17
DIRECT INSERT                         10470 
INTERNAL                                 49
START                                    17

我们注意到这里是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
----------
   2270284

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

     VALUE
----------
   3357644

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

     VALUE
----------
   3359024

SQL> select (3359024 -3357644) redo_append,(3357644 - 2270284) redo from dual;

REDO_APPEND       REDO
----------- ----------
       1380    1087360

SQL> drop table test;

Table dropped.  

我们注意到,只有append才能减少redo

b.在非归档模式下:

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  235999908 bytes
Fixed Size                   451236 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> @redo

SQL> create table test nologging as select * from dba_objects where 1=0;

Table created.

SQL> select * from redo_size;

     VALUE
----------
     56580

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

     VALUE
----------
   1144148

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

     VALUE
----------
   1145528

SQL> select (1145528 -1144148) redo_append,(1144148 -56580) redo from dual;

REDO_APPEND       REDO
----------- ----------
       1380    1087568

SQL>