Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Best Practice when using an EXECUTE IMMEDIATE running a large DML?

Sam_PNov 18 2019 — edited Nov 19 2019

Hello,

What is the best practice when using an EXECUTE IMMEDIATE running a very large DML operation like, say a MERGE? To be specific, I have 2 scenarios below -- one which embeds a COMMIT inside the string that is executed by an EXECUTE IMMEDIATE with IN and OUT params being supplied dynamically via bind variables while the other scenario has a COMMIT outside. Does Oracle treat this differently in terms of performance?

SCENARIO A: COMMIT is embedded inside the EXECUTE IMMEDIATE string

EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';

EXECUTE IMMEDIATE 'BEGIN MERGE /*+ PARALLEL(8) */ INTO .......; numrows := SQL%ROWCOUNT; COMMIT; END;' USING IN a, b, OUT c;

OR

SCENARIO B: COMMIT is executed outside the EXECUTE IMMEDIATE string

EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';

EXECUTE IMMEDIATE 'BEGIN MERGE /*+ PARALLEL(8) */ INTO .......; numrows := SQL%ROWCOUNT; END;' USING IN a, b, OUT c;

COMMIT;

Which one do you recommend and why?

Thanks!

This post has been answered by Paulzip on Nov 18 2019
Jump to Answer
Comments
Post Details
Added on Nov 18 2019
4 comments
2,853 views