Oracle how often should i commit

Find out if the session that spends a lot of time on the log file sync event belongs to a batch or OLTP process or if it is a middle-tier Tuxedo, Weblogic, etc. If the session belongs to a batch process, it may be committing each database change inside a loop. Discover the module name and ask the developer to review the code to see if the number of commits can be reduced.

This is an application issue, and the solution is simply to eliminate unnecessary commits and reduce the overall commit frequency. Some application developers have learned that if they commit infrequently, jobs may fail due to rollback segments running out of space, and they get calls in the middle of the night. Those who have been bitten by deadlocks may have been told to commit more frequently.

Naturally, they become commit-happy people. The right thing to do is to properly define what a transaction is and commit at the end of each transaction. A transaction is a unit of work. A unit of work should either succeed or fail in its entirety. The proper place for a commit or rollback is at the end of each unit of work. Do not introduce additional commits for the sake of rollback segments space or deadlocks.

When you create new application schemas…. Blog at WordPress. Home About. Make it count. Search Search. Home ». Start here About. Example 1. Elapsed: The following block updates , rows, serially, committing every rows: Example 1. Best regards Cristiano. I can smell queue here…. If the DB slows down the application, then either: 1 the hardware is insufficient 2 the application is wrongly designed or both, of course.

Cristiano, if you believe that there exist situations when it is necessary to commit more frequently to avoid ORA, please send me a reproducible test case.

Leave a Reply Cancel reply Enter your comment here Fill in your details below or click an icon to log in:. Email required Address never made public. Name required. Follow Following. Oracle Diagnostician Join 1, other followers. Sign me up. Already have a WordPress. Log in now. And if you commit in the middle, you likely have to write quite a bit of code to ensure that your code is fully restartable.

Do you have an AWR or statspack snapshot or a trace file that shows what you're actually waiting on? That would lead me to strongly suspect that you're missing some indexes that are causing your cascaded deletes to do full table scans every time a row is deleted.

Fixing the missing indexes or doing multi-row deletes so that you have to do full table scans less frequently would seem far more likely to improve performance than worrying about when changes are committed.

Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. How often to I commit in Oracle Ask Question. Asked 7 years, 6 months ago. Active 7 years, 6 months ago. Viewed 5k times. Improve this question. Junaid Junaid 1, 16 16 silver badges 25 25 bronze badges.