Insert语句加/*+APPEND */在循环中单条提交对系统的影响分析

1、/*+APPEND */提示的用途

/*+APPEND */提示,是一个INSERT语句专有的hint,它的作用,大家都知道是用来提升insert速度的,并且效果非常的明显,至于它的提升速度的工作原理,在笔者的另一篇博文《用直接路径(direct-path)insert提升性能的两种方法》中有提到(以优点方式提出),该文地址为:http://blog.csdn.net/ljunjie82/article/details/42615233

2、单条循环提交中使用/*+APPEND */的巨大影响

再好的东西,有好的一面,也有不好的一面,用得好,可以助你事半功倍,用不好,将会带来巨大的影响。

/*+APPEND */由于期是在高水位以上插入,以及/*+APPEND */会给表加6级排它锁的特性,所以试想,如果要在loop或if循环中,要循环的插入一百万行数据,每循环一次只有一行符合条件的数据插入,commit只能放在循环之内(/*+APPEND */决定着commit无法放在特环外面),即代表着一万行数据,有一千万次commit。

这种使用场景,笔者已经在多个项目中看到,所以在此将该种用法的影响分析出来供有需要的人士参考。

这样的操作,对ORACLE数据库将会带来怎样的严重后果?

3、影响分析测试

3.1 loop循环中使用/*+APPEND */ hint的INSERT单条提交场景

(1)创建三张测试表

create table emp(empno number);             -- 游标值引用表

create table emp_inter(numberno number);    --中间表

create table emp_append_test(empno number); --目标表

(2)向游标值引用表与中间表各插入10000行数据

set timing on;

declare

i number:=1;

begin

loop

insert into emp (empno) values (i);

insert into emp_inter (numberno) values (i);

commit;

i:=i+1;

exit when i=10001;

end loop;

end;

/

 

输出时间值:Elapsed: 00:00:02.8    --同时向两张表insert 10000行数据,耗时2.8秒

3.2 对空间占用的严重影响测试与分析

3.2.1对三张表所占用空间进行测试前记录

select ‘EMP‘ as table_name,count(distinct dbms_rowid.rowid_block_number(rowid)) as blocks from EMP

 union all

select ‘EMP_INTER‘ as table_name,count(distinct dbms_rowid.rowid_block_number(rowid)) as blocks from EMP_INTER

 union all

select ‘EMP_APPEND_TEST‘ as table_name,count(distinct dbms_rowid.rowid_block_number(rowid)) as blocks from EMP_APPEND_TEST

输出结果如下:

  table_name

blocks

EMP

16

EMP_INTER

16

EMP_APPEND_TEST

0

    在此已经看到,EMP、EMP_INTER两张表,各插入999行数据,占用block为16个,当前没有插入数据的EMP_APPEND_TEST表占用0个block。

3.2.2在loop循环中加/*+APPEND */hint做insert数据

向目标表emp_append_test插入数据

set serveroutput on

set timing on

declare

n number:=1;

begin

  for c in (select empno from emp)

loop

insert /*+APPEND */ into emp_append_test select * from emp_inter where numberno=c.empno;

n:=n+1;

commit;

end loop;

dbms_output.put_line(‘insert rows is :‘||n);

end;

/

输出值:insert rows is :10000     --插入10000行数据

Elapsed: 00:00:11.62      --此次向一张表中插入10000行数据,耗时11.62秒

3.2.3 再次查询三张表占用的block数量

select ‘EMP‘ as table_name,count(distinct dbms_rowid.rowid_block_number(rowid)) as blocks from EMP

 union all

select ‘EMP_INTER‘ as table_name,count(distinct dbms_rowid.rowid_block_number(rowid)) as blocks from EMP_INTER

 union all

select ‘EMP_APPEND_TEST‘ as table_name,count(distinct dbms_rowid.rowid_block_number(rowid)) as blocks from EMP_APPEND_TEST;

输出结果如下:

      table_name

blocks

EMP

16

EMP_INTER

16

EMP_APPEND_TEST

10000

从上面看来,结果是非常可怕的,插入一万行数据,占用一万个block,以每个block 8KB计算,一万行数据占用78.1MB左右(10000*8/1024)。

3.3 对查询性能影响

(1)对未使用/*+APPEND */循环单条commit的表EMP查询性能测试

SQL> set autotrace on statistics

SQL> select * from emp where empno =1;

-------------

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

         23  consistent gets

          0  physical reads

          0  redo size

        523  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

(2)对使用/*+APPEND */循环单条commit的表EMP_APPEND_TEST查询性能测试

SQL> set autotrace on statistics

SQL> select * from EMP_APPEND_TEST where empno = 1;

---------------------------

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

      20003  consistent gets

      10000  physical reads

          0  redo size

        523  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

(3)查询性能结果比较:

table_name

consistent gets

physical reads

EMP

23

21

EMP_APPEND_TEST

20003

10024

consistent gets翻了869倍

physical reads翻了477倍

4、问题小结

    Insert语句加/*+APPEND*/ hint在循环中单条提交,由于/*+APPEND */ hint是在高水位线以上插入的特性,导致每提交一次,就会取一个新的block存放,高水位就上推一个block,以及/*+APPEND */ hint会给表加6级排它锁的特导,导致必须在commit后才能插入新的数据,大量单条/*+APPEND */插入,使得表急剧增大,除对insert本身造成性能影响之外,对以后的select、update、delete更是造成更巨大的影响。

 

 

本文作者:黎俊杰(网名:踩点),从事”系统架构、操作系统、存储设备、数据库、中间件、应用程序“六个层面系统性的性能优化工作

欢迎加入 系统性能优化专业群,共同探讨性能优化技术。群号:258187244

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。