ORACLE資料庫效能優化之表的NOLOGGING

通過設定表的NOLOGGING來產生更少的REDO

ORACLE資料庫會對產生改變的操作記錄REDO，比如DDL語句、DML語句，這些操作首先會放在redo buffer中，然後由LGER程式根據觸發條件寫到聯機日誌檔案，如果資料庫開啟歸檔的話，還要在日誌切換的時候歸檔。在這樣一個完整的鏈條上的每一個環節，都可能會成為效能的瓶頸，所以需要引起DBA和資料庫應用人員的注意。

對於我們來說，最好的調優方式就是不產生REDO或者儘量少的產生REDO。

這裡我們需要搞明白，有些操作不管你怎樣都會產生REDO的，比如普通的DML語句（insert、update、delete等），而有些操作我們是可以考慮不產生redo或者少產生redo的。

比如：1、建立或者重建索引
      2、直接路徑裝載資料，比如SQL*Loader的direct=y方式、insert into APPEND
      3、CTAS方式建立表時

以上列出的不是所有的操作。

下面是一個小的例子，當我們把一個表設定成NOLOGGING模式的時候，通過合適的操作，可以讓oracle產生很少的REDO。

首先確認資料庫處於歸檔模式
system@DB01> conn / as sysdba
Connected.
sys@DB01> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     174
Next log sequence to archive   176
Current log sequence           176

一、如果表採用預設的方式，也就是記錄日誌的方式，不管你是否使用insert into， oracle產生的日誌都會很多
scott@DB01> conn system/oracle
Connected.

system@DB01> create table tj as select * from dba_objects where 1=2;
Table created.

system@DB01> select count(*) from tj;

  COUNT(*)
----------
         0

system@DB01> select table_name,logging from user_tables where table_name='TJ';
TABLE_NAME                     LOG
------------------------------ ---
TJ                             YES

通過autotrace統計redo生成
system@DB01> set autotrace trace stat

1.不採用append 提示
system@DB01> insert into tj select * from dba_objects;
50330 rows created.

Statistics
----------------------------------------------------------
       1795  recursive calls
       4188  db block gets
       6913  consistent gets
          8  physical reads
    5690832  redo size
       1131  bytes sent via SQL*Net to client
       1199  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
      50330  rows processed

system@DB01> rollback;
Rollback complete.

2.採用append 提示的方式
system@DB01> insert --+append

             into  tj select * from dba_objects;
50330 rows created.

Statistics
----------------------------------------------------------
        228  recursive calls
        762  db block gets
       5392  consistent gets
          0  physical reads
    5712972  redo size
       1117  bytes sent via SQL*Net to client
       1211  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      50330  rows processed

system@DB01> rollback;
Rollback complete.

通過以上1和2兩種不同方式的比較，我們發現產生的日誌量是差不多的1.5690832  redo size   2.5712972 redo size

二、修改表為不記錄日誌，這個時候insert into就會體現出他的優勢
system@DB01> alter table tj nologging;
Table altered.

1.不採用append 提示
system@DB01> insert into tj select * from dba_objects;
50330 rows created.

Statistics
----------------------------------------------------------
        211  recursive calls
       2732  db block gets
       6770  consistent gets
          0  physical reads
    5542844  redo size
       1136  bytes sent via SQL*Net to client
       1199  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
      50330  rows processed

system@DB01> rollback;
Rollback complete.

2.採用append 提示的方式
system@DB01> insert --+append

             into  tj select * from dba_objects;
50330 rows created.

Statistics
----------------------------------------------------------
          8  recursive calls
        698  db block gets
       5328  consistent gets
          0  physical reads
       1524  redo size
       1121  bytes sent via SQL*Net to client
       1211  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      50330  rows processed

通過以上1和2兩種不同方式的比較，我們發現產生的日誌量APPEND的方式明顯會少，1.5542844  redo size  2. 1524  redo size。

根據上面的實驗我們可以發現，為了通過減少REDO而提高語句的效能，我們要滿足兩個條件1.表NOLOGGING 2.在語句中使用APPEND提示。

當然我們這裡只是在強調效能，作為一個DBA，在效能和安全之間一定要做一個平衡，當你選擇了NOLOGGING的時候，由於表是不記錄日誌的，那如果資料庫崩潰，這些資料是不能被恢復的。

--&gt>轉載於:http://blog.sina.com.cn/s/blog_69e7b8d701016usr.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29119536/viewspace-1247722/，如需轉載，請註明出處，否則將追究法律責任。