mysql nologging_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

-------