oracle redo日誌產生量測試及比較2_insert

現在我們透過普通的insert，append，以及建立表的方式進行比較
  
    1.資料庫執行在非歸檔模式下
        a.使用logging模式建立表
            SQL> select log_mode from v$database;
 
            LOG_MODE
            ------------
            NOARCHIVELOG  
 
            SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic#  WHERE a.name = 'redo size';
 
            NAME                 VALUE
            --------------- ----------
            redo size             1764
 
            SQL> create table mytest as select * from dba_objects where 1=0;
 
            SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic#  WHERE a.name = 'redo size';
 
            NAME                 VALUE
            --------------- ----------     --建表產生的redo量23908-1764=22144
            redo size            23908 
 
            SQL> insert into mytest select * from dba_objects;
 
            11634 rows created.
            Elapsed: 00:00:00.36
 
            SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic#  WHERE a.name = 'redo size';
 
            NAME                 VALUE
            --------------- ----------    --直接使用insert時產生的redo量1281060-23908=1257152
            redo size          1281060 
 
            SQL> insert /*+ append */ into mytest select * from dba_objects;
 
            11634 rows created.
            Elapsed: 00:00:00.26
 
            SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic#  WHERE a.name = 'redo size';
 
            NAME                 VALUE
            --------------- ----------    --使用append模式時產生的redo量1284740-1281060=3680
            redo size          1284740    --普通insert比使用append insert多產生1257152/3680=341倍redo
 
        b.使用nologging模式建立表
            SQL> create table mytest_nolog nologging as select * from dba_objects where 1=0;
 
            SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic#  WHERE a.name = 'redo size';
 
            NAME                 VALUE
            --------------- ----------     --使用nologging建立空表mytest_nolog時產生的日誌量1305812-1284740=21072
            redo size          1305812
 
            SQL> insert into mytest_nolog select * from dba_objects;
 
            11635 rows created.
            Elapsed: 00:00:00.21
            SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic#  WHERE a.name = 'redo size';                        
 
            NAME                 VALUE
            --------------- ----------    --使用普通insert插入記錄產生的日誌量2562664-1305812=1256852
            redo size          2562664
 
            SQL> insert /* +append */ into mytest_nolog select * from dba_objects;
 
            11635 rows created.
            Elapsed: 00:00:00.18
            SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic#  WHERE a.name = 'redo size';                                   

 
 
            NAME                 VALUE
            --------------- ----------       --使用append模式時產生的redo量 3766404-2562664=1203740
            redo size          3766404    
                                           
        c.redo的比較
            在具有logging屬性物件中，使用append模式時產生的redo量1284740-1281060=3680，
                普通insert比使用append insert多產生/3680=341倍redo
            在具有nologging屬性物件中,使用append insert模式與普通insert模式產生的redo量相差不太大,
                append insert模式為，而普通的insert模式為
          
    2.資料庫執行在歸檔模式下
        a.前期處理
            SQL> drop table mytest purge;
 
            SQL> drop table mytest_nolog purge;
          
            SQL> select log_mode from v$database;
 
            LOG_MODE
            ------------
            ARCHIVELOG
      
        b.建立表物件並進行比較
            SQL> create table mytest as select * from dba_objects where 1=0;    --logging模式建立表物件
 
            SQL> create table mytest_nolog nologging as select * from dba_objects where 1=0;--nologging模式建立表物件
 
            SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic#  WHERE a.name = 'redo size';   
 
            NAME                 VALUE
            --------------- ----------     --檢視當前的redo size 為46844
            redo size            46844
 
            SQL> insert into mytest select * from dba_objects;    --為表mytest使用常規insert插入記錄
 
            11598 rows created.
            Elapsed: 00:00:00.25
 
            SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic#  WHERE a.name = 'redo size';
 
            NAME                 VALUE
            --------------- ----------       --表mytest使用常規insert插入記錄產生的redo size 為1299120-46844=1252276
            redo size          1299120
 
            SQL> insert into mytest_nolog select * from dba_objects;   --為表mytest_nolog使用常規insert插入記錄
 
            11598 rows created.
            Elapsed: 00:00:00.28
 
            SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic#  WHERE a.name = 'redo size';
 
            NAME                 VALUE
            --------------- ----------    --表mytest_nolog使用常規insert插入記錄產生的redo size 為2552880-1299120=1253760
            redo size          2552880
 
            SQL> insert /* +append */ into mytest select * from dba_objects;--表mytest使用insert append方式
 
            11598 rows created.
            Elapsed: 00:00:00.20
 
            SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic#  WHERE a.name = 'redo size';
 
            NAME                 VALUE
            --------------- ----------    --表mytest使用insert append插入記錄產生的redo size 為3750852-2552880=1197972
            redo size          3750852
 
            SQL> insert /* +append */ into mytest_nolog select * from dba_objects;--表mytest_nolog使用insert append方式
 
            11598 rows created.
            Elapsed: 00:00:00.18
 
            SQL> SELECT a.name,b.value FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic#  WHERE a.name = 'redo size';
 
            NAME                 VALUE
            ---------------            ----------    --表mytest_nolog使用insert append插入記錄產生的redo size 為4948764-3750852=1197912
            redo size          4948764
      
       
  
    3.小結
        對於表物件插入記錄時，使用普通insert 與使用append方式比較
            在非歸檔模式下，表物件在使用nologging模式時，兩者產生的日誌量相差不大，而使用logging模式時，常規insert的日誌量遠大於append方式。
            在歸檔模式下，表物件使用logging模式，兩者產生的日誌量相差不大。而表物件使用nologging模式時，則使用append將使得效能有所提高。
        在非歸檔模式下的inesrt append操作將是效能最高的。
        比如我們在做dataguard、流複製、第三方工具同步時，針對資料庫開啟了force logging的方式，各種特徵的表，在插入時，日誌量相差是不大的
        
          
    4.direct insert append使用時的注意事項    
        a.當使用insert into ... values語句時，不能夠使用append方式
        b.append方式為批次插入的記錄，因此新插入的記錄被儲存在hwm 之上，對於hwm之下空閒塊將不會被使用。
        c.在append方式插入記錄後，要執行commit，才能對錶進行查詢。否則會出現錯誤：
            ORA-12838: cannot read/modify an object after modifying it in parallel
        d.在歸檔模式下，表物件具有nologging屬性，且以append方式批次新增記錄，才會顯著減少redo數量。
        e.在非歸檔模式下，表物件即便具有logging屬性，也可減少redo數量。
        f.對於表上具有索引的表物件，如果新增的記錄數量為整個表的很少一部分，則直接以append方式批次新增記錄，如果原表記錄很少，
            實時性要求不是很高，而新增記錄很多，可以先刪除索引，在使用append方式追加記錄，最後再建立索引。