Commit Enhancements in Oracle 10g Database Release 2

In Oracle 10g Release 2 the COMMIT command has been enhanced with the WRITE clause to give a degree of control over the way redo information is written to the redo logs during the commit operation. The action associated with the regular COMMIT command can be altered using the COMMIT_WRITE parameter.

Background
COMMIT Command
COMMIT_WRITE Parameter
11g Updates (COMMIT_WAIT, COMMIT_LOGGING Parameters)

Background

Altering the way the COMMIT command handles redo can improve performance, but it should only be used for processes that meet the following criteria.

They result in large numbers of transactions that require redo log writes.
Data loss can be tolerated in the event of an instance crash during the process.
Waiting for redo log writes is a significant part of the waits associated with the process.

COMMIT Command

The available options for the COMMIT command and the WRITE clause are displayed below.

COMMIT;
COMMIT WRITE WAIT;
COMMIT WRITE NOWAIT;
COMMIT WRITE BATCH;
COMMIT WRITE IMMEDIATE;

The meanings of the WRITE clause values are listed below.

IMMEDIATE - The commit "prods" the LGWR process by sending a message, so that the redo is written immediately to the redo logs.
BATCH - The writes to the redo logs are buffered.
WAIT - The commit command is synchronous. It doesn't return until the relevant redo information is written to the online redo log.
NOWAIT - The commit command is asynchronous. It can return before the relevant redo information is written to the online redo log.

The following code shows the enhanced commit processing in action. First define a test table for the code to populate.

CREATE TABLE commit_test (
  id           NUMBER(10),
  description  VARCHAR2(50),
  CONSTRAINT commit_test_pk PRIMARY KEY (id)
);

Next we see the variations of the WRITE clause in action. The code truncates the table and measures the time taken to populate it with a commit for each insert. This process is repeated for each variant of the WRITE clause. All the times are measured in hundredths of a second.

SET SERVEROUTPUT ON
DECLARE
  PROCEDURE do_loop (p_type  IN  VARCHAR2) AS
    l_start  NUMBER;
    l_loops  NUMBER := 1000;
  BEGIN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE commit_test';

    l_start := DBMS_UTILITY.get_time;
    FOR i IN 1 .. l_loops LOOP
      INSERT INTO commit_test (id, description)
      VALUES (i, 'Description for ' || i);
      
      CASE p_type
        WHEN 'WAIT'             THEN COMMIT WRITE WAIT;
        WHEN 'NOWAIT'           THEN COMMIT WRITE NOWAIT;
        WHEN 'BATCH'            THEN COMMIT WRITE BATCH;
        WHEN 'IMMEDIATE'        THEN COMMIT WRITE IMMEDIATE;
        WHEN 'BATCH,WAIT'       THEN COMMIT WRITE BATCH WAIT;
        WHEN 'BATCH,NOWAIT'     THEN COMMIT WRITE BATCH NOWAIT;
        WHEN 'IMMEDIATE,WAIT'   THEN COMMIT WRITE IMMEDIATE WAIT;
        WHEN 'IMMEDIATE,NOWAIT' THEN COMMIT WRITE IMMEDIATE NOWAIT;
      END CASE;
    END LOOP;
    DBMS_OUTPUT.put_line(RPAD('COMMIT WRITE ' || p_type, 30) || ': ' || (DBMS_UTILITY.get_time - l_start));
  END;
BEGIN
  do_loop('WAIT');
  do_loop('NOWAIT');
  do_loop('BATCH');
  do_loop('IMMEDIATE');
  do_loop('BATCH,WAIT');
  do_loop('BATCH,NOWAIT');
  do_loop('IMMEDIATE,WAIT');
  do_loop('IMMEDIATE,NOWAIT');
END;
/
COMMIT WRITE WAIT	      : 66
COMMIT WRITE NOWAIT	      : 67
COMMIT WRITE BATCH	      : 65
COMMIT WRITE IMMEDIATE	      : 63
COMMIT WRITE BATCH,WAIT       : 76
COMMIT WRITE BATCH,NOWAIT     : 10
COMMIT WRITE IMMEDIATE,WAIT   : 59
COMMIT WRITE IMMEDIATE,NOWAIT : 73

PL/SQL procedure successfully completed.

SQL>

COMMIT_WRITE Parameter

The action associated with the regular COMMIT command is defined by the COMMIT_WRITE parameter, which accepts a comma-separated list of values.

COMMIT_WRITE = '{IMMEDIATE | BATCH},{WAIT |NOWAIT}'

The COMMIT_WRITE parameter can be specified at instance or session level using the ALTER SYSTEM and ALTER SESSION commands respectively.

ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='WAIT';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='NOWAIT';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='IMMEDIATE';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='BATCH';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='BATCH,WAIT';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='BATCH,NOWAIT';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='IMMEDIATE,WAIT';
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='IMMEDIATE,NOWAIT';

The default actions for the COMMIT_WRITE parameter and WRITE clause are the same, although at the time of writing the COMMIT_WRITE documentation incorrectly says they are not, so refer to the COMMIT documentations, which says,

"If you specify neither WAIT nor NOWAIT, then WAIT is the default. If you specify neither IMMEDIATE nor BATCH, then IMMEDIATE is the default."

Next we see the variations of the COMMIT_WRITE parameter in action. This example uses the table defined previously and follows the format of the previous example, but the COMMIT_WRITE parameter is altered for each run and a standard commit is issued.

SET SERVEROUTPUT ON
DECLARE
  PROCEDURE do_loop (p_type  IN  VARCHAR2) AS
    l_start  NUMBER;
    l_loops  NUMBER := 1000;
  BEGIN
    EXECUTE IMMEDIATE 'ALTER SESSION SET COMMIT_WRITE=''' || p_type || '''';
    EXECUTE IMMEDIATE 'TRUNCATE TABLE commit_test';

    l_start := DBMS_UTILITY.get_time;
    FOR i IN 1 .. l_loops LOOP
      INSERT INTO commit_test (id, description)
      VALUES (i, 'Description for ' || i);
      COMMIT;
    END LOOP;
    DBMS_OUTPUT.put_line(RPAD('COMMIT_WRITE=' || p_type, 30) || ': ' || (DBMS_UTILITY.get_time - l_start));
  END;
BEGIN
  do_loop('WAIT');
  do_loop('NOWAIT');
  do_loop('BATCH');
  do_loop('IMMEDIATE');
  do_loop('BATCH,WAIT');
  do_loop('BATCH,NOWAIT');
  do_loop('IMMEDIATE,WAIT');
  do_loop('IMMEDIATE,NOWAIT');
END;
/
COMMIT_WRITE=WAIT	      : 63
COMMIT_WRITE=NOWAIT	      : 37
COMMIT_WRITE=BATCH	      : 12
COMMIT_WRITE=IMMEDIATE	      : 43
COMMIT_WRITE=BATCH,WAIT       : 49
COMMIT_WRITE=BATCH,NOWAIT     : 24
COMMIT_WRITE=IMMEDIATE,WAIT   : 47
COMMIT_WRITE=IMMEDIATE,NOWAIT : 58

PL/SQL procedure successfully completed.

SQL>

11g Updates (COMMIT_WAIT, COMMIT_LOGGING Parameters)

In 11g the COMMIT_WRITE parameter has been deprecated and replaced with the COMMIT_WAIT and COMMIT_LOGGING parameters, allowing the logging and waiting settings to be set independently at the system and session level.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.