黎俊杰，性能优化专家，跟随国内知名性能优化大师白鳝（老白）多年，子衿技术团队性能优化主要技术骨干，具有12年以上大型制造物流、电力能源行业信息系统需求设计与管控、业务逻辑设计、软硬件架构设计、数据存储结构设计、数据库存储过程&函数&触发器&SQL语句的开发与优化、小机&X86服务器硬件维护与优化、AIX&Linux操作系统运维与优化、大型Oracle&PostgreSQL高可用设计实施运维及优化、Weblogic中间件运维与优化、HP&IBM&EMC存储设备运维与优化、大型园区主干网络设计与思科防火墙&路由器&三层交换机&二层交换机配置管理经验，积累了大量规划设计、故障分析诊断、优化调整的技术文档。对数据库自动化运维、趋势预测、智能告警、报表分析等方面具有独到的研究与实际开发应用经验。

目 录

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

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

3、影响分析测试

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

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

◆3.3 对查询性能影响

4、问题小结

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

/*+APPEND */提示，是一个INSERT语句专有的hint，它的作用，是用来提升insert速度的，并且效果非常的明显，至于它的提升速度的工作原理，在笔者的另一篇博文《用直接路径（direct-path）insert提升性能的两种方法》中有提到（以优点方式提出），该文地址为：

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

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

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

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

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

3、影响分析测试3.1 loop循环中使用/*+APPEND */ hint的INSERT单条提交场景

（1）创建三张测试表

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

createtable emp_inter(numberno number); --中间表

createtable emp_append_test(empno number); --目标表

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

set timing on;

declare

i number:=1;

begin

loop

insertinto emp (empno)values(i);

insertinto emp_inter (numberno)values(i);

commit;

i:=i+1;

exitwhen i=10001;

endloop;

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

unionall

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

unionall

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

在此已经看到，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;

endloop;

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

unionall

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

unionall

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更是带来更巨大的性能影响。

联系我们

子衿技术公众号，邀请IT界技术大牛激扬文字，发表真知灼见，篇篇干货。

我们依托南瑞集成实验室，开展线下交流，动手实践，欢迎您的加入！

地址：南京市江宁区诚信大道19号南京南瑞集团公司信息系统集成分公司

电话：025-81095705