oracle nologging 和批量insert

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

 

 

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /u01/app/oracle/product/9.2.0.4/dbs/arch

Oldest online log sequence     4564

Current log sequence           4566

SQL> grant select on redo_size to zhf;

 

Grant succeeded.

 

SQL> conn zhf/zhf

Connected.

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

 

Table created.

 

SQL> select * from sys.redo_size;

 

     VALUE

----------

     46524

 

SQL> insert into redo_test select * from all_objects;

 

21991 rows created.

 

SQL> select * from sys.redo_size;

 

     VALUE

----------

   2521696

 

SQL> insert /*+ append */ into redo_test

  2  select * from all_objects;

 

21991 rows created.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from sys.redo_size;

 

     VALUE

----------

   2528548

 

SQL> select 2521696-46524, 2528548-2521696 from dual;

 

2521696-46524 2528548-2521696

------------- ---------------

      2475172            6852

 

/*

 *noarchivelog mode, table: logging  可以看到insert /*+ append */ into方式redo产生很少.

 */

 

SQL> alter table redo_test nologging;

 

Table altered.

 

SQL> select * from sys.redo_size;

 

     VALUE

----------

   2530400

 

SQL> insert into redo_test select * from all_objects;

 

21991 rows created.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from sys.redo_size;

 

     VALUE

----------

   4972372

 

SQL> insert /*+ append */ into redo_test

  2  select * from all_objects;

 

21991 rows created.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from sys.redo_size;

 

     VALUE

----------

   4979460

 

SQL> select 4972372 -2530400, 4979460 -4972372 from dual;

 

4972372-2530400 4979460-4972372

--------------- ---------------

        2441972            7088

*****非归档模式下表的nologging状态对于redo影响不大

 

结论: 在非归档模式下通过insert /*+ append */ into方式批量加载数据可以大大减少redo产生.

 

 

in archive log mode:

 

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

 

SQL> select * from sys.redo_size;

 

     VALUE

----------

     56400

 

SQL> insert /*+ append */ into redo_test

  2  select * from all_objects;

 

2745 rows created.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from sys.redo_size;

 

     VALUE

----------

    347024

 

SQL> insert into redo_test select * from all_objects;

 

2745 rows created.

 

SQL> select * from sys.redo_size;

 

     VALUE

----------

    633224

 

SQL> select 633224-347024, 347024-56400 from dual;

 

633224-347024 347024-56400

------------- ------------

       286200       290624

 

 

 

可以看到在归档模式下，且表的logging属性为true,insert /*+ append */ into这种方式也会纪录大量redo

 

 

SQL> alter table redo_test nologging;

 

Table altered.

 

SQL> select * from sys.redo_size;

 

     VALUE

----------

    635148

 

SQL> insert into redo_test

  2  select * from all_objects;

 

2745 rows created.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from sys.redo_size;

 

     VALUE

----------

    918376

 

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

 

2745 rows created.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from sys.redo_size;

 

     VALUE

----------

   1207128

 

SQL> select 918376-635148, 1207128-918376 from dual;

 

918376-635148 1207128-918376

------------- --------------

       283228         288752

 

 

Archivelog 模式下，设置表的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加载数据后要做数据库全备.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10173379/viewspace-620076/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10173379/viewspace-620076/