Aychin's Oracle RDBMS Blog

Only for Advanced level Professionals

COMMIT_WRITE


COMMIT_WRITE, it is a new initialization parameter starting from the Oracle 10g database. This parameter controls how redo for transactions commit is written to the redo log files. By default when user issues commit statement LGWR background process is triggered to write redo entries to disk, I/O operation. Only after the all redo information for current transaction will be written to disk, call will be returned to the user with “Commit complete.” message.

Using COMMIT_WRITE parameter we can change this behavior. There is 4 possible values for this parameter, also possible to use combinations of them: IMMEDIATE, BATCH, WAIT and NOWAIT. First two values IMMEDIATE and BATCH controls in which way redo must be written to disk, immediately after commit (force disk I/O) or must be buffered to memory and LGWR will decide itself, when to write it to disk. WAIT and NOWAIT controls when call will be returned to the user, if WAIT specified then control will be returned to the user only after his redo will be on the disk, NOWAIT means that call will be returned to the user immediately, without waiting for redo to be persistent on the disk.

It is possible to use combinations of this values, for example default behavior for commits is IMMEDIATE WAIT, redo must be written to disk immediately and users session will wait until this operation will be done, then call will be returned to user.

If, for example, we will use combination IMMEDIATE NOWAIT then, log writer will be forced to write redo entries to disk immediately, but call will be returned to the user without waiting for I/O  to complete.

If BATCH WAIT combination will be used, then redo entries buffered in memory will be written to disk later, no I/O will be forced, and user session will be waiting until this write operation completes.

If BATCH NOWAIT combination will be used, then redo entries buffered in memory will be written to disk later, no I/O will be forced, but call will be returned to user immediately.

Note that, if WAIT or NOWAIT option doesn’t specified explicitly, WAIT will be considered as default. If IMMEDIATE or BATCH option doesn’t specified explicitly, IMMEDIATE will be considered as default. For example COMMIT_WRITE=BATCH, it will be treated as BATCH WAIT.

If You have some commit intensive application, then You can use BATCH NOWAIT option, it will speedup your application because disk I/Os for each commit will be avoided and session will not spent time for waiting to get control back. It is fastest way to commit data but on the other hand is most unprotected, because if instance crash will have a place when redo is still in memory and not yet written to disk, You will lose this data. The probability of such scenario is very small, we talk about centiseconds, also in loaded production environment commits of other sessions will force to write Your redo records too. Best choice will be using IMMEDIATE NOWAIT. But anyway the probability of loosing data is there!

The difference between BATCH NOWAIT and IMMEDIATE NOWAIT is on impact to the other sessions in the system. Because You specified NOWAIT, there will be no difference for Your session, because You will not wait for LGWR anyway. But IMMEDIATE option will cause intensive I/O and big contention for other sessions trying to commit. Using BATCH option will avoid this contention, because LGWR itself will choose best time to write Your redo to disk.

You can set this parameter on instance level, or on session level. By default commit_write parameter is null, it means IMMEDIATE, WAIT

SQL> show parameter commit_write

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
commit_write                         string

--Lets set it to BATCH,NOWAIT

SQL> alter system set commit_write=BATCH,NOWAIT scope=both;

System altered.

SQL> show parameter commit_write

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
commit_write                         string      BATCH, NOWAIT

--Now all transactions in the system will use BATCH, NOWAIT commit method --To set it back to default, use

SQL> alter system set commit_write=IMMEDIATE,WAIT scope=both;

System altered.

SQL> show parameter commit_write

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
commit_write                         string      IMMEDIATE, WAIT

--We can set this parameter on session level

SQL> alter session set commit_write=BATCH,NOWAIT;

Session altered.

SQL> show parameter commit_write

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
commit_write                         string      BATCH, NOWAIT

--Try from another session

SQL> ho sqlplus /nolog

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> show parameter commit_write

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
commit_write                         string      IMMEDIATE, WAIT

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

SQL> show parameter commit_write

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
commit_write                         string      BATCH, NOWAIT

It also possible to set commit method directly in commit statement, at commit time. For example:

SQL> insert into employees (empid, empname) values (101, 'Scott');

1 row created.

SQL> commit write batch nowait;

Commit complete.

You can use it whenever You want to commit, also in stored procedures.


(c) Aychin Gasimov, 05/2010, Azerbaijan Republic


Advertisements

3 responses to “COMMIT_WRITE

  1. Pingback: Using events with DBMS_SCHEDULER (example of DDL auditing) « Aychin's Oracle RDBMS Blog

  2. Kamran Agayev A. January 24, 2011 at 06:41

    Nice article Aychin. Thanks for sharing.

    In the following article there are results of using different variations of this parameter in 11g
    http://jhdba.wordpress.com/2008/04/03/commit_write-in-11g-the-impact/

    For some transactions, it seems that IMMEDIATE NOWAIT is the best option 🙂 (However, the default value of this parameter in 11g is “IMMEDIATE WAIT”)

    • aychin January 24, 2011 at 08:56

      Salam Aleykum, Kamran! Nice to hear You my friend!

      I checked this link, the results of test is predictable. As I marked with red letters there is no difference in performance of BATCH NOWAIT and IMMEDIATE NOWAIT. But we must use IMMEDIATE NOWAIT option with careful! In busy OLTP systems, it can be very fast for Your particular session, but it can degrade performance of all other sessions in the system.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: