Re: When to Commit?

edmond wrote:
> I often hear that for long txn, we should commit occasionally rather than
> having a long outstanding txn. But in Expert one-on-one Oracle, the author
> suggest we should commit when required rather than manually inserting commit
> i.e. if our logic is to commit at end of the long txn, so be it (so long as
> our rollback segment is large enough).
>
> What are the considerations behind these 2 approaches?

Advocates of periodic commits may also be attempting to avoid the scenario in which a long-running process uses up all its rollback resources and fails with "ORA-01562 failed to extend rollback segment" or similar, perhaps wasting its batch window. They reason that this can be avoided by committing more often and thus releasing resources back to the system. In addition to creating additional overhead and risking ORA-01555 as mentioned above (because those rollback segments we just handed back are actually still needed), often the process isn't designed to restart cleanly and has to be rerun from the start anyway.

I've also heard it argued that commits are necessary in order to log progress for monitoring purposes, although you could achieve this with either an autonomous transaction or V$SESSION_LONGOPS and DBMS_APPLICATION_INFO. Committing every n records is a bad design based on a lack of understanding of how the database works or what tools are available. The system should be configured with sufficient rollback resources to complete its routine tasks without elaborate workarounds that don't work anyway. If you run out, ask the DBA to add a couple more gigabytes to the undo tablespace. Received on Sat Jul 02 2005 - 03:45:57 CDT