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對歸檔模式下出于安全考慮還是要多一些。
文章出處：http://www.diybl.com/course/7_databases/oracle/Oracleshl/2008810/135707.html

 

 

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>