How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?

Contrary to popular belief, one should COMMIT less frequently within a PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors. The higher the frequency of commit, the sooner the extents in the undo/ rollback segments will be cleared for new transactions, causing ORA-1555 errors.

To fix this problem one can easily rewrite code like this:

FOR records IN my_cursor LOOP
   ...do some stuff...
   COMMIT;
END LOOP;
COMMIT;

... to ...

FOR records IN my_cursor LOOP
   ...do some stuff...
   i := i+1;
   IF mod(i, 10000) = 0 THEN    -- Commit every 10000 records
      COMMIT;
   END IF;
END LOOP;
COMMIT;

If you still get ORA-1555 errors, contact your DBA to increase the undo/ rollback
segments.

NOTE: Although fetching across COMMITs work with Oracle, is not supported by the ANSI standard.