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!