表段、索引段上的LOGGING與NOLOGGING

--====================================

-- 表段、索引段上的LOGGING與NOLOGGING

--====================================

在有些情況下，對於表段和索引段可以採用記錄日誌的模式，也可以使用不記錄日誌的模式。如在對錶段、索引段使用資料泵匯入時，可以

使用NOLOGGING模式，而使用DATA GUARD或對可用性較高的情境中需要記錄日誌，甚至使用強制記錄日誌。本文介紹了在表段，索引段使用

LOGGING與NOLOGGING時產生redo的大小以及DIRECT INSERT APPEND 的使用方法。

NOLOGGING跟資料庫的運行模式有關，i和i的預設安裝都是非歸檔模式，並且自動歸檔預設是禁用。在安裝g、g時，可以選擇是否歸

檔。NOLOGGIING將記錄少量日誌資訊到記錄檔。如果資料庫層級或資料表空間層級使用了FORCE LOGGING強制日誌記錄模式，則該選項無效。

一、表段，索引段上使用一般DDL，DML時，LOGGING與NOLOGGING情況  

1.查看資料庫的歸檔模式

有關設定日誌歸檔模式的問題，請參考：

Oracle 聯機重做記錄檔(ONLINE LOG FILE)

             Oracle 歸檔日誌

sys@ORCL> select log_mode,force_logging from v$database;

LOG_MODE     FOR

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

ARCHIVELOG   NO

sys@ORCL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/bk/arch

Oldest online log sequence     50

Next log sequence to archive   51

Current log sequence           51  

sys@ORCL> 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

scott@ORCL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

2.建立一個查看redo size 的視圖redo_size

CREATE OR REPLACE FORCE VIEW "SYS"."REDO_SIZE"

AS

 SELECT a.name,b.value

 FROM v$statname a

 JOIN v$mystat b

ON a.statistic# = b.statistic#

 WHERE a.name = 'redo size';

sys@ORCL> create public synonym redo_size for redo_size;

sys@ORCL> grant select on redo_size to scott;

3.在歸檔模式下比較表段上的NOLOGGING與LOGGING

scott@ORCL> select * from redo_size;

NAME                 VALUE

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

redo size             1644

scott@ORCL> CREATE TABLE tb_obj_nolog NOLOGGING AS SELECT * FROM dba_objects;  --nologging模式建立表

scott@ORCL> select 1644 last,70064 as cur,(70064-1644) diff from dual;--使用nologging模式建表產生的redo size為

 LAST        CUR       DIFF

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

 1644      70064      68420

scott@ORCL> CREATE TABLE tb_obj_log LOGGING AS SELECT * FROM dba_objects;    --使用logging模式來建立表

sscott@ORCL> select * from redo_size;   --查看當前的redo size

NAME                 VALUE

--------------- ----------             --查看logging模式產生的redo size 為-68420=1274048,比nologging日誌模

redo size          1344112               --式多出了19倍多

scott@ORCL> select table_name,logging from user_tables where table_name like 'TB_OBJ%';--查看建立表的日誌記錄模式

TABLE_NAME                     LOG

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

TB_OBJ_LOG                     YES

TB_OBJ_NOLOG                   NO

4.基於索引來比較redo size(同樣是在歸檔模式下)

scott@ORCL> select * from redo_size;    --查看當前的redo_size

NAME                 VALUE

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

redo size             1140

scott@ORCL> create index idx_tb_obj_log on tb_obj_log(object_id);  --基於表tb_obj_log來建立索引

scott@ORCL> select * from redo_size;      --查看當前的redo_size

NAME                 VALUE

--------------- ----------                --基於loggiing模式，建立索引產生的redo size 為221600-1140=220460

redo size           221600

scott@ORCL> alter index idx_tb_obj_log rebuild;    --重建索引

scott@ORCL> select 221600 last,448132 cur,448132-221600 diff from dual;

 LAST        CUR       DIFF

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

221600     448132     226532   --重建索引後產生的redo size 為226532，比直接建立時產生的redo size還要多

scott@ORCL> alter index idx_tb_obj_log rebuild nologging; --使用nologging重建索引。

--也可以在建立索引的時候直接使用nologging關鍵字

   --如：create index idx_tb_obj_nolog tb_obj_nolog(object_id) nologging

scott@ORCL> select * from redo_size;    --查看當前的redo size

NAME                 VALUE

--------------- ----------              --基於nologging記錄模式重建索引產生的redo size為469160-448132=21028

redo size           469160

5.非歸檔模式下的LOGGING與NOLOGGING  

scott@ORCL> drop table tb_obj_log purge;

scott@ORCL> drop table tb_obj_nolog purge;

scott@ORCL> select log_mode,force_logging from v$database;  --切換日誌到非歸檔模式後，下面是查詢的結果

LOG_MODE     FOR

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

NOARCHIVELOG NO

scott@ORCL> select * from redo_size;    --查看當前的redo size

NAME                 VALUE

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

redo size             1688

scott@ORCL> create table tb_obj_log as select * from dba_objects;   --建立表對象，使用logging日誌記錄模式

scott@ORCL> select * from redo_size;   --查看當前的redo size

NAME                 VALUE

--------------- ----------            --使用logging日誌記錄模式，建立表對象之後產生的redo size 為-1688 =68548

redo size            70236

scott@ORCL> create table tb_obj_nolog nologging as select * from dba_objects; --建立表對象，使用nologging日誌記錄模式

scott@ORCL> select * from redo_size;   --查看當前的redo size

NAME                 VALUE

--------------- ----------             --使用nologging日誌記錄模式建立表對象之後產生的redo size為135464-70236=65228

redo size           135464

6.小結：

使用logging與nologging來建立對象或執行DML時

對於非歸檔模式下，其產生的日誌資訊(redo size)相差的並不大

對于歸檔模式下，logging模式產生的日誌將遠遠大於使用nologging模式產生的日誌量

二、使用DIRECT INSERT APPEND方式的LOGGING與NOLOGGING情況      

DIRECT INSERT APPEND是Oracle插入資料到資料庫的一種方式之一。使用APPEND方式來匯入，其實是將記錄直接存放到高水位線(HWM)之上，

而不考慮高水位線之下的空閑塊。

1.資料庫運行在非歸檔模式下

a.使用logging模式建立表

scott@ORCL> select log_mode from v$database;

LOG_MODE

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

NOARCHIVELOG  

scott@ORCL> select * from redo_size;

NAME                 VALUE

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

redo size             1764

scott@ORCL> create table tb_obj_log as select * from dba_objects where 1=0;

scott@ORCL> select * from redo_size;

NAME                 VALUE

--------------- ----------     --建表產生的redo量23908-1764=22144

redo size            23908  

scott@ORCL> insert into tb_obj_log select * from dba_objects;

11634 rows created.

Elapsed: 00:00:00.36

scott@ORCL> select * from redo_size;

NAME                 VALUE

--------------- ----------    --直接使用insert時產生的redo量1281060-23908=1257152

redo size          1281060  

scott@ORCL> insert /*+ append */ into tb_obj_log select * from dba_objects;

11634 rows created.

Elapsed: 00:00:00.26

scott@ORCL> select * from redo_size;

NAME                 VALUE

--------------- ----------    --使用append模式時產生的redo量1284740-1281060=3680

redo size          1284740    --普通insert比使用append insert多產生1257152/3680=341倍redo

b.使用nologging模式建立表

scott@ORCL> create table tb_obj_nolog nologging as select * from dba_objects where 1=0;

scott@ORCL> select * from redo_size;

NAME                 VALUE

--------------- ----------     --使用nologging建立空表tb_obj_nolog時產生的日誌量1305812-1284740=21072

redo size          1305812

scott@ORCL> insert into tb_obj_nolog select * from dba_objects;

11635 rows created.

Elapsed: 00:00:00.21

scott@ORCL> select * from redo_size;                          

NAME                 VALUE

--------------- ----------    --使用普通insert插入記錄產生的日誌量2562664-1305812=1256852

redo size          2562664

scott@ORCL> insert /* +append */ into tb_obj_nolog select * from dba_objects;

11635 rows created.

Elapsed: 00:00:00.18

scott@ORCL> select * from 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.前期處理

scott@ORCL> drop table tb_obj_log purge;

scott@ORCL> drop table tb_obj_nolog purge;

sys@ORCL> select log_mode from v$database;

LOG_MODE

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

ARCHIVELOG

b.建立表對象並進行比較

scott@ORCL> create table tb_obj_log as select * from dba_objects where 1=0;    --logging模式建立表對象

scott@ORCL> create table tb_obj_nolog nologging as select * from dba_objects where 1=0;--nologging模式建立表對象

scott@ORCL> select * from redo_size;    

NAME                 VALUE

--------------- ----------     --查看當前的redo size 為46844

redo size            46844

scott@ORCL> insert into tb_obj_log select * from dba_objects;    --為表tb_obj_log使用常規insert插入記錄

11598 rows created.

Elapsed: 00:00:00.25

scott@ORCL> select * from redo_size;

NAME                 VALUE

--------------- ----------       --表tb_obj_log使用常規insert插入記錄產生的redo size 為1299120-46844=1252276

redo size          1299120

scott@ORCL> insert into tb_obj_nolog select * from dba_objects;   --為表tb_obj_nolog使用常規insert插入記錄

11598 rows created.

Elapsed: 00:00:00.28

scott@ORCL> select * from redo_size;

NAME                 VALUE

--------------- ----------    --表tb_obj_nolog使用常規insert插入記錄產生的redo size 為2552880-1299120=1253760

redo size          2552880

scott@ORCL> insert /* +append */ into tb_obj_log select * from dba_objects;--表tb_obj_log使用insert append方式

11598 rows created.

Elapsed: 00:00:00.20

scott@ORCL> select * from redo_size;

NAME                 VALUE

--------------- ----------    --表tb_obj_log使用insert append插入記錄產生的redo size 為3750852-2552880=1197972

redo size          3750852

scott@ORCL> insert /* +append */ into tb_obj_nolog select * from dba_objects;--表tb_obj_nolog使用insert append方式

11598 rows created.

Elapsed: 00:00:00.18

scott@ORCL> select * from redo_size;

NAME                 VALUE

--------------- ----------    --表tb_obj_nolog使用insert append插入記錄產生的redo size 為4948764-3750852=1197912

redo size          4948764

c.redo的比較

歸檔模式下，具有nologging特性的表tb_obj_nolog，使用insert append方式插入的速度最快，且日誌量最小，為。而

logging特性的表tb_obj_log使用insert append方式時的日誌量為，相差不是很大。對於使用普通的insert插入，則

產生的日誌量差異比較大。

3.小結

對於表對象插入記錄時，使用常規insert 與使用direct insert append方式比較

在非歸檔模式下，表對象在使用nologging模式時，兩者產生的日誌量相差不大，而使用logging模式時，常規insert的日誌量遠

大於direct insert append方式。

在歸檔模式下，表對象使用logging模式，兩者產生的日誌量相差不大。而表對象使用nologging模式時，則使用insert append

將使得效能有所提高。

在非歸檔模式下的inesrt append操作將是效能最高的。

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方式追加記錄，最後再建立索引。

oracle視頻教程請關注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html