Insert语句加/*+APPEND */在循环中单条提交对系统的影响分析
来源:互联网 发布:java程序员电脑配置 编辑:程序博客网 时间:2024/06/06 10:49
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(empnonumber); --游标值引用表
create table emp_inter(numbernonumber); --中间表
create table emp_append_test(empnonumber); --目标表
(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 empnofrom 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- Insert语句加/*+APPEND */在循环中单条提交对系统的影响分析
- 分析Query 语句对系统性能的影响
- Query 语句对系统性能的影响案例分析
- StringBuilder/StringBuffer的insert, append复杂度分析
- insert 操作对undo的影响
- StringBuffer setLength 和 append对capacity的影响
- sqlite3 update/insert/insert or replace对触发器的影响
- 分析关于close_wait过多对系统造成的影响
- SAS 中的INSERT语句:PROC APPEND
- insert append 操作的注意事项
- 程序循环顺序对效率的影响
- Form表单的提交在IE6中对common-fileupload上传速度的影响
- 关于android SQLite 的insert操作对类型 REAL影响
- extended-insert对mysqldump及导入性能的影响
- Local prefixed index和Local nonprefixed index对select语句的性能影响分析
- insert /*+append */
- insert /*+ APPEND */
- insert /*+ APPEND */
- STL sort升序排序小发现
- ios开发,将子线程获取的数据实时更新到UI
- 机器学习基础(二)多元线性回归模型
- IOS线程数据篇10之Sqlite3数据库的使用
- Ubuntu防火墙设置
- Insert语句加/*+APPEND */在循环中单条提交对系统的影响分析
- windbg调试
- VC 判断CString字符串中各位是数字,大小写字母,符号,汉字
- HDU 1856 More is better (并查集)
- ios开发之app内启动用户评价
- C++ 指针和const限定符
- WV.3-动态链表-新结点总是链表头
- python 栈
- JSP中pageEncoding contentType 字符编码个人归纳总结