oracle 批量insert 锁,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 modeNo Archive Mode
Automatic archivalDisabled
Archive destination/u01/app/oracle/product/9.2.0.4/dbs/arch
Oldest online log sequence4564
Current log sequence4566
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
2select * 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
------------- ---------------
24751726852
/*
*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
2select * 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
--------------- ---------------
24419727088
*****非归档模式下表的nologging状态对于redo影响不大
结论:在非归档模式下通过insert /*+ append */ into方式批量加载数据可以大大减少redo产生.
in archive log mode:
SQL> archive log list;
Database log modeArchive Mode
Automatic archivalEnabled
SQL> select * from sys.redo_size;
VALUE
----------
56400
SQL> insert /*+ append */ into redo_test
2select * 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
------------- ------------
286200290624
可以看到在归档模式下，且表的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
2select * 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
------------- --------------
283228288752
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方式(这个的原理可以参见<>)，但纪录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加载数据后要做数据库全备.