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

       資料庫日誌有幾種模式，強制日誌、非強制日誌模式、物件級別等！通常在資料庫模式設定強制後，餘下的不管如何都會強制日誌！現在我們就oracle資料庫日誌模式產生的日誌量進行比較測試，在各種模式下日誌產生的情況！
日誌記錄模式的轉換
       a.資料庫從非強制日子模式切換到強制日誌模式
           SQL>alter database force logging;    
       b.資料庫從強制日誌模式切換到非強制日誌模式
           SQL>alter database noforce logging;
       c.表空間級別從強制日誌模式切換到非強制日誌模式
           SQL>alter tablespace tablespacename noforce logging;
       d.表空間級別從非強制日誌模式切換到強制日誌模式
           SQL>alter tablespace tablespacename force logging;
       e.物件級別日誌記錄模式
           SQL>alter tablet mytest nologging;    --不記錄日誌模式
           SQL>alter tablet mytest logging;      --採用日誌記錄模式
 
一、表段，索引段上使用一般DDL，DML時，LOGGING與NOLOGGING情況  
    1.檢視資料庫的歸檔模式
        有關設定日誌歸檔模式的問題，請參考：
            Oracle 聯機重做日誌檔案(ONLINE LOG FILE)
            Oracle 歸檔日誌
 
        SQL> select log_mode,force_logging from v$database;
 
        LOG_MODE     FOR
        ------------ ---
        ARCHIVELOG   NO
      
        SQL> archive log list;
        Database log mode              Archive Mode
        Automatic archival             Enabled
        Archive destination            /u01/mytest/arch
        Oldest online log sequence     1024
        Next log sequence to archive   1025
        Current log sequence           1025 
      
        SQL> select tablespace_name,logging,force_logging from dba_tablespaces;
 
        TABLESPACE_NAME                LOGGING   FOR
        ------------------------------ --------- ---
        SYSTEM                         LOGGING   NO
        UNDOTBS1                       LOGGING   NO
        SYSAUX                         LOGGING   NO
        TEMP                           NOLOGGING NO
        USERS                          LOGGING   NO
        PERFSTAT                       LOGGING   NO
      

      
    2.使用如下語句進行查詢
      
         SQL >SELECT a.name,b.value  FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = 'redo size';
 

 
    3.在歸檔模式下比較表段上的NOLOGGING與LOGGING
        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             1644
 
        SQL> CREATE TABLE mytest_nolog NOLOGGING AS SELECT * FROM dba_objects;  --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            70064         
 
        SQL> select 1644 last,70064 as cur,(70064-1644) diff from dual;--使用nologging模式建表產生的redo size為
 
              LAST        CUR       DIFF
        ---------- ---------- ----------
              1644      70064      68420
 
        SQL> CREATE TABLE mytest LOGGING AS SELECT * FROM dba_objects;    --使用logging模式來建立表
 
        SQL> SELECT a.name,b.value  FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = 'redo size';;   --檢視當前的redo size
 
        NAME                 VALUE
        --------------- ----------            
        redo size          1344112              

        SQL> select 70064 last,1344112 as cur,(1344112-70064) diff from dual; 
 
              LAST        CUR       DIFF
        ---------- ---------- ----------
            70064    1344112      1274048          ----檢視logging模式產生的redo size 為-68420=1274048,比nologging日誌模,有19倍多！
             
 
        SQL> select table_name,logging from user_tables where table_name like 'MYTEST%';--檢視建立表的日誌記錄模式
 
        TABLE_NAME                     LOG
        ------------------------------ ---
        MYTEST                        YES
        MYTEST_NOLOG                   NO
 
    4.基於索引來比較redo size(同樣是在歸檔模式下)
        SQL> SELECT a.name,b.value  FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = 'redo size';;    --檢視當前的redo_size
 
        NAME                 VALUE
        --------------- ----------
        redo size             1140
 
        SQL> create index idx_mytest on mytest(object_id);  --基於表mytest來建立索引
 
        SQL> SELECT a.name,b.value  FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = 'redo size';;      --檢視當前的redo_size
 
        NAME                 VALUE
        --------------- ----------                --基於loggiing模式，建立索引產生的redo size 為221600-1140=220460
        redo size           221600
 
        SQL> alter index idx_mytest rebuild;    --重建索引
            
        SQL> select 221600 last,448132 cur,448132-221600 diff from dual;
      
              LAST        CUR       DIFF
        ---------- ---------- ----------
            221600     448132     226532   --重建索引產生的日誌比直接建還要多，主要是重建過程還有一個對舊索引的刪除
 
            
        SQL> alter index idx_mytest rebuild nologging; --使用nologging重建索引。
                                                --也可以在建立索引的時候直接使用nologging關鍵字
                                                --如：create index idx_mytest_nolog mytest_nolog(object_id) 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';;    --檢視當前的redo size
 
        NAME                 VALUE
        --------------- ----------              --基於nologging日誌模式重建索引產生的redo size為469160-448132=21028
        redo size           469160
 
    5.非歸檔模式下的LOGGING與NOLOGGING
 
        SQL> drop table mytest purge;
 
        SQL> drop table mytest_nolog purge;
      
        SQL> select log_mode,force_logging from v$database;  --切換日誌到非歸檔模式後，下面是查詢的結果
 
        LOG_MODE     FOR
        ------------ ---
        NOARCHIVELOG NO
      
        SQL> SELECT a.name,b.value  FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = 'redo size';;    --檢視當前的redo size
 
        NAME                 VALUE
        --------------- ----------
        redo size             1688
        SQL> create table mytest as select * from dba_objects;   --建立表物件，使用logging日誌記錄模式
 
        SQL> SELECT a.name,b.value  FROM v$statname a JOIN v$mystat b ON a.statistic# = b.statistic# WHERE a.name = 'redo size';;   --檢視當前的redo size
 
        NAME                 VALUE
        --------------- ----------            --使用logging日誌記錄模式，建立表物件之後產生的redo size 為-1688 =68548
        redo size            70236
 
        SQL> create table mytest_nolog nologging as select * from dba_objects; --建立表物件，使用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';;   --檢視當前的redo size
 
        NAME                 VALUE
        --------------- ----------             --使用nologging日誌記錄模式建立表物件之後產生的redo size為135464-70236=65228
 
        redo size           135464
        
    6.小結：
        使用logging與nologging來建立物件或執行DML時
            對於非歸檔模式下，其產生的日誌資訊(redo size)相差的並不大
            對於歸檔模式下，logging模式產生的日誌將遠遠大於使用nologging模式產生的日誌量