Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003

    Unanswered: COMMIT frequency

    I have a procedure which bulk deletes the data from around 40-45 Production tables.
    The basic structure of job is


    FORALL 1..500
    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.

  2. #2
    Join Date
    Mar 2002
    Reading, UK
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts