概述

前段时间其中一个报表数据库上有条insert sql语句，因为插入的大量数据，执行非常慢，所以需要对其进行分析优化。

分析步骤是在：ARCHIVE与NOARCHIVE模式下进行。

测试场景: 分别对表的常规插入，表在append插入，表在append + parallel插入进行性能测试，得出结果。

01

环境准备

基于Oracle11.2.0.1测试

SQL> create user test identified by test default tablespace users;
SQL> grant resource, connect to test;
SQL> grant select any table to test;
SQL> grant select any dictionary to test;
SQL> conn test/test;

#创建T1,T2,T3表
SQL> create table t1 as select * from dba_objects;
SQL> create table t2 as select * from dba_objects where 1=2;
SQL> create table t3 as select * from dba_objects where 1=2;

#往T1表插入数据
SQL> insert into t1 select * from t1;
SQL> /
SQL> /
SQL> select count(*) from t1;

#设置T2表为nologging属性
SQL> alter table t2 nologging;

02

数据库处于ARCHIVE时

1、常规插入

1.1、nologging 表T2

SQL> set autotrace on;
SQL> insert into t2 select * from t1; 
SQL> commit;

耗费：82563832 redo size

1.2、logging 表T3

SQL> insert into t3 select * from t1; 
SQL> commit;

耗费： 82320896 redo size

2、append 插入

2.1、nologging 表T2

SQL> insert /*+ append */ into t2 select * from t1; 
SQL> commit;

耗费：62068 redo size

2.2、logging 表T3

SQL> insert /*+ append */ into t3 select * from t1; 
SQL> commit;

耗费：83036976 redo size

3、parallel + append 插入

3.1、nologging 表T2

SQL> alter session enable parallel dml;
SQL> insert /*+ append parallel(2) */ into t2 select * from t1; 
SQL> commit;

耗费：28512 redo size

3.2、logging 表T3

SQL> alter session enable parallel dml;
SQL> insert /*+ append parallel(2)*/ into t3 select * from t1; 
SQL> commit;

耗费：28352 redo size

03

数据库处于NOARCHIVE时

1、常规插入

1.1、nologging 表T2

SQL> set autotrace on;
SQL> insert into t2 select * from t1; 
SQL> commit;

耗费：82368872 redo size

1.2、logging 表T3

SQL> insert into t3 select * from t1; 
SQL> commit;

耗费： 82367200 redo size

2、append 插入

2.1、nologging 表T2

SQL> insert /*+ append */ into t2 select * from t1; 
SQL> commit;

耗费：62328 redo size

2.2、logging 表T3

SQL> insert /*+ append */ into t3 select * from t1; 
SQL> commit;

耗费：62268 redo size

3、parallel + append 插入

3.1、nologging 表T2

SQL> alter session enable parallel dml;
SQL> insert /*+ append parallel(2) */ into t2 select * from t1; 
SQL> commit;

耗费：28468 redo size

3.2、logging 表T3

SQL> alter session enable parallel dml;
SQL> insert /*+ append parallel(2)*/ into t3 select * from t1; 
SQL> commit;

耗费：28484 redo size

04

综合比较

1）数据库处于ARCHIVE模式时，对logging表执行append插入，是对性能没有优化的。加并行parallel才会有影响。

2）数据库处于NOARCHIVE模式时，对logging表执行append插入，可以有效的提升性能。当然加并行parallel效果会更好

这个实验还是花了挺长时间去测试的，大家有空也可以自己测试一下。后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注一下~