3

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?

  • 1
    Are your log files on a sensible device? (IE: striped, separate from data, NOT RAID5) – Philᵀᴹ Mar 16 '16 at 17:11
  • @phil Yes they are. We have 4 Redo groups with 2 members each that are on dedicated logically separate Raid 10 drives (2 drives, one for each member of the groupset) however, they are on the same SAN as our data drives, The redo is appropriately sized so that we are at a consistent 2 log switches per hour. I could always add an additional Redo group if that will help alleviate log file sync waits... We have looked into moving our Redo to SSD, but I've read conflicting information on how much that will actually reduce our log file sync wait times.... – Kris Johnston Mar 16 '16 at 18:31
  • I would add to the sensible criteria - dedicated. Log writes are latency sensitive. I can understand why there is conflicting information regarding SSD's; most people who benefit from putting their redo logs on SSDs do so because they now have a (set of) dedicated devices. It's highly likely that they would have seen the same benefit by having dedicated "spinning rust" :) – BobC Feb 24 '17 at 1:55
2

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.