8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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 neitherWAIT
norNOWAIT
, thenWAIT
is the default. If you specify neitherIMMEDIATE
norBATCH
, thenIMMEDIATE
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...