Oracle:Commit Enhancements in Oracle 10g Database Release 2

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. This 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.

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 imemdiately 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 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_WRITEdocumentation 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."

The following code examples show the enhanced commit processing in action. First we define a 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;
      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');
END;
/
COMMIT WRITE WAIT             : 129
COMMIT WRITE NOWAIT           : 86
COMMIT WRITE BATCH            : 128
COMMIT WRITE IMMEDIATE        : 128

PL/SQL procedure successfully completed.

SQL>

Next we see the variations of the COMMIT_WRITE parameter in action. This example follows the format of the previous example, but the COMMIT_WRITEparameter 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             : 141
COMMIT_WRITE=NOWAIT           : 90
COMMIT_WRITE=BATCH            : 78
COMMIT_WRITE=IMMEDIATE        : 94
COMMIT_WRITE=BATCH,WAIT       : 139
COMMIT_WRITE=BATCH,NOWAIT     : 78
COMMIT_WRITE=IMMEDIATE,WAIT   : 133
COMMIT_WRITE=IMMEDIATE,NOWAIT : 87

PL/SQL procedure successfully completed.

SQL>