FreeLists / oracle-l / Asynchronous commit - wait or no wait 10GR2

["John Hallas" <john.hallas@xxxxxxxxxx>]

In 10GR2 there is an option to commit immediate nowait which allows the
commit to return before the redo is persistent in the redo log. This is
not the default. The default commit remains as commit write immediate
wait.

 

I am testing Sybase and Oracle inserts and I have noticed an oddity with
in Oracle. Solaris 10G T2000 server 10.2.0.3

 

A simple stored procedure

 

create table loaded1 ( id number, name varchar2(8), last_modified
date);

 

 

create  or replace procedure sp4 ( loops in number)

as

cnt number :=0;

 

begin

while (cnt < loops)

        LOOP

        insert into loaded1 values (cnt,'abcdefgh',sysdate);

        commit write immediate nowait;   XXXXXXXXX

        cnt := cnt+1;

        END LOOP;

commit;

 end;

/

Where line xxxxxxxx can be commit write immediate wait;, commit write
immediate nowait; , commit; The first and last should be identical and
be the Oracle default. My parameter commit_write is not set. I am
truncating the table after each run.

 

Timings

 

CWIW 5000 records 57 secs

CWINW 5000 records 3 seconds

Commit 5000 records 3 seconds

 

These figures are repeatable. I think that the first and longest option
should be the Oracle default according to all the documentation but I am
thinking that in fact the 2nd option (nowait) is the real default.

 

Am I missing something here 

 John

+44 (0)113 223 2274 (direct)

+44 (0)113 297 9797

 




BJSS Limited, 1st Floor Coronet House, Queen Street, Leeds LS1 2TW.
Registered in England with company number 2777575.
http://www.bjss.co.uk