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?