說明：紅字的redo size 即為產生日誌的大小

測試環境：rhel6  oracle 11.2.0.4

本次測試在非歸檔和歸檔環境下，利用append和nologging方式插入資料 來減少redo size的大小

(注：本次測試僅在測試環境上測試資料的效能問題，生產環境切勿測試)

一、   非歸檔模式

 

 

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination           /u01/app/oracle/product/12.2.0.1/db_1/dbs/arch

Oldest online log sequence     3

Current log sequence           5

 

 

1、object_wangx的資料正常插入一張表object_wangx_normal中

SQL> create table object_wangx_normal as select * from object_wangx where 1=2;

 

Table created.

 

SQL> set autotrace traceonly statistics;

SQL> insert into object_wangx_normal  select * from object_wangx;

 

435806 rows created.

 

 

Statistics

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

       115  recursive calls

     47865  db block gets

     25491  consistent gets

         0  physical reads

   69165128 redo size

       863  bytes sent via SQL*Net toclient

       979  bytes received via SQL*Netfrom client

         3  SQL*Net roundtrips to/fromclient

         1  sorts (memory)

         0  sorts (disk)

    435806  rows processed

 

SQL> commit;

SQL> SET AUTOTRACE OFF;

 

 

 

2、object_wangx的資料append插入一張表object_wangx_append中

SQL> create table object_wangx_append as select * from object_wangx where 1=2;

 

Table created.

 

SQL> set autotrace traceonly statistics;

SQL> insert /*+append*/ into object_wangx_append  select * from object_wangx;

 

435806 rows created.

 

 

Statistics

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

       199  recursive calls

      9538  db block gets

      8698  consistent gets

         0  physical reads

     111196  redo size

       850  bytes sent via SQL*Net toclient

       991  bytes received via SQL*Netfrom client

         3  SQL*Net roundtrips to/fromclient

         1  sorts (memory)

         0  sorts (disk)

    435806  rows processed

 

SQL> commit;

 

Commit complete.

 

SQL> SET AUTOTRACE OFF;

 

3、object_wangx的資料append插入一張nologgng表object_wangx_append_nologging中

SQL>create table object_wangx_append_nologging as select * from object_wangx where1=2;

 

Tablecreated.

 

SQL>alter table object_wangx_append_nologging nologging;

 

Tablealtered.

 

SQL>set autotrace traceonly statistics;

SQL>insert /*+append*/ into object_wangx_append_nologging  select * from object_wangx;

 

435806rows created.

 

 

Statistics

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

        206 recursive calls

       9536 db block gets

       8705 consistent gets

          2 physical reads

     111020 redo size

        850 bytes sent via SQL*Net to client

       1001 bytes received via SQL*Net from client

          3 SQL*Net roundtrips to/from client

          3 sorts (memory)

          0 sorts (disk)

     435806 rows processed

 

SQL>commit;

 

Commitcomplete.

 

SQL>SET AUTOTRACE OFF;

 

 

 

4、object_wangx的資料append插入一張nologgng表object_wangx_append_nologging_table中，並在表名後加上 nologging關鍵字

SQL>create table object_wangx_append_nologging_table as select * from object_wangx where 1=2;

 

Tablecreated.

 

SQL> alter table OBJECT_WANGX_APPEND_NOLOGGING_TABLE nologging;

 

Tablealtered.

 

SQL>set autotrace traceonly statistics;

 

SQL>  insert /*+append*/ into object_wangx_append_nologging_table nologging select * from object_wangx;

 

435806rows created.

 

 

Statistics

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

        169 recursive calls

       9442 db block gets

       8645 consistent gets

          0 physical reads

     104244  redo size

        850 bytes sent via SQL*Net to client

       1017 bytes received via SQL*Net from client

          3 SQL*Net roundtrips to/from client

          3 sorts (memory)

          0 sorts (disk)

     435806 rows processed

 

SQL>commit;  

 

Commitcomplete.

 

SQL>  SET AUTOTRACE OFF;

 

 

 

二、歸檔模式

 

SQL>archive log list;

Database log mode             Archive Mode

Automaticarchival             Enabled

Archivedestination           USE_DB_RECOVERY_FILE_DEST

Oldestonline log sequence     3

Next logsequence to archive   5

Currentlog sequence           5

 

 

 

SQL>select count(1) from object_wangx;

 

  COUNT(1)

----------

    782880

 

 

1、object_wangx的資料正常插入一張表object_wangx__arh_normal中

SQL> create table object_wangx_arh_normal as select * from object_wangx where 1=2;

 

Table created.

 

SQL> set autotrace traceonly statistics;

SQL> insert into object_wangx_arh_normal select * from object_wangx;

 

782880 rows created.

 

 

Statistics

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

       101  recursive calls

     57438  db block gets

     30722  consistent gets

         0  physical reads

   83877640 redo size

       838  bytes sent via SQL*Net toclient

       820  bytes received via SQL*Netfrom client

         3  SQL*Net roundtrips to/fromclient

         1  sorts (memory)

         0  sorts (disk)

    782880  rows processed

SQL> commit;

 

Commit complete.

 

SQL> SET AUTOTRACE OFF;

 

 

2、object_wangx的資料append插入一張表object_wangx_arh_append中

SQL> create table object_wangx_arh_append as select * from object_wangx where 1=2;

 

Table created.

 

SQL> set autotrace traceonly statistics;

SQL> insert /*+append*/ into object_wangx_arh_append  select * from object_wangx;

 

782880 rows created.

 

 

Statistics

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

       180  recursive calls

     11181  db block gets

     10374  consistent gets

         0  physical reads

   84206664 redo size

       825  bytes sent via SQL*Net toclient

       835  bytes received via SQL*Netfrom client

         3  SQL*Net roundtrips to/fromclient

         1  sorts (memory)

         0  sorts (disk)

    782880  rows processed

SQL> commit;

 

Commit complete.

 

SQL> SET AUTOTRACE OFF;

3、object_wangx的資料append插入一張nologgng表object_wangx_arg_append_nologging中

 

SQL>create table object_wangx_arh_append_n as select * from object_wangx where 1=2;

 

Tablecreated.

 

SQL>alter table object_wangx_arh_append_n nologging;

 

Tablealtered.

 

SQL>  set autotrace traceonly statistics;

SQL>insert /*+append*/ into object_wangx_arh_append_n  select * from object_wangx;

 

782880rows created.

 

 

Statistics

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

        202 recursive calls

      11181 db block gets

      10392 consistent gets

          0 physical reads

     109612  redo size

        825 bytes sent via SQL*Net to client

        836 bytes received via SQL*Net from client

          3 SQL*Net roundtrips to/from client

          5 sorts (memory)

          0 sorts (disk)

     782880 rows processed

SQL>commit;

 

Commitcomplete.

 

SQL>SET AUTOTRACE OFF;

 

 

4、object_wangx的資料append插入一張nologgng表object_wangx_arh_append_nologging_table中，並在表名後加上 nologging關鍵字

SQL>create table object_wangx_arh_append_n_t as select * from object_wangx where1=2;

 

Tablecreated.

 

SQL>alter table object_wangx_arh_append_n_t nologging;

 

Tablealtered.

 

SQL>set autotrace traceonly statistics;

SQL>  insert /*+append*/ into object_wangx_arh_append_n_t nologging select * from object_wangx;

 

782880rows created.

 

 

Statistics

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

        226 recursive calls

      11181 db block gets

      10392 consistent gets

          0 physical reads

     109568  redo size

        825 bytes sent via SQL*Net to client

        848 bytes received via SQL*Net from client

          3 SQL*Net roundtrips to/from client

          5 sorts (memory)

          0 sorts (disk)

     782880 rows processed

SQL>  commit; 

 

Commitcomplete.

 

SQL>SET AUTOTRACE OFF;

 

 

 

 

結論：

1、在非歸檔時，insert單獨設定append 即可減少redo log日誌，無需設定表為nologging模式

 

2、在歸檔時，insert單獨設定append不會減少redo log日誌，在設定append的基礎上 還需要配合設定表為nologging模式才可大量減少redolog日誌