Oracle 11g - 如何优化慢速并行插入选择? [英] Oracle 11g - How to optimize slow parallel insert select?

问题描述

我们想加快下面并行插入语句的运行速度.我们预计插入大约 8000 万条记录，大约需要 2 个小时才能完成.

we want to speed up the run of the parallel insert statement below. We are expecting to insert around 80M records and it is taking around 2 hours to finish.

INSERT /*+ PARALLEL(STAGING_EX,16) APPEND NOLOGGING */ INTO STAGING_EX (ID, TRAN_DT, 
RECON_DT_START, RECON_DT_END, RECON_CONFIG_ID, RECON_PM_ID) 
SELECT /*+PARALLEL(PM,16) */ SEQ_RESULT_ID.nextval, sysdate, sysdate, sysdate, 
'8a038312403e859201405245eed00c42', T1.ID FROM PM T1 WHERE STATUS = 1 and not 
exists(select 1 from RESULT where T1.ID = RECON_PM_ID and CREATE_DT >= sysdate - 60) and 
UPLOAD_DT >= sysdate - 1 and (FUND_SRC_TYPE = :1) 

我们认为缓存不存在列的结果会加快插入速度.我们如何执行缓存?任何想法如何加速插入?

We think that caching the results of the not exist column will speed up the inserts. How do we perform the caching? Any ideas how else to speed up the insert?

请参阅下面的 Enterprise Manager 计划统计信息.我们还注意到这些语句不是并行运行的.这正常吗?

Please see below for plan statistics from Enterprise Manager. Also we noticed that the statements are not being run in parallel. Is this normal?

顺便说一句，序列已经缓存到 1M

btw, the sequence is already cached to 1M

推荐答案

尝试使用更多绑定变量，尤其是在可能发生嵌套循环的地方.我注意到你可以在像

Try using more bind variables, especially where nested loops might happen. I've noticed that you can use it in cases like

CREATE_DT >= :YOUR_DATE instead of CREATE_DT >= sysdate - 60 

我认为这可以解释为什么您在执行计划的最低部分有 1.8 亿次执行，即使更新查询的整个其他部分仍然是 7900 万次中的 800 万次.

I think this would explain why you have 180 million executions in the lowest part of your execution plan even though the whole other part of the update query is still at 8 million out of your 79 million.

这篇关于Oracle 11g - 如何优化慢速并行插入选择?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！