Re: commit_write= BATCH, NOWAIT; ... how long do I wait?


"Pat" <pat.casey@xxxxxxxxxxxxxxx> wrote in message
news:b21e89a6-7fb0-4206-a45f-43b242ebd361@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.


I don't think there's any documentation that states explicitly anything
like:
"if you set commit_write to 'batch, nowait' then your change will remain
unsecured for N seconds"'.

However, there is documentation that states that the log writer (LGWR)
wakes up approximately every three seconds or when there is 1MB of
log in the buffer even if nothing else kicks it, : so you can probably
assume
that the most you can lose is about 1MB or 3 seconds, whichever is larger.

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


.

Relevant Pages

  • Re: commit_write= BATCH, NOWAIT; ... how long do I wait?
    ... we've started configuring the database servers with: ... minutes or so worth of transactions, so we're not deeply concerned by ... the durability loss associated with running in asynchronous commit ... Does anybody know how long Oracle will buffer redo in memory before it ...
    (comp.databases.oracle.server)
  • commit_write= BATCH, NOWAIT; ... how long do I wait?
    ... we've started configuring the database servers with: ... we can probably afford the loss of 5 ... the durability loss associated with running in asynchronous commit ... Does anybody know how long Oracle will buffer redo in memory before it ...
    (comp.databases.oracle.server)
  • Re: [PHP] A no brainer...
    ... that once you commit, the data is there no matter what. ... Unless session storage is used to save time in retrieving data, ... I think it would be kind of fun to run some actual tests. ... it makes no sense for a database to be faster than ...
    (php.general)
  • Re: using sqlite3 - execute vs. executemany; committing ...
    ... and also the proper commiting the transactions and closing the connection. ... If the database supports transactions then cursors automatically use ... I suggest that you use the standard cursor methods instead, ... always use .commit() and .close. ...
    (comp.lang.python)
  • Re: A lazy-committing database object with curry?
    ... > I'm writing a database helper class that represents records in a SQL ... > commit the changes all at once. ... transactionally, and the transaction fails, your in memory objects will ...
    (comp.lang.python)