Oracle的Nologging何时生效 与 批量insert加载数据速度

Question

如题所述

Answer 1

您好，很高兴为您解答。

一 非归档模式下
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.

连接到:
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

当前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方式(这个的原理可以参见<<批量DML操作优化建议.txt>>)，但纪录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加载数据后要做数据库全备.

如若满意，请点击右侧【采纳答案】，如若还有问题，请点击【追问】

希望我的回答对您有所帮助，望采纳！

Answer 2

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 /*+ append */ 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 /*+ append */ 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
1

我们注意到这里是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 /*+ append */ 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 /*+ append */ 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>

同样只有append才能减少redo的生成.

Answer 3

append+parallel+alter table nologging .