Browse oracle-l: This Month's Archive • Main Archive Page • Related posts • Previous by Date • Next by Date

Re: Asynchronous commit - wait or no wait 10GR2

From: Job Miller <jobmiller@xxxxxxxxx>
To: john.hallas@xxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
Date: Wed, 3 Oct 2007 05:23:57 -0700 (PDT)

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 <john.hallas@xxxxxxxxxx> wrote:         
st1\:*{behavior:url(#default#ieooui) }           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
  

 

       
---------------------------------
Be a better Heartthrob. Get better relationship answers from someone who knows.
Yahoo! Answers - Check it out.

References:

Asynchronous commit - wait or no wait 10GR2
From: John Hallas

Re: Asynchronous commit - wait or no wait 10GR2

Other related posts: