大数据量下的DML操作 bulk collect into limit forall commit;

最新推荐文章于 2021-06-16 17:55:59 发布

原创最新推荐文章于 2021-06-16 17:55:59 发布 · 3.3k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#performance #insert #collections #processing #behavior #user

Oracle 开发专栏收录该内容

129 篇文章

订阅专栏

本文介绍如何通过BULK COLLECT和FORALL提升大数据量DML操作的效率。利用批量收集和批量插入减少PL/SQL与SQL之间的切换，从而显著提高处理速度。需要注意合理设置LIMIT避免PGA内存溢出。

如果有个大数据量的DML操作事务,在OLAP报表等低并发库里. 并且强制归档模式中.

采用BULK 和FORALL 会比较快!

open cur_COLUMN_USER;
  loop
   fetch cur_COLUMN_USER bulk collect
   into 
          l_ARY_statedate,                    
          l_ARY_form,
          l_ARY_columnid,           
          l_ARY_usernumber,                   
          l_ARY_new_user,               
          l_ARY_exit_use    
   limit g_batch_size_n;
   
   forall i in 1..l_ARY_statedate.count
    insert into content_lst_day
    (......)
    values(l_ary_statedate(i),....);

 commit;
end loop;

相对使用普通游标循环提取数据出来处理的话会快很多.

原因 1 bulk collect into 到数组可以一次型把数据提取出来,减少了循环当中PL/SQL和SQL引撑的切换时间

原因 2 forall in ..... 也是一次型提交数据到某个地方也同样减少了循环当中PL/SQL和SQL引撑的切换时间

注意 1 数据太大要设置合理的LIMIT 否则提取到数组的数据会爆了PGA的回话内存

原因 3 bulk 内部操作对insert delete 做了优化采用批量方法.极大减少了redo 和Undo 使用量.

原因 4 为证明当一个大数据量的insert 会超级慢,如果分批插入的总时间比一次插入省很多时间.

An ORA-22813 when using BULK COLLECT is typically expected behavior indicating that you have exceeded the amount of free memory in the PGA. As collections are processed by PL/SQL they use the PGA to store their memory structures. Depending on the LIMIT size of the BULK COLLECT and additional processing of the collected data you may exceed the free memory of the PGA. While intuitively you may think that increasing the PGA memory and increasing the LIMIT size will increase performance, the following example shows you that this is not true in this case. So, by reviewing this example you should be able to strike a balance between a reasonable LIMIT size and the size of the PGA while maintaining a high level of performance using BULK COLLECT.