Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Asynchronous commit - wait or no wait 10GR2

Re: Asynchronous commit - wait or no wait 10GR2

From: Nigel Thomas <>
Date: Thu, 4 Oct 2007 09:50:59 -0700 (PDT)
Message-ID: <>

The thinking behind PL/SQL's long standing behaviour is that there is no real benefit in upgrading from CWINW to CWIW because a) if the system goes down after a commit but before the commit is written, the database will still be restored in a consistent state (just a commit or two behind what your batch pl/sql might have expected b) if the process itself is lost, there is no effective difference NOTE that when you commit within a batch you need to handle the possibility of restart - REGARDLESS of whether you WAIT or NOWAIT. You pay a (small) restart penalty for NW as you are likely to have to reprocess an extra slice or two of your data. The size of the penalty depends on the size of the slice (the bigger the slice, the statistically less likely it is that your failure will occur in the short period between CWINW and the physical write). Obviously the fly in the ointment would be if you communicated (eg how far you had got) with another system via a mechanism outside the Oracle transaction: LOOP PROCESS_A_SLICE; CWINW; SEND_NON_TRANSACTIONAL_MESSAGE; END LOOP; For example, you could successfully send a message to a file, unix pipe, or DBMS_PIPE to tell another process to get on with downstream processing of invoice 101, but then the database fails before the write completes and so is restored back to invoice 100; you then find yourself sending the same message again when you restart your batch and/or the downstream process could find that invoice 101 was unexpectedly missing. In those cases, you would have to consider downstream duplicate detection etc. Anyone want to consider what effects might be on processing messages via (a) AQ and (b) other transactional message services? Any cases where messages could be duplicated? Regards Nigel ----- Original Message ---- From: Job Miller <> To:; Sent: Wednesday, October 3, 2007 1:23:57 PM Subject: Re: Asynchronous commit - wait or no wait 10GR2 PL/SQL has always done this "optimization" automatically. so the default within a pl/sql procedure is to commit upon return to the caller. the cwiw setting must override the default pl/sql optimization. Job John Hallas <> wrote: 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 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. Be a better Heartthrob. Get better relationship answers from someone who knows. Yahoo! Answers - Check it out.

Received on Thu Oct 04 2007 - 11:50:59 CDT

Original text of this message