关于带多步数据更新的存储过程优化

Question

提问：
现在小弟有一存储过程，跑数据的时候是3K/M，但是性能还是不能满足业务需求，需要优化，麻烦大大。处理过程大致如下：（a，b，c，d表中的数据都是主键直接查询得出）open cur fetch cur获取a表数据获取b表数据获取c表数据获取d表数据游标数据，a，b，c，d表写入自己定义的recordinsert 目标表commit
还有。。在cur的SQL语句中使用decode 或者||会不会影响效率。
跪谢！

Answer 1

补充一下，需要处理的数据大概500W+，需要在2H内完成，机器性能不是问题，因为类似的存储过程跑1000W+的数据也跑过，大概也是2H+。

Answer 2

四表之间有关联吗？如果没有很复杂的业务逻辑,我一般不采用cursor．
把你的详细需求以及处理数据量帖出来看看，能不能用sql直接完成．

Answer 3

1.可能的话把目标表索引去掉，待系统空闲时再加上--这是馊主意，看业务情况而定。
2.把cursor的记录分了组，每一组commit一次--要分析瓶颈是否在回滚段上。

Answer 4

4表无关联，怎样的业务逻辑才算不负责。。初次写存储过程。。不是太明白。。a，b，c，d表的数据也要依靠cur中的数据才能查到，并且fetch的时候将cur的数据放到了record里面。。这样会影响性能吗?

Answer 5

适当的分了组分别commit,看总时间是否能缩短，如果能了就不用管回滚段的问题了，因为问题已解决了，如果不能说明不是回滚段的问题。

Answer 6

适当是选择多少？我现在是每1K条commit一次。。感觉速度没什么变化是否要改再取小一点？？

Answer 7

既然机子没有问题,可以增大回滚段的大小或undo文件的个数.

Answer 8

感谢Allan_xd 。现在 我在对每个表的操作前都加了insert into tmp values (p_taskcode,'XX',systimestamp);来观察去各个表数据的大概时间，发现对某个表的操作比其他表操作耗时，多了一个数量级的时间，但是呢对这些表的访问都是用主键的这样怎么办？

Answer 9

好像fetch cur into rec_XXX 也比较耗时间

Answer 10

最好把业务说清楚一些，DBA并不是拿起一条语句就优化的。
abcd四个表没有任何关联？
那插入目标表的规则呢？

Answer 11

恩，4个表没有任何关联，从4个表中取数放到自己写的record的里面就完了。插入规则是指什么？业务逻辑是从一个表里取数，然后根据该表中的数据分别去4个表中取所需的数据，最后插入到目标表中。

Answer 12

lz,不要在循环中执行sql,包括你a,b,c,d的读取和insert的执行。另外，不要过于频繁地执行commit既然a,b,c,d的数据可以通过主键获得，那么意味着a,b,c,d的相关数据完全可以同游标的sql合并一次性取出。也许甚至可以一条sql完成所有操作而不需要游标和循环。

Answer 13

如果这样的话是一个相当复杂的SQL如果改成这种方案能大幅提高效率？直接insert XXXselect XX....XX  from ...where ....................是这样吗？如果是这种方案的话，如果出现异常如何处理？

Answer 14

那就说  游标中的数据先写入表中，然后再4表关联更新目标表？？update不是更慢了？

Answer 15

插入，再插入之前是需要取abcd4个表中的数据谢谢

Answer 16

一般情况下，要检索同样的数据，如果一条SQL可以完成，那么它会比循环读取的总耗时要短的多。当然，正确的执行计划是必要的。
假设你的游标里有10000条数据，那么要完成整个循环，就要执行40000次检索，10000次插入，10000次游标的递归调用，10000次提交，光是sql引擎的切换就耗费大量时间。至于异常处理，你在循环内可以作的，在循环外也是可以的，比如使用DBMS_ERRLOG的功能。如果可以，可以把你代码贴出来，这样可以更直观一些，别人调整起来也更有针对性。