Oracle 11g R2 – Implementing Asynchronous Batch Commits

oracleoracle-11g-r2

We have an Oracle Database data warehouse that averages 120 commits per second (according to AWR). The top wait activity is log file sync, which is to be expected… For now, overall response times are satisfactory, however, we are concerned with scalability, going forward.

Unfortunately, due to the nature of the vast majority of these transactions, the usual fix of "change your application to commit less often" isn't an option.

We are giving serious thought to changing these commits over to asynchronous batch commits via: COMMIT WRITE BATCH NOWAIT;

We are certainly aware that this would be breaking the "D" in ACID, namely that committed transactions could be lost in the event of an instance crash. However, most of the aforementioned commits is logging information where if we lose a transaction here and there from an instance crash, the consequences of those lost transactions is very minimal.

I understand that with batch nowait commits, the commits are buffered in the log buffer (which we can tune via the log_buffer init parameter, if necessary). However, i'm unable to find answers to the following questions:

1) Do the batch commits span multiple connections? In other words, if we:

 open a connection
 do work 
 commit write batch nowait;
 close the connection
 open another connection
 do work
 commit write batch nowait;
 close the connection 
 rinse, repeat...

Are both of those commits batched or does Oracle flush the commits to disk for each connection when the connection is closed?

2) Is there anyway to find out how often the batched commits are written to disk and become "durable"?

3) Are the batched commits written to disk serialized? In other words: if the application batch commits "A" work, then batch commits "B" work, is "A" work always written to disk before "B" work or is it possible for "B" work to be written to disk before "A" work is written to disk, instance crashes, "A" work is lost but "B" work is durable?

Best Answer

I went ahead and opened an Oracle SR, and this is what Oracle had to say:

1) Do the batch commits span multiple connections?

No. Oracle will write data from Buffer to redologs once sufficient redo is generated by the session. Since your session is closed Oracle will write it immediately.

2) Is there anyway to find out how often the batched commits are written to disk and become "durable"?

No. I have checked all oracle Documents and Metalink but that information is not available. The most advanced documentation about Asynchronous Commits is available from this link.

3) Are the batched commits written to disk serialized?

From what I understood from the documentation, they are serialized. Since transactions are written to Disk when "sufficient" redo is generated, I expect A will be written before B. Worst case they will be written at the same time. Please note that this not true with Multiple sessions like "A" from Session 1 and "B" from Session 2.

Related Question