小菜毛毛技術(shù)分享

與大家共同成長

BlogJava :: 首頁 :: 聯(lián)系 :: 聚合

:: 管理

164 Posts :: 141 Stories :: 94 Comments :: 0 Trackbacks

Oracle的Nologging何時生效與批量insert加載數(shù)據(jù)速度(zt)

一非歸檔模式下

D:>sqlplus "/ as sysdba"

數(shù)據(jù)庫版本為9.2.0.1.0

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

連接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

當(dāng)前session產(chǎn)生的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';

視圖已建立。

授權(quán)給相應(yīng)數(shù)據(jù)庫schema
SQL> grant select on redo_size to liyong;

授權(quán)成功。

SQL> shutdown immediate;
數(shù)據(jù)庫已經(jīng)關(guān)閉。
已經(jīng)卸載數(shù)據(jù)庫。
ORACLE 例程已經(jīng)關(guān)閉。

SQL> startup mount;
ORACLE 例程已經(jīng)啟動。

Total System Global Area 122755896 bytes
Fixed Size 453432 bytes
Variable Size 88080384 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
數(shù)據(jù)庫裝載完畢。

非歸檔模式
SQL> alter database noarchivelog;

數(shù)據(jù)庫已更改。

SQL> alter database open;

數(shù)據(jù)庫已更改。

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

表已創(chuàng)建。

SQL> select * from sys.redo_size;

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

SQL> insert into redo_test
2 select * from all_objects;

已創(chuàng)建28260行。

SQL> select * from sys.redo_size;

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

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

已創(chuàng)建28260行。

SQL> commit;

提交完成。

SQL> select * from sys.redo_size;

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

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

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

將表redo_test置為nologging狀態(tài).
SQL> alter table redo_test nologging;

表已更改。

SQL> select * from sys.redo_size;

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

SQL> insert into redo_test
2 select * from all_objects;

已創(chuàng)建28260行。

SQL> commit;

提交完成。

SQL> select * from sys.redo_size;

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

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

已創(chuàng)建28260行。

SQL> commit;

提交完成。

SQL> select * from sys.redo_size;

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

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

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

結(jié)論: 在非歸檔模式下通過insert /*+ append */ into方式批量加載數(shù)據(jù)可以大大減少redo產(chǎn)生.

二歸檔模式下

SQL> shutdown immediate;
數(shù)據(jù)庫已經(jīng)關(guān)閉。
已經(jīng)卸載數(shù)據(jù)庫。
ORACLE 例程已經(jīng)關(guān)閉。
SQL> startup mount;
ORACLE 例程已經(jīng)啟動。

Total System Global Area 122755896 bytes
Fixed Size 453432 bytes
Variable Size 88080384 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
數(shù)據(jù)庫裝載完畢。
SQL> alter database archivelog;

數(shù)據(jù)庫已更改。

SQL> alter database open;

數(shù)據(jù)庫已更改。

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;

已創(chuàng)建28260行。

SQL> commit;

提交完成。

SQL> select * from sys.redo_size;

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

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

已創(chuàng)建28260行。

SQL> commit;

提交完成。

SQL> select * from sys.redo_size;

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

可以看到在歸檔模式下，且表的logging屬性為true,insert /*+ append */ into這種方式也會紀(jì)錄大量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;

已創(chuàng)建28260行。

SQL> commit;

提交完成。

SQL> select * from sys.redo_size;

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

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

已創(chuàng)建28260行。

SQL> commit;

提交完成。

SQL> select * from sys.redo_size;

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

可以發(fā)現(xiàn)在歸檔模式，要設(shè)置表的logging屬性為false，才能通過insert /*+ append */ into大大減少redo產(chǎn)生.
SQL> select 10008060-6661820,10022852-10008060 from dual;

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

結(jié)論: 在歸檔模式下,要設(shè)置表的logging屬性為false，
才能通過insert /*+ append */ into大大減少redo.

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

redo_test表有45W條記錄

SQL> select count(*) from redo_test;

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

1 最常見的批量數(shù)據(jù)加載 25秒

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

表已創(chuàng)建。

SQL> set timing on

SQL> insert into insert_normal
2 select * from redo_test;

已創(chuàng)建452160行。

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

2 使用insert /*+ append */ into方式(這個的原理可以參見<<批量DML操作優(yōu)化建議.txt>>)，但紀(jì)錄redo. 17.07秒
SQL> create table insert_hwt
2 as
3 select * from redo_test where 0=2;

表已創(chuàng)建。
SQL> insert /*+ append */ into insert_hwt
2 select * from redo_test;

已創(chuàng)建452160行。

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

3 使用insert /*+ append */ into方式，且通過設(shè)置表nologging不紀(jì)錄redo.

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

表已創(chuàng)建。

/*
或者通過
alter table table_name nologging設(shè)置
*/

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

已創(chuàng)建452160行。

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

總結(jié):

我們看到對于批量操作，如果設(shè)置表nologging，可以大大提高性能.原因就是Oracle沒有紀(jì)錄DML所產(chǎn)生的redo.
當(dāng)然，這樣會影響到備份。nologging加載數(shù)據(jù)后要做數(shù)據(jù)庫全備.

jolly10 發(fā)表于:2008.03.18 13:19 ::分類: ( 轉(zhuǎn)載學(xué)習(xí)內(nèi)容 ) ::閱讀:(1097次) :: 評論 (3) :: 引用 (0)

re: Oracle的Nologging何時生效與批量insert加載數(shù)據(jù)速度(zt) [回復(fù)]

下面我又試了試insert into XXX values (XXX)能不能少產(chǎn)生redo,做了試驗發(fā)現(xiàn),不行的,下面的過程.
SQL> select * from v$version where rownum archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 17
Current log sequence 19

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';

View created.

SQL> grant select on redo_size to ljg;

SQL> conn ljg/ljg
Connected.

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

SQL> CREATE OR REPLACE PROCEDURE p_loging
2 as
3 CURSOR c_a IS
4 SELECT * FROM all_objects;
5
6 BEGIN
7 FOR x IN c_a LOOP
8 INSERT INTO REDO_TEST
9 VALUES(x.OWNER, x.OBJECT_NAME, x.SUBOBJECT_NAME, x.OBJECT_ID, x.DATA_OBJE CT_ID,
10 x.OBJECT_TYPE, x.CREATED, x.LAST_DDL_TIME, x.TIMESTAMP, x.STATUS, x.TEMPORARY, x.GENERATED, x.SECONDARY);
11 END LOOP;
12 COMMIT;
13
14 END;
15 /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE p_nologing
2 as
3 CURSOR c_a IS
4 SELECT * FROM all_objects;
5
6 BEGIN
7 FOR x IN c_a LOOP
8 INSERT /*+ APPEND */ INTO REDO_TEST
9 VALUES(x.OWNER, x.OBJECT_NAME, x.SUBOBJECT_NAME, x.OBJECT_ID, x.DATA_OBJECT_ID,
10 x.OBJECT_TYPE, x.CREATED, x.LAST_DDL_TIME, x.TIMESTAMP, x.STATUS, x.TEMPORARY, x.GENERATED, x.SECONDARY);
11 END LOOP;
12 COMMIT;
13
14 END;
15 /

Procedure created.

SQL> select * from sys.redo_size;

VALUE
----------
85940

SQL> exec p_loging;

PL/SQL procedure successfully completed.

SQL> select * from sys.redo_size;

VALUE
----------
15273968

SQL> exec p_nologing;

PL/SQL procedure successfully completed.

SQL> select * from sys.redo_size;

VALUE
----------
30411272

SQL> select 15273968- 85940 logging,30411272-15273968 nologging from dual;

LOGGING NOLOGGING
---------- ----------
15188028 15137304

可以看到nologging和logging產(chǎn)生的redo差不多.

jolly10 評論于：2008.06.05 11:07

re: Oracle的Nologging何時生效與批量insert加載數(shù)據(jù)速度(zt) [回復(fù)]

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';

View created.

SQL> grant select on redo_size to ljg;

SQL> conn ljg/ljg
Connected.

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

Procedure created.

SQL> select * from sys.redo_size;

VALUE
----------
85940

SQL> exec p_loging;

PL/SQL procedure successfully completed.

SQL> select * from sys.redo_size;

VALUE
----------
15273968

SQL> exec p_nologing;

PL/SQL procedure successfully completed.

SQL> select * from sys.redo_size;

VALUE
----------
30411272

SQL> select 15273968- 85940 logging,30411272-15273968 nologging from dual;

LOGGING NOLOGGING
---------- ----------
15188028 15137304

可以看到nologging和logging產(chǎn)生的redo差不多.

jolly10 評論于：2008.06.05 11:07

re: Oracle的Nologging何時生效與批量insert加載數(shù)據(jù)速度(zt) [回復(fù)]

在ITPUB中問到可以用BULK COLLECT 來減少insert into values的redo.
CREATE OR REPLACE PROCEDURE p_BulkAdd
AS
TYPE Tredo_test IS TABLE OF REDO_TEST%ROWTYPE;
V_REDO_TEST Tredo_test;
BEGIN
SELECT * BULK COLLECT INTO V_REDO_TEST FROM ALL_OBJECTS;
FORALL X IN V_REDO_TEST.FIRST..V_REDO_TEST.LAST
INSERT INTO REDO_TEST VALUES V_REDO_TEST(X);
END;
/

SQL> select * from sys.redo_size;

VALUE
----------
30411272

SQL> exec p_bulkadd;

PL/SQL procedure successfully completed.

SQL> select * from sys.redo_size;

VALUE
----------
35050796

SQL> select 35050796-30411272 from dual;

35050796-30411272
-----------------
4639524

這個做的確是少了很多redo.是一個方法.

posted on 2009-12-18 13:11 小菜毛毛閱讀(6116) 評論(0) 編輯收藏所屬分類: 數(shù)據(jù)庫

新用戶注冊刷新評論列表


只有注冊用戶登錄后才能發(fā)表評論。




網(wǎng)站導(dǎo)航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關(guān)文章: 查看oracle執(zhí)行計劃 ORACLE執(zhí)行計劃的一些基本概念 Oracle系統(tǒng)表字典表深入學(xué)習(xí)Oracle分區(qū)表及分區(qū)索引 oracle 觸發(fā)器 trigger 調(diào)試關(guān)于錯誤:"ORA-04091: table is mutating, trigger/function may not see it"的分析(觸發(fā)器操作自身表) oracle java oracle 調(diào)用 java簡單示例 oracle調(diào)用java 有效創(chuàng)建oracle dblink 的兩種方式

小菜毛毛技術(shù)分享

常用鏈接

留言簿(15)

我參與的團隊

隨筆分類

隨筆檔案

文章分類

文章檔案

新聞檔案

收藏夾

搜索

最新評論

閱讀排行榜

評論排行榜