commit_write= BATCH, NOWAIT; ... how long do I wait?
Date: Thu, 9 Jul 2009 19:56:44 -0700 (PDT)
Message-ID: <b21e89a6-7fb0-4206-a45f-43b242ebd361_at_y19g2000yqy.googlegroups.com>
We're supporting an application that is generally well behaved, but during certain activities (large imports usually), it tends to do excessive numbers of small commits, which burns out the database's IO subsystem and generally slows the whole rest of the app down. Not to mention the fact that the imports themselves take rather a long time.
We're running Oracle 10.2.0.4 (64 bit) on top of red-hat linux.
Recently, we've started configuring the database servers with:
alter system set commit_write=BATCH, NOWAIT;
In the event of a database/server crash we can afford the loss of a few transactions. Frankly, we can probably afford the loss of 5 minutes or so worth of transactions, so we're not deeply concerned by the durability loss associated with running in asynchronous commit mode.
However, I at least, am more than a little curious as to how much data we're putting at risk here.
Does anybody know how long Oracle will buffer redo in memory before it commit when running in this mode? I'm operating under a theory that it probably commits every time either A) its redo buffer is full or B) the oldest redo entry is older than x, but that's pure speculation on my part.
Is there any guarantee *at all* here that data older than "x" is on disk? I've worked with other databases (mysql/innodb) where there's a guarantee that it'll flush the redo within 1 second of your commit if you run in "weakly" durable mode, but I can't seem to find any Oracle doc that specifies if there is such a commitment. Received on Thu Jul 09 2009 - 21:56:44 CDT