Oracle Nologging and Append 經典總結__Oracle

對於logging的理解總是以為表的日誌設定為NO它就不會去產生日誌了，其實不是的下面是對於logging的一些解釋和實驗。

Logging介紹

可以採用nologging模式執行以下操作：

1．索引的建立和ALTER（重建）。
2．表的批量INSERT（通過/*+append */提示使用“直接路徑插入“。或採用SQL*Loader直接路徑載入）。表資料產生少量redo，但是所有索引修改會產生大量redo（儘管表不組建記錄檔，但這個表上的索引卻會產生redo。）。
3．Lob操作（對大對象的更新不必組建記錄檔）。
4．通過create table as select建立表。
5．各種alter table操作，如move和split。

在一個archivelog模式的資料庫上，如果nologging使用得當，可以加快許多操作的速度，因為它能顯著減少產生的重做日誌量。假設你有一個表，需要從一個資料表空間移到另一個資料表空間，原先需要N小時才能完成的操作可能只需要N/2小時。要想適當地使用這個特性，需要DBA的參與，或者必須與負責Database Backup和恢複（或任何備用資料庫）的人溝通。如果這個人不知道使用了這個特性，一旦出現介質失敗，就可能遺失資料，或者備用資料庫的完整性可能遭到破壞，對此一定要三思。

對象Logging狀態查詢

通過此查詢SQL語句查詢表的logging狀態

SELECT T.TABLE_NAME, T.LOGGING
  FROM USER_TABLES T
WHERE T.TABLE_NAME LIKE '%TEST_FUTUFARES%';

Create和Insert的Logging測試
Create table …. as select ….及 insert into …..select ….測試

改變logging狀態值的方法：

ALTER TABLE table_name NOLOGGING/logging;

下面的例子是來源資料在1萬條左右，Create table …as select …測試發現相差2秒鐘左右,特別是在大資料量帶有nologging的Create速度上確實會快很多。

下面是INSERT語句的測試資料量在2百萬左右，TEST_FUTUFARES2的logging不管是在YES還是NO的狀態下其實插入都是一樣的速度

通過以上測試其實表在Nologging與Logging狀態時插入2百萬的資料耗時差不多的，也就是說DML不是說不記日誌而只是在特定的情況下是不記日誌的，比如用SQL*Loader直接裝載及INSERT /*+Append*/選項直接路徑裝載，也就是說不管是否是NOLOGGING狀態DML操作正常情況下肯定會產生日誌。

Nologging模式下資料庫操作只有如下幾種情況下不產成redo記錄：
１、用sql*load的direct load方式時，不採用redo記錄
已測試

２、用insert的direct方式，即在append方式insert
已測試

３、create table ….as select….
已測試

４、create index
create index  TEST_FUTUFARES2_log  on TEST_FUTUFARES2 (FARE_KIND,FUTUFARE_TYPE) nologging;

建立索引要想產生極少的REDO必須要按上面的那種方式建立索引，按照上面的那種方法去建立索引不管表的日誌是處在nologging還是logging狀態下都是一樣都會產生很少的REDO日誌，否則還是會產生大量的REDO日誌。
５、alter table ... move partition
６、alter table ... split partition
７、alter index ... split partition
８、alter index ... rebuild
９、alter index ... rebuild partition
１０、INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line

Append介紹

非歸檔模式情況下：

1．查看當前會話所有產生的REDO總量

表處於nologging狀態:

SQL> set timing on;
SQL>INSERT  INTO TEST_FUTUFARES2 SELECT * FROM TEST_FUTUFARES;
2090220 rows inserted
Executed in 36.25 seconds

SQL> SELECT A.NAME, B.VALUE FROM V$MYSTAT B, V$STATNAME A WHERE A.STATISTIC# = B.STATISTIC# AND A.NAME LIKE '%redo size%';
NAME                                                   VALUE
--------------------------------------------------------------------------
Redo size                                             113495212

SQL>INSERT /*+append*/ INTO TEST_FUTUFARES2 SELECT * FROM TEST_FUTUFARES;
2090220 rows inserted
Executed in 9.062 seconds

SQL> SELECT A.NAME, B.VALUE FROM V$MYSTAT B, V$STATNAME A WHERE A.STATISTIC# = B.STATISTIC# AND A.NAME LIKE '%redo size%';
NAME                                                      VALUE
--------------------------------------------------------------------------
Redo size                                                113560764

SQL>select 113560764-113495212 from dual;
113560764-113495212
-------------------
              65552

表處於logging狀態:
   對於此測試得出的結果其實跟上面的nologging得出的測試結果幾乎是一模一樣的，就不貼出來了。

歸檔模式情況下：

表處於logging狀態:

SQL> INSERT  INTO TEST_FUTUFARES2 SELECT * FROM TEST_FUTUFARES;
2090220 rows inserted
Executed in 44.031 seconds

SQL> SELECT A.NAME, B.VALUE FROM V$MYSTAT B, V$STATNAME A WHERE A.STATISTIC# = B.STATISTIC# AND A.NAME LIKE '%redo size%';
NAME                                                     VALUE
--------------------------------------------------------------------------
Redo size                                              113460280

SQL>INSERT /*+append*/  INTO TEST_FUTUFARES2 SELECT * FROM TEST_FUTUFARES;
2090220 rows inserted
Executed in 24.297 seconds

SQL> SELECT A.NAME, B.VALUE FROM V$MYSTAT B, V$STATNAME A WHERE A.STATISTIC# = B.STATISTIC# AND A.NAME LIKE '%redo size%';
NAME                                                      VALUE
--------------------------------------------------------------------------
Redo size                                               223253980

SQL> select 223253980-113460280 from dual;
223253980-113460280
-------------------
          109793700

表處於nologging狀態:

SQL> INSERT /*+append*/  INTO TEST_FUTUFARES2 SELECT * FROM TEST_FUTUFARES;
2090220 rows inserted
Executed in 6.391 seconds

SQL> SELECT A.NAME, B.VALUE FROM V$MYSTAT B, V$STATNAME A WHERE A.STATISTIC# = B.STATISTIC# AND A.NAME LIKE '%redo size%';
NAME                                                      VALUE
--------------------------------------------------------------------------
redo size                                               223576712

SQL> select 223576712-223253980 from dual;
223576712-223253980
-------------------
             322732

2．查看全域資料庫redo產生量，可以通過v$sysstat視圖看到

SQL> select name,value  from v$sysstat where name='redo size';
NAME                                                    VALUE
--------------------------------------------------------------------------
Redo size                                               122314360

總結：
關於Nologging與append測試的一些總結，通過上面的SQL語句查看可以得出在大量資料插入過程的語句中加入/*+append*/的這個SQL語句產生的REDO日誌明顯示是會少同時時間節約了很多，當然這樣可能會影響備份因此nologging載入資料後要做一個資料庫的全備。

insert append並不是在任何時候都可以節省時間的以下是測試的一些總結：

第一種情況：

database為archivelog狀態，這種情況下，就算你用insert append也是不一定提高插入效率的。但是如果你將目標表設定為nologging，然後再使用insert append就會很快。
第二種情況：

database為noarchivelog狀態，如果在這種情況下直接採用insert方法而不加入append向表中插入資料，佔用的redo空間的大小與archivelog狀態下佔用的大小是相當的，不論表是否為nologging。但是如果採用insert append方法的話，通過redo的佔用值大家可以發現，不論表是否為nologging，所佔用的redo的大小都是很小的。也就說明：在資料庫為noarchivelog的狀態下，採用insert append方法，如果表不是nologging，系統也會自動將錶轉換為nologging（即在執行insert append之前，先執行一個alter table arch1 nologging）。
第三種情況：

如果表上有索引，則append方式大量新增記錄，不會減少索引上產生的redo數量，索引上的redo數量可能比表的redo數量還要大。用insert append可以實現直接路徑載入速度是快很多，但有一點需要注意: insert append時在表上加”6”類型的鎖，會阻塞表上的所有DML語句，因此在有業務啟動並執行情況下要謹慎使用。若同時執行多個insert append對同一個表並行載入資料，並不一定會提高速度，因為每一時刻只能有一個進程在載入(排它鎖造成)à此觀點是在網上查到的對於此疑問對於鎖的問題此兄弟可能不是太理解，對於ORACLE資料庫來說只要DML特別是insert操作他肯定會將表給鎖住而且是獨佔鎖除非進行commit,rollback，及其它的DDL操作來釋放否則鎖會一直獨佔導致其它的DML操作無法進行正常的操作，而跟所謂的APPEND無關。

以下是關於表模式(LOGGING/NOLOGGING)，插入模式(APPEND/NOAPPEND)，資料庫運行模式(歸檔/非歸檔)，REDO日誌產生的關係

資料庫模式	表模式	插入模式	REDO產生
ARCHIVELOG	LOGGING	APPEND	有REDO
NO APPEND	有REDO
NOLOGGING	APPEND	無REDO
NO APPEND	有REDO
NOARCHIVELOG	LOGGING	APPEND	無REDO
NO APPEND	有REDO
NOLOGGING	APPEND	無REDO
NO APPEND	有REDO