nologging和insert /*+append*/

nologging和insert /*+append*/

引文地址:http://blogold.chinaunix.net/u2/82363/showart_1802490.html
alter table test nologging
insert /*+ append */ into test select


ask tom上有過一篇文章，是說Oracle實際上需要滿足表是nologging和insert /*+append*/兩個條件才真正實現nologging的


在insert資料量很大的時候（千萬級），減少redo的產生對效能應該有很大的提高。
這是一個使用append和nologging對redo產生情況的實驗。
結論：
-------------------------------
一、非歸檔模式下：
沒有最佳化前 （1281372 redo size）
1、單一的使用nologging引數，對redo的產生沒有什麼影響。 （1214836 redo size）
2、單一的使用append提示，redo的減少很顯著 （43872 redo size）
3、nologging+append，更顯著 （1108 redo size）
二、歸檔模式下：
沒有最佳化前：
1、單獨使用nologging引數，（1231904 redo size）
2、單獨使用append提示， （1245804 redo size）
3、nologging + append， （3748 redo size）

a、使用nologging引數並不代表在dml操作中，oracle不產生redo，只是對於指定表的更新資料不產生redo，但是oracle還是要記錄這些操作，所以無論怎麼最佳化，dml操作肯定要產生redo，但是使用這些引數對redo size的影響還是非常可觀的。
b、單獨使用nologging引數，對redo size沒有多少影響，只有和append配合時，才能產生效果。
c、單獨使用append提示，對redo的產生影響很大，這是我到現在都不明白的道理，按說append是繞過freelists，直接去尋找新塊，能減少對freelists的爭用，為什麼會少這麼多redo呢？
d、歸檔模式和非歸檔模式下，引數影響不一樣，尤其是單獨使用append引數時，看來oracle對歸檔模式下出於安全考慮還是要多一些。
文章出處：


1.Nologging的設定跟資料庫的執行模式有關
a.資料庫執行在非歸檔模式下:

SQL> archive log list;

Database log mode No Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/oradata/hsjf/archive
Oldest online log sequence 155
Current log sequence 157

SQL> @redo

SQL> create table test as select * from dba_objects where 1=0;

Table created.

SQL> select * from redo_size;

VALUE
----------
63392

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

VALUE
----------
1150988

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

VALUE
----------
1152368

SQL> select (1152368 -1150988) redo_append,(1150988 -63392) redo from dual;

REDO_APPEND REDO
----------- ----------
1380 1087596

SQL> drop table test;

Table dropped.

我們看到在Noarchivelog模式下,對於常規表的insert append只產生少量redo

b.在歸檔模式下

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 235999908 bytes

Fixed Size 451236 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> @redo

SQL> create table test as select * from dba_objects where 1=0;

Table created.

SQL> select * from redo_size;

VALUE
----------
56288

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

VALUE
----------
1143948

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

VALUE
----------
2227712

SQL> select (2227712 -1143948) redo_append,(1143948 -56288) redo from dual;

REDO_APPEND REDO
----------- ----------
1083764 1087660

SQL> drop table test;

Table dropped.

我們看到在歸檔模式下,對於常規表的insert append產生和insert同樣的redo
此時的insert append實際上並不會有效能提高.
但是此時的append是生效了的

透過Logmnr分析日誌得到以下結果:

SQL> select operation,count(*)
2 from v$logmnr_contents
3 group by operation;

OPERATION COUNT(*)
-------------------------------- ----------
COMMIT 17
DIRECT INSERT 10470
INTERNAL 49
START 17

我們注意到這裡是DIRECT INSERT,而且是10470條記錄,也就是每條記錄都記錄了redo.

2.對於Nologging的table的處理

a. 在歸檔模式下:

SQL> create table test nologging as select * from dba_objects where 1=0;

Table created.

SQL> select * from redo_size;

VALUE
----------
2270284

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

VALUE
----------
3357644

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

VALUE
----------
3359024

SQL> select (3359024 -3357644) redo_append,(3357644 - 2270284) redo from dual;

REDO_APPEND REDO
----------- ----------
1380 1087360

SQL> drop table test;

Table dropped.

我們注意到,只有append才能減少redo

b.在非歸檔模式下:

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 235999908 bytes
Fixed Size 451236 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> @redo

SQL> create table test nologging as select * from dba_objects where 1=0;

Table created.

SQL> select * from redo_size;

VALUE
----------
56580

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

VALUE
----------
1144148

SQL>

SQL> insert into test select * from dba_objects;

10470 rows created.

SQL> select * from redo_size;

VALUE
----------
1145528

SQL> select (1145528 -1144148) redo_append,(1144148 -56580) redo from dual;

REDO_APPEND REDO
----------- ----------
1380 1087568

SQL>

備註: 以上操作對partition同樣有效

redo_size 指令碼

create or replace view redo_size as
select value
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';

[@more@]