Oracle 11g - How to optimize slow parallel insert select?

Question

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? Edit: btw, the sequence is already cached to 1M

Accepted Answer

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 
```

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 - How to optimize slow parallel insert select?

Solution

Problem