关于使用insert append和parallel
2021-08-10 10:16:55

为避免将来的理解冲突，本文所有说明基于Oracle 10g版本。

 

关于使用insert append

Eygle大师曾经对insert append和logging/nologging的关系做过比较全面的测试和说明（http://www.itpub.net/thread-242761-1-1.html）。

这里不详述了，只给出结论：

 

非归档模式下，不管表是否设置logging/nologging，append都会减少redo和undo的生成。

归档模式下，append+nologging才能减少redo和undo的生成。

 

注意在direct insert 下，实际上操作是不会对数据产生redo和undo的，但这不代表不会产生redo和undo。

 

另外在direct insert后，由于插入的数据没有在redo中记录，此时如果需要进行恢复，插入的记录数据会丢失。所以在插入数据后，需要进行全库数据备份。

 

以append方式插入记录后，要执行commit，才能对表进行查询。否则会出现错误：

ORA-12838: 无法在并行模式下修改之后读/修改对象

 

另外insert append时在表上加“6”类型的锁，会阻塞表上的所有DML语句。因此在有业务运行的情况下要慎重使用。若同时执行多个insert append对同一个表并行加载数据，并不一定会提高速度。

 

使用append insert方法：

alter table t nologging;

insert /*+ append */ into test.t select * from test.t2;

commit;

 

关于使用parallel

可以在表级设置并行度，比如：

create table test nologging PARALLEL (DEGREE 7) as select * from dba_tables;

alter table t parallel (degree 4);

 

也可以通过SQL hint在会话级别设置，但是需要注意，如果想在执行DML语句时使用并行（比如insert、delete、update），需要enable parallel dml，比如：

alter session enable parallel dml;

insert /*+ append parallel(t 4) */ into t select * from t2;

 

顺便给出我以前的2个测试结论：

实验1：insert时间比较

insert into t select * from t2; （耗时00:00:12.96）

普通insert。

 

alter table t nologging;

insert /*+ append */ into t select * from t2;  （耗时00:00:00.74）

direct insert，时间最短效率最好。

 

alter session enable parallel dml;

insert /*+ append parallel(t 4) */ into t select * from t2; （耗时00:00:02.07）

direct insert +parallel，其实并没有direct insert效率高，不过比普通insert好点。

 

实验2：create table + parallel产生UNDO测试

方法1：

create table test as select * from dba_tables;

 

方法2：

create table test nologging as select * from dba_tables;

 

方法3：

create table test nologging PARALLEL (DEGREE 7) as select * from dba_tables;

 

方法4：

create table test as select * from dba_tables where 1=0;

insert into test select * from dba_tables;

commit;

 

监控方式：

SQL> select sid,username from v$session;

SQL> select * from v$sesstat where sid=(select sid  from v$session where username='TEST') and STATISTIC#=(select STATISTIC# from v$statname where name='undo change vector size');

 

所有测试执行三次，结果如下：

 

	第1次undo产生量(单位：bytes)	第2次	第3次
方法1(create table as select)	29212	30380	36220
方法2(nologging)	28684	26956	28640
方法3(nologging parallel)	90120	89312	91260
方法4(create table and insert data)	40660	43796	41940

 

结论：创建表时增加并行度会产生较多的undo量。