I have a procedure which bulk deletes the data from around 40-45 Production tables.
The basic structure of job is –
BULK DELETE From Production Table
(Around 45 FORALL clause used for different Production Tables)
I want to know after how many FORALL statements should I commit the data . ( Say every 5 or every 8) . Or should I commit after every FORALL statement. Kindly suggest keeping the performance point of view.
Well it all depends on how much data your deleting, the volume of undo used by it and most importantly is it one transaction i.e. if it fails should it rollback all the deleted data.
You can measure the volume of undo using either tracing or querying v$transaction (used_ublk column). Now if the blocks used is a significant amount of your undo tablespace (taking into account how long you retain undo for flashback) then you may need to commit in between deletes. Otherwise dont bother I dont think it will speed up your query, it just ensures your proc doesnt fail due to ora-1555 or other undo/rollback related errors.