文档章节

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

o
 osc_mervd488
发布于 2018/04/23 17:45
字数 1413
阅读 17
收藏 0

精选30+云产品,助力企业轻松上云!>>>

前言

前段时间报表数据库上有条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效果会更好

o
粉丝 0
博文 500
码字总数 0
作品 0
私信 提问
加载中
请先登录后再评论。

暂无文章

如果你失明了,你怎么编程? - How can you program if you're blind?

问题: Sight is one of the senses most programmers take for granted. 视觉是大多数程序员认为理所当然的感官之一。 Most programmers would spend hours looking at a computer monitor......

技术盛宴
10分钟前
4
0
如何删除使用Python的easy_install安装的软件包? - How do I remove packages installed with Python's easy_install?

问题: Python's easy_install makes installing new packages extremely convenient. Python的easy_install使安装新包非常方便。 However, as far as I can tell, it doesn't implement th......

fyin1314
40分钟前
8
0
如何将逗号分隔的字符串转换为数组? - How to convert a comma separated string to an array?

问题: I have a comma separated string that I want to convert into an array, so I can loop through it. 我有一个逗号分隔的字符串,我想将其转换成数组,因此可以循环遍历它。 Is the...

富含淀粉
今天
13
0
深源恒际:担心个人身份被冒用?不存在!

本文作者:c****t 近日,苟晶被冒名顶替身份参加高考的事件在社会各界掀起广泛热议。事件调查结果公布后,舆论风向逆转,吃瓜群众认为当事人夸大其词消费了公众情绪,一边对当事人所遭遇的不...

百度开发者中心
昨天
5
0
CKEditor 5 + SpringBoot实战(三):SpringData JPA数据持久化

在本系列的文章中,我将介绍如何在Spring Boot Application中使用CKEditor编辑器。介绍的内容包括基本环境的搭建,文件上传,SpringData JPA数据持久化,CKEditor5的安装,CKEditor图片上传,...

树下魅狐
今天
9
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部