nolog和append的使用

Nologging只在很少情況下生效，通常,DML操作總是要生成redo的，關於Nologging和append,一直存在很多誤解.經過一系列研究,終於發現了Nologging的真相.

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> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

     VALUE

----------

     1150988

SQL> insert /*+ append */ 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> insert /*+ append */ 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(*)

    from v$logmnr_contents

    group by operation;

OPERATION                          COUNT(*)

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

COMMIT                                   17

DIRECT INSERT                         10470  

INTERNAL                                 49

START                                    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

----------

   2270284

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

     VALUE

----------

   3357644

SQL>

SQL> insert /*+ append */ 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 /*+ append */ 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

同樣只有append才能減少redo的生成.