Oracle的Nologging何時生效 與 批次insert載入資料速度(zt)

一 非歸檔模式下

D:>sqlplus "/ as sysdba"

資料庫版本為9.2.0.1.0

SQL*Plus: Release 9.2.0.1.0 - Production on 星期一 8月 14 10:20:39 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

當前session產生的redo
SQL> create or replace view redo_size
2 as
3 select value
4 from v$mystat, v$statname
5 where v$mystat.statistic# = v$statname.statistic#
6 and v$statname.name = 'redo size';

檢視已建立。

授權給相應資料庫schema
SQL> grant select on redo_size to liyong;

授權成功。

SQL> shutdown immediate;
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。

SQL> startup mount;
ORACLE 例程已經啟動。

Total System Global Area 122755896 bytes
Fixed Size 453432 bytes
Variable Size 88080384 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
資料庫裝載完畢。

非歸檔模式
SQL> alter database noarchivelog;

資料庫已更改。

SQL> alter database open;

資料庫已更改。

SQL> create table redo_test as
2 select * from all_objects where 1=2;

表已建立。

SQL> select * from sys.redo_size;

VALUE
----------
59488

SQL> insert into redo_test
2 select * from all_objects;

已建立28260行。

SQL> select * from sys.redo_size;

VALUE
----------
3446080

SQL> insert /*+ append */ into redo_test
2 select * from all_objects;

已建立28260行。

SQL> commit;

提交完成。

SQL> select * from sys.redo_size;

VALUE
----------
3458156

可以看到insert /*+ append */ into方式redo產生很少.
SQL> select 3446080-59488,3458156-3446080 from dual;

3446080-59488 3458156-3446080
------------- ---------------
3386592 12076

將表redo_test置為nologging狀態.
SQL> alter table redo_test nologging;

表已更改。

SQL> select * from sys.redo_size;

VALUE
----------
3460052

SQL> insert into redo_test
2 select * from all_objects;

已建立28260行。

SQL> commit;

提交完成。

SQL> select * from sys.redo_size;

VALUE
----------
6805876

SQL> insert /*+ append */ into redo_test
2 select * from all_objects;

已建立28260行。

SQL> commit;

提交完成。

SQL> select * from sys.redo_size;

VALUE
----------
6818144

非歸檔模式下表的nologging狀態對於redo影響不大
SQL> select 6805876-3460052,6818144-6805876 from dual;

6805876-3460052 6818144-6805876
--------------- ---------------
3345824 12268

結論: 在非歸檔模式下透過insert /*+ append */ into方式批次載入資料可以大大減少redo產生.

二 歸檔模式下

SQL> shutdown immediate;
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> startup mount;
ORACLE 例程已經啟動。

Total System Global Area 122755896 bytes
Fixed Size 453432 bytes
Variable Size 88080384 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
資料庫裝載完畢。
SQL> alter database archivelog;

資料庫已更改。

SQL> alter database open;

資料庫已更改。

SQL> conn liyong
請輸入口令:
已連線。

將表redo_test重新置為logging
SQL> alter table redo_test logging;

表已更改。

SQL> select * from sys.redo_size;

VALUE
----------
5172

SQL> insert into redo_test
2 select * from all_objects;

已建立28260行。

SQL> commit;

提交完成。

SQL> select * from sys.redo_size;

VALUE
----------
3351344

SQL> insert /*+ append */ into redo_test
2 select * from all_objects;

已建立28260行。

SQL> commit;

提交完成。

SQL> select * from sys.redo_size;

VALUE
----------
6659932

可以看到在歸檔模式下，且表的logging屬性為true,insert /*+ append */ into這種方式也會紀錄大量redo
SQL> select 3351344-5172,6659932-3351344 from dual;

3351344-5172 6659932-3351344
------------ ---------------
3346172 3308588

將表置為nologging

SQL> alter table redo_test nologging;

表已更改。

SQL> select * from sys.redo_size;

VALUE
----------
6661820

SQL> insert into redo_test
2 select * from all_objects;

已建立28260行。

SQL> commit;

提交完成。

SQL> select * from sys.redo_size;

VALUE
----------
10008060

SQL> insert /*+ append */ into redo_test
2 select * from all_objects;

已建立28260行。

SQL> commit;

提交完成。

SQL> select * from sys.redo_size;

VALUE
----------
10022852

可以發現在歸檔模式，要設定表的logging屬性為false，才能透過insert /*+ append */ into大大減少redo產生.
SQL> select 10008060-6661820,10022852-10008060 from dual;

10008060-6661820 10022852-10008060
---------------- -----------------
3346240 14792

結論: 在歸檔模式下,要設定表的logging屬性為false，
才能透過insert /*+ append */ into大大減少redo.

三 下面我們再看一下在歸檔模式下，幾種批次insert操作的效率對比.

redo_test表有45W條記錄

SQL> select count(*) from redo_test;

COUNT(*)
----------
452160

1 最常見的批次資料載入 25秒

SQL> create table insert_normal as
2 select * from redo_test where 0=2;

表已建立。

SQL> set timing on

SQL> insert into insert_normal
2 select * from redo_test;

已建立452160行。

提交完成。
已用時間: 00: 00: 25.00

2 使用insert /*+ append */ into方式(這個的原理可以參見<>)，但紀錄redo. 17.07秒
SQL> create table insert_hwt
2 as
3 select * from redo_test where 0=2;

表已建立。
SQL> insert /*+ append */ into insert_hwt
2 select * from redo_test;

已建立452160行。

提交完成。
已用時間: 00: 00: 17.07

3 使用insert /*+ append */ into方式，且透過設定表nologging不紀錄redo.

SQL> create table insert_hwt_with_nologging nologging
2 as
3 select * from redo_test where 2=0;

表已建立。

/*
或者透過
alter table table_name nologging設定
*/

SQL> insert /*+ append */ into insert_hwt_with_nologging 11.03秒
2 select * from redo_test;

已建立452160行。

提交完成。
已用時間: 00: 00: 11.03

總結:

我們看到對於批次操作，如果設定表nologging，可以大大提高效能.原因就是Oracle沒有紀錄DML所產生的redo.
當然，這樣會影響到備份。nologging載入資料後要做資料庫全備.