Oracle Illustrated

Feb 21 2010   7:04AM GMT

Migrating from 9i to 11g – Commit_write parameter

Lakshmi Venkatesh Profile: Lakshmi Venkatesh

Feature available from Oracle 10g.

COMMIT_WRITE – COMMIT_WRITE = ‘{IMMEDIATE | BATCH},{WAIT |NOWAIT}’
COMMIT_WAIT – COMMIT_WAIT = { NOWAIT | WAIT | FORCE_WAIT }
COMMIT_POINT_STRENGTH Values range 0 to 225
COMMIT_LOGGING = COMMIT_LOGGING = { IMMEDIATE | BATCH }

Examples:

Oracle 11g
COMMIT_WRITE – All to do with writing to REDO logs

commit_write wait;

Doesn’t return unless the redo information is written to the online redo log.

CREATE TABLE TEST_COMMIT
(a number,
b varchar2(100))
/

set timing on
declare
j number := 1;
BEGIN
FOR i IN 1 .. 50000 loop
insert into test_Commit values ( j, ‘COMMIT WRITE WAIT’);
j := j + 1;
end loop;
COMMIT WRITE WAIT;
END;
/

Elapsed: 00:00:25.90

commit_write immediate;

Redo information is written immediate to the logs

truncate table test_commit;

set timing on
declare
j number := 1;
BEGIN
FOR i IN 1 .. 50000 loop
insert into test_Commit values (j, ‘COMMIT WRITE NOWAIT’);
j := j+1;
END LOOP;
COMMIT WRITE NOWAIT;
END;
/

Elapsed: 00:00:08.76

commit_write batch;

Redo information writes are deffered

truncate table test_commit;

set timing on
declare
j number := 1;
BEGIN
FOR i IN 1 .. 50000 loop
insert into test_Commit values (j, ‘COMMIT WRITE BATCH;’);
j := j+1;
END LOOP;
COMMIT WRITE BATCH;
END;
/

Elapsed: 00:00:05.45

commit_write nowait;

returns before the redo information is written to the online redo log

truncate table test_commit;

set timing on
declare
j number := 1;
BEGIN
FOR i IN 1 .. 50000 loop
insert into test_Commit values (j, ‘COMMIT WRITE IMMEDIATE’);
j := j+1;
END LOOP;
COMMIT WRITE IMMEDIATE;
END;
/

Elapsed: 00:00:04.48

It can be set at SESSION / SYSTEM level.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: