insert 性能优化测试

insert 性能优化测试

1、append

关于insert /*+ append */需要注意以下三点：

a、非归档模式下，只需append就能大量减少redo的产生；归档模式下，只有append+nologging才能大量减少redo。
b、insert /*+ append / 时会对表加锁（排它锁），会阻塞表上的除了select以外所有DML语句；传统的DML在TM enqueue上使用模式3（row exclusive），
其允许其他DML在相同的模式上获得TM enqueue。但是直接路径加载在TM enqueue使用模式6（exclusive），这使其他DML在直接路径加载期间将被阻塞。
c、insert /+ append */ 直接路径加载，速度比常规加载方式快。因为是从HWM的位置开始插入，也许会造成空间浪费。

2、parallel

–会话级

alter session enable parallel dml;

alter session disable parallel dml;

–表级

alter table tb_test parallel (degree 4);

alter table tb_test noparallel;

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

alter session enable parallel dml;

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

3、测试过程

• （1）测试环境：

• 数据库非归档模式

• tb_test表上没有任何索引

• 测试时数据库上还有其他任务在执行

• 数据库版本11g 110204

所以在测试过程中truncate 和commit的时间比较长。

tb_normal_year_speed有1000万记录，表的大小有0.66GB ，体现一定的数据量。

SQL> create table tb_test as select * from tb_normal_year_speed where 1<>1;

Table created


Executed in 1.843 seconds

SQL> select round( t.BYTES/1024/1024/1024 ,2) GB
  2   from user_segments t where t.segment_name=upper('tb_normal_year_speed');

        GB
----------
      0.66

Executed in 0.074 seconds

（2）情况1：普通insert

避免高水位，truncate table 。

SQL> insert into tb_test select * from tb_normal_year_speed;

10000000 rows inserted


Executed in 879.98 seconds

SQL> commit;

Commit complete


Executed in 0.082 seconds


SQL> truncate table tb_test ;

Table truncated


Executed in 832.097 seconds

（3）情况2：append + nologging

SQL> alter table tb_test nologging;

Table altered


Executed in 0.065 seconds

SQL> insert /*+ append */  into tb_test select * from tb_normal_year_speed;

10000000 rows inserted


Executed in 32.413 seconds

SQL> commit;

Commit complete


Executed in 0.065 seconds


SQL> truncate table tb_test ;

Table truncated


Executed in 215.878 seconds

（4）情况3：append + nologging + parallel

SQL> alter session enable parallel dml;

Session altered


Executed in 0.002 seconds


SQL> alter table tb_test parallel (degree 4);

Table altered


Executed in 0.045 seconds


SQL> insert /*+ append parallel(t 4) */ into tb_test t  select * from tb_normal_year_speed; 

10000000 rows inserted


Executed in 66.986 seconds

SQL> commit;

Commit complete


Executed in 38.314 seconds


SQL> truncate table tb_test ;

Table truncated


Executed in 447.142 seconds

4、测试结论

数据库测试时，有其他任务在执行，因此看commit和truncate有时比较长，三次测试的环境都一样，效果对比如下：

测试情况	消耗时间（S）	说明
情况1：普通insert	879.98
情况2：append + nologging	32.413	效果最佳
情况3：append + nologging + parallel	66.986	并行降低一倍的效率