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?

有关企业管理器的计划统计信息，请参见下文.我们还注意到语句不是并行运行的.这正常吗?

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 

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

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屋！