一 非归档模式下

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.

"tk+K(ec1f0

 

 

2t R/^:t;\0连接到:
1v"]G WRiI0Oracle9i Enterprise Edition Release 9.2.0.1.0 - ProductionITPUB个人空间,yB1S }IET
With the Partitioning, OLAP and Oracle Data Mining optionsITPUB个人空间8y4lE9l"R2Y
JServer Release 9.2.0.1.0 - Production

 

当前session产生的redoITPUB个人空间0{(U)N^%j _
SQL> create or replace view redo_size
-W}H+^n[k._02 as
'n\M^sr7rL q x03 select valueITPUB个人空间(w1~l(K9`[@4\F
4 from v$mystat, v$statname
[Yez)BtL n)|nW05 where v$mystat.statistic# = v$statname.statistic#
1S^ b5l)i&HA06 and v$statname.name = 'redo size';

视图已建立。

授权给相应数据库schemaITPUB个人空间bl.`
_OS
SQL> grant select on redo_size to liyong;

授权成功。

SQL> shutdown immediate;
)U
S0~.]v5V |
PL q0数据库已经关闭。
#r1}W7w r`?B0已经卸载数据库。ITPUB个人空间 r-Z;~&`bR6\.C
ORACLE 例程已经关闭。

SQL> startup mount;ITPUB个人空间%v.we3Q#L6P%h,Gn7b"q4|
ORACLE 例程已经启动。

Total System Global Area 122755896 bytes
S@_%V @z G0Fixed Size 453432 bytesITPUB个人空间o2Ik^Qe~
Variable Size 88080384 bytes
r&UN)|8P
ljka S0Database Buffers 33554432 bytes
7y\o2@b/\0Redo Buffers 667648 bytesITPUB个人空间q2}1^5EOHh|O
数据库装载完毕。

非归档模式ITPUB个人空间QBs+Zn;C
SQL> alter database noarchivelog;

数据库已更改。

SQL> alter database open;

数据库已更改。

SQL> create table redo_test asITPUB个人空间M
ztAw s
2 select * from all_objects where 1=2;

表已创建。

SQL> select * from sys.redo_size;

VALUEITPUB个人空间p$_^%Ha|
----------ITPUB个人空间`9e4U6W%FMB
59488

SQL> insert into redo_testITPUB个人空间
B'M2JdriK$H"sv
2 select * from all_objects;

已创建28260行。

SQL> select * from sys.redo_size;

VALUEITPUB个人空间C;|!B3X,n+h
----------
:R#FL:eD,U+FBc03446080

SQL> insert /*+ append */ into redo_test
Q;P;K c8aGDsxb02 select * from all_objects;

已创建28260行。

SQL> commit;

提交完成。

SQL> select * from sys.redo_size;

VALUE
}|*G4G ^'@#m&gxD0----------ITPUB个人空间7L-{i~-y?B0d
3458156

可以看到insert /*+ append */ into方式redo产生很少.
(v_ P*T8L1W0SQL> select 3446080-59488,3458156-3446080 from dual;

3446080-59488 3458156-3446080ITPUB个人空间eS Q pv \
------------- ---------------
_0VZ:g4\!p A+QS03386592 12076

 

将表redo_test置为nologging状态.
3W&p4U3]'yz[rzc N3M9y0SQL> alter table redo_test nologging;

表已更改。

SQL> select * from sys.redo_size;

VALUE
T3I$V}Q0----------ITPUB个人空间o{B2mb
3460052

SQL> insert into redo_testITPUB个人空间 XTv2h[;[(py
2 select * from all_objects;

已创建28260行。

SQL> commit;

提交完成。

SQL> select * from sys.redo_size;

VALUEITPUB个人空间QB-?3{fq)H!J};n
----------
$AI9x P!pc06805876

SQL> insert /*+ append */ into redo_test
e$m,U"L;M*X+qX02 select * from all_objects;

已创建28260行。

SQL> commit;

提交完成。

SQL> select * from sys.redo_size;

VALUEITPUB个人空间.a@s X*b
T2`!H
Y1s
----------ITPUB个人空间6ylX$X&H\%Q
6818144

非归档模式下表的nologging状态对于redo影响不大
5n*^!? PQC ?9_0SQL> select 6805876-3460052,6818144-6805876 from dual;

6805876-3460052 6818144-6805876ITPUB个人空间8ZX(eU7l!Z Y
--------------- ---------------ITPUB个人空间1\R+P]N(h L
3345824 12268

ITPUB个人空间r-l t HG:e n
结论: 在非归档模式下通过insert /*+ append */ into方式批量加载数据可以大大减少redo产生.

 

 

 

 

二 归档模式下

ITPUB个人空间!i/ER ?:T/ie0P:P?
SQL> shutdown immediate;
p*U OA6u,E0数据库已经关闭。ITPUB个人空间dSL3r^
已经卸载数据库。ITPUB个人空间-GFr'_:G1\#F
ORACLE 例程已经关闭。
MU~3I0s0SQL> startup mount;
){ S`#ou fv4_qH0ORACLE 例程已经启动。

Total System Global Area 122755896 bytes
%@e~ ofJ0Fixed Size 453432 bytes
5cQ4v*W2y*q [0Variable Size 88080384 bytes
g0?8OI`t
IY0Database Buffers 33554432 bytes
az{v0Cuw0Redo Buffers 667648 bytes
r Ksd/S?0数据库装载完毕。ITPUB个人空间[
v/BCqn
SQL> alter database archivelog;

数据库已更改。

SQL> alter database open;

数据库已更改。

SQL> conn liyong
#N N;KTLI7\q8O ^0请输入口令:ITPUB个人空间/e0A oF4VE @
已连接。

ITPUB个人空间+p&a(k M y Z.|
将表redo_test重新置为logging
6EZG5XBDq7w
\0SQL> alter table redo_test logging;

表已更改。

SQL> select * from sys.redo_size;

VALUE
jT!bwXu0----------
D?Yv1X(iNQ-E05172

SQL> insert into redo_test
$d$Z!B
Tl0X02 select * from all_objects;

已创建28260行。

SQL> commit;

提交完成。

SQL> select * from sys.redo_size;

VALUE
D+|(YD Bj'gx5M%o*j0----------ITPUB个人空间LB3S2^8EI;R2I
3351344

SQL> insert /*+ append */ into redo_testITPUB个人空间6{
XsQ,`*L za
2 select * from all_objects;

已创建28260行。

SQL> commit;

提交完成。

SQL> select * from sys.redo_size;

VALUE
[D'ONV0----------
+A _!B5T\4s3E06659932

可以看到在归档模式下，且表的logging属性为true,insert /*+ append */ into这种方式也会纪录大量redoITPUB个人空间m0j#n*i fl;i
SQL> select 3351344-5172,6659932-3351344 from dual;

3351344-5172 6659932-3351344
#sLNN'\NZ0lT0------------ ---------------ITPUB个人空间7q6U$i N
tI!E;Zx
3346172 3308588

vV3h@R5Z Kq_F6B)Z0将表置为nologging

SQL> alter table redo_test nologging;

表已更改。

SQL> select * from sys.redo_size;

VALUE
6J:r E4[)_^P0----------ITPUB个人空间k}%r A{u&{
6661820

SQL> insert into redo_test
$@1qb,v@C02 select * from all_objects;

已创建28260行。

SQL> commit;

提交完成。

SQL> select * from sys.redo_size;

VALUE
W^9w'L&]yBD0----------ITPUB个人空间c#M-Q5v~"j(@
10008060

SQL> insert /*+ append */ into redo_test
&bD$C I&Dj^A&@02 select * from all_objects;

已创建28260行。

SQL> commit;

提交完成。

SQL> select * from sys.redo_size;

VALUE
4n ~ r'KU%E0----------
0N`Oe hw H010022852

可以发现在归档模式，要设置表的logging属性为false，才能通过insert /*+ append */ into大大减少redo产生.
,@.?-u2dOM#O0SQL> select 10008060-6661820,10022852-10008060 from dual;

10008060-6661820 10022852-10008060
a Y c C t,hG
`0o0---------------- -----------------
W/uh^ gKPr7a|03346240 14792

 

结论: 在归档模式下,要设置表的logging属性为false，
;ZEz)Ft0才能通过insert /*+ append */ into大大减少redo.

 

 

 

 

三 下面我们再看一下在归档模式下，几种批量insert操作的效率对比.

 

redo_test表有45W条记录

SQL> select count(*) from redo_test;

COUNT(*)ITPUB个人空间.Y]F\x-w_v3k
----------
'bif:v,ld L$@
n0452160

ITPUB个人空间[&F @jTZr
1 最常见的批量数据加载 25秒

SQL> create table insert_normal asITPUB个人空间6U/Qiw"^`Rj/w
2 select * from redo_test where 0=2;

表已创建。

SQL> set timing on

SQL> insert into insert_normal
(Eh9}~4B9Y02 select * from redo_test;

已创建452160行。

提交完成。ITPUB个人空间CRp-` D+D`)t-K"}
已用时间: 00: 00: 25.00

.Usr/Z[/b(M"Nc L02 使用insert /*+ append */ into方式(这个的原理可以参见<<批量DML操作优化建议.txt>>)，但纪录redo. 17.07秒
T6r$R4r+CTr0SQL> create table insert_hwt
ob5WI3nW-s02 asITPUB个人空间Q4`)] UHPk(|)O q`
3 select * from redo_test where 0=2;

表已创建。
8Q9h)I"qoS ^0SQL> insert /*+ append */ into insert_hwt
Z8Q I.K0~02 select * from redo_test;

已创建452160行。

提交完成。
Q(G0TlS0j5|0已用时间: 00: 00: 17.07

)cY9M[3R03 使用insert /*+ append */ into方式，且通过设置表nologging不纪录redo.

SQL> create table insert_hwt_with_nologging nologging
P/Be@gXj t!p ~@F02 as
"f!c |on?,m i03 select * from redo_test where 2=0;

表已创建。

/*ITPUB个人空间GpRa"L,m P7p J
或者通过
8k,]
Cr{0alter table table_name nologging设置
yamPR$l#\0*/

SQL> insert /*+ append */ into insert_hwt_with_nologging 11.03秒ITPUB个人空间'A |a-TQ
2 select * from redo_test;

已创建452160行。

提交完成。ITPUB个人空间&Aj5K&cu \4F,y:S
已用时间: 00: 00: 11.03

 

总结:

我们看到对于批量操作，如果设置表nologging，可以大大提高性能.原因就是Oracle没有纪录DML所产生的redo.
(r;S3^SI0当然，这样会影响到备份。nologging加载数据后要做数据库全备.