Insert 语句对 nologging 与 logging表 在不同场景下的优化

前言

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

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

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

环境准备

数据库版本	基础表	nologging表	logging表
Oracle 11g	T1	T2	T3

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

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

72813 rows created.

SQL> /

145626 rows created.

SQL> /

291252 rows created.

SQL> select count(*) from t1;

  COUNT(*)
----------
    582504

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

Table altered.

数据库处于ARCHIVE时

常规插入

nologging 表T2

SQL> insert into t2 select * from t1;
commit;
582824 rows created.
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

---------------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT     |  |   582K|    53M|  1455   (2)| 00:00:18 |
|   1 |  LOAD TABLE CONVENTIONAL | T2   |   |   |        |      |
|   2 |   TABLE ACCESS FULL  | T1   |   582K|    53M|  1455   (2)| 00:00:18 |
---------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
       3345  recursive calls
      46879  db block gets
      27878  consistent gets
       8269  physical reads
   67752144  redo size
    838  bytes sent via SQL*Net to client
    784  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      3  sorts (memory)
      0  sorts (disk)
     582824  rows processed

SQL>
Commit complete. 

耗费：67752144 redo size

logging 表T3

SQL> insert into t3 select * from t1;
commit;
582824 rows created.
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

---------------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT     |  |   582K|    53M|  1455   (2)| 00:00:18 |
|   1 |  LOAD TABLE CONVENTIONAL | T3   |   |   |        |      |
|   2 |   TABLE ACCESS FULL  | T1   |   582K|    53M|  1455   (2)| 00:00:18 |
---------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
       2860  recursive calls
      46875  db block gets
      27811  consistent gets
      1  physical reads
   67875992  redo size
    829  bytes sent via SQL*Net to client
    784  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
     582824  rows processed

SQL>
Commit complete.

耗费：67875992 redo size

append 插入

nologging 表T2

SQL> insert /*+ append */  into t2 select * from t1;
commit;
582824 rows created.

Execution Plan
----------------------------------------------------------

ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel

SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
       2627  recursive calls
       9324  db block gets
       8832  consistent gets
      0  physical reads
     143436  redo size
    824  bytes sent via SQL*Net to client
    798  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
     582824  rows processed

耗费：143436 redo size

logging 表T3

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

582824 rows created.

Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel

SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
       2627  recursive calls
       9327  db block gets
       8832  consistent gets
      0  physical reads
   68384900  redo size
    822  bytes sent via SQL*Net to client
    797  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
     582824  rows processed

耗费：68384900 redo size

parallel + append 插入

nologging 表T2

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

SQL> 

582824 rows created.

Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel

SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
     52  recursive calls
     32  db block gets
     19  consistent gets
      0  physical reads
      21916  redo size
    824  bytes sent via SQL*Net to client
    809  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
     582824  rows processed

耗费：21916 redo size

logging 表T3

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

SQL> 

582824 rows created.

Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel

SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
     50  recursive calls
     33  db block gets
     20  consistent gets
      0  physical reads
      21308  redo size
    824  bytes sent via SQL*Net to client
    808  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
     582824  rows processed

耗费：21308 redo size

数据库处于NOARCHIVE时

常规插入

nologging 表T2

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

---------------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT     |  |   582K|    53M|  1455   (2)| 00:00:18 |
|   1 |  LOAD TABLE CONVENTIONAL | T2   |   |   |        |      |
|   2 |   TABLE ACCESS FULL  | T1   |   582K|    53M|  1455   (2)| 00:00:18 |
---------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
       2538  recursive calls
      46869  db block gets
      27796  consistent gets
       8266  physical reads
   67754744  redo size
    824  bytes sent via SQL*Net to client
    784  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
     582824  rows processed

耗费：67754744 redo size

logging 表T3

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

---------------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT     |  |   582K|    53M|  1455   (2)| 00:00:18 |
|   1 |  LOAD TABLE CONVENTIONAL | T3   |   |   |        |      |
|   2 |   TABLE ACCESS FULL  | T1   |   582K|    53M|  1455   (2)| 00:00:18 |
---------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
       2593  recursive calls
      46873  db block gets
      27800  consistent gets
       1600  physical reads
   67757328  redo size
    824  bytes sent via SQL*Net to client
    784  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      3  sorts (memory)
      0  sorts (disk)
     582824  rows processed

耗费：67757328 redo size

append 插入

nologging 表T2

Statistics
----------------------------------------------------------
       2627  recursive calls
       9324  db block gets
       8832  consistent gets
       2993  physical reads
     143480  redo size
    822  bytes sent via SQL*Net to client
    798  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
     582824  rows processed

耗费：143480 redo size

logging 表T3

Statistics
----------------------------------------------------------
       2627  recursive calls
       9327  db block gets
       8832  consistent gets
      0  physical reads
     143420  redo size
    821  bytes sent via SQL*Net to client
    798  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
     582824  rows processed

耗费：143420 redo size

parallel + append 插入

nologging 表T2

Statistics
----------------------------------------------------------
     50  recursive calls
     32  db block gets
     21  consistent gets
      0  physical reads
      21896  redo size
    823  bytes sent via SQL*Net to client
    810  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
     582824  rows processed

耗费：21896 redo size

logging 表T3

Statistics
----------------------------------------------------------
     50  recursive calls
     33  db block gets
     20  consistent gets
      0  physical reads
      21896  redo size
    821  bytes sent via SQL*Net to client
    809  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
     582824  rows processed

耗费：21896 redo size

综合比较

属性	表名	常规插入产生的redo size	apppend插入产生的redo size	apppend + parallel插入产生的redo size
数据库模式	archive
nologing	t2	67752144	143436	21916
loging	t3	67875992	68384900	21308
数据库模式	noarchive
nologing	t2	67754744	143480	21896
loging	t3	67757328	143420	21896

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

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