Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy



Oracle commit parameters

Article by Rampant Author Chris Foot

Oracle's New Commit Parameters
10G R2's new COMMIT clause provides options that control how the redo stream is written from the redo log buffer to the online redo log files. As a result, it is really important that we fully understand these new parameters because they affect the way Oracle commits data as we now know it.

In the past, we knew that a COMMIT guaranteed that a transaction's data was safely stored in the database. After 10G R2, this guarantee will depend upon the COMMIT statement options chosen for that particular transaction. The end result is the data you think should be in the database, might not be. Oh boy…

Let's take a look at 10G R2's new COMMIT WRITE <option> clause:

  • COMMIT WRITE WAIT; - This is the default option. WAIT tells the COMMIT to function as it has done in the past. The transaction will wait until LGWR posts a message back stating that all redo changes were flushed from the redo log buffer to the online redo log files on disk. If you issue a COMMIT with no other parameters, this is the option that will be chosen.

  • COMMIT WRITE NOWAIT; - The transaction will continue processing without waiting for LGWR to post a message back stating that all redo changes are on disk.

  • COMMIT WRITE BATCH: - Oracle will batch groups of COMMIT statements together and flush them to the redo logs as a single unit. If you have batch jobs that commit too frequently, this is a possible remedy. I would recommend that you first ask the developers to change their code to increase the amount of work performed between commits before you implement this option.

  • COMMIT WRITE IMMEDIATE; - Notifies Oracle to flush the log buffer to the online redo logs immediately.

Oracle provides the dynamic system initialization parameter COMMIT_WRITE to conrtrol redo stream processing at the database level.

Why would Oracle do this? The answer is performance. Commit processing has a measurable impact on transaction performance. One of the first questions a DBA asks (or should ask) when tuning a long running batch job is how many updates are occurring between commits. If the answer is "after every record", a lengthy discussion on the detrimental impact of performing too many commits is justified.

Oracle provides the "log file sync" wait event to allow administrators to monitor commit performance. The wait is measured when Oracle foreground processes issue a COMMIT or ROLLBACK statement and are waiting for LGWR to post a message back stating that all redo changes are safely on disk. Oracle documentation on Metalink provides the following information to help reduce the number of log file sync wait events.

"If there are lots of short duration transactions see if it is possible to BATCH transactions together so there are fewer distinct COMMIT operations. Each commit has to have it confirmed that the relevant REDO is on disk. Although commits can be "piggybacked" by Oracle reducing the overall number of commits by batching transactions can have a very beneficial effect."

Notice that Oracle is recommending that users BATCH transactions together to reduce the number of COMMIT statement operations.




 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks