Oracle's New Commit Parameters
10G R2's new COMMIT clause provides
options that control how the redo
stream is written from the redo log
buffer to the online redo log files.
As a result, it is really important
that we fully understand these new
parameters because they affect the
way Oracle commits data as we now
know it.
In the past, we knew that a COMMIT
guaranteed that a transaction's data
was safely stored in the database.
After 10G R2, this guarantee will
depend upon the COMMIT statement
options chosen for that particular
transaction. The end result is the
data you think should be in the
database, might not be. Oh boy…
Let's take a look at 10G R2's new
COMMIT WRITE <option> clause:
-
COMMIT WRITE WAIT; - This is the
default option. WAIT tells the
COMMIT to function as it has
done in the past. The
transaction will wait until LGWR
posts a message back stating
that all redo changes were
flushed from the redo log buffer
to the online redo log files on
disk. If you issue a COMMIT with
no other parameters, this is the
option that will be chosen.
-
COMMIT WRITE NOWAIT; - The
transaction will continue
processing without waiting for
LGWR to post a message back
stating that all redo changes
are on disk.
-
COMMIT WRITE BATCH: - Oracle
will batch groups of COMMIT
statements together and flush
them to the redo logs as a
single unit. If you have batch
jobs that commit too frequently,
this is a possible remedy. I
would recommend that you first
ask the developers to change
their code to increase the
amount of work performed between
commits before you implement
this option.
-
COMMIT WRITE IMMEDIATE; -
Notifies Oracle to flush the log
buffer to the online redo logs
immediately.
Oracle provides the dynamic system
initialization parameter
COMMIT_WRITE to conrtrol redo stream
processing at the database level.
Why would Oracle do this? The answer
is performance. Commit processing
has a measurable impact on
transaction performance. One of the
first questions a DBA asks (or
should ask) when tuning a long
running batch job is how many
updates are occurring between
commits. If the answer is "after
every record", a lengthy discussion
on the detrimental impact of
performing too many commits is
justified.
Oracle provides the "log file sync"
wait event to allow administrators
to monitor commit performance. The
wait is measured when Oracle
foreground processes issue a COMMIT
or ROLLBACK statement and are
waiting for LGWR to post a message
back stating that all redo changes
are safely on disk. Oracle
documentation on Metalink provides
the following information to help
reduce the number of log file sync
wait events.
"If there are lots of
short duration transactions
see if it is possible to
BATCH transactions together
so there are fewer distinct
COMMIT operations. Each
commit has to have it
confirmed that the relevant
REDO is on disk. Although
commits can be "piggybacked"
by Oracle reducing the
overall number of commits by
batching transactions can
have a very beneficial
effect."
Notice that Oracle is recommending
that users BATCH transactions
together to reduce the number of
COMMIT statement operations.
