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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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: