Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 31241 invoked from network); 3 Oct 2007 08:09:30 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 3 Oct 2007 08:09:29 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 80B7978F1D9; Wed, 3 Oct 2007 09:09:29 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 01134-05; Wed, 3 Oct 2007 09:09:29 -0400 (EDT) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E96DD78F26B; Wed, 3 Oct 2007 09:09:28 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 03 Oct 2007 08:23:59 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BEB9B78F0FC for ; Wed, 3 Oct 2007 08:23:59 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 23683-05 for ; Wed, 3 Oct 2007 08:23:59 -0400 (EDT) Received: from web53912.mail.re2.yahoo.com (web53912.mail.re2.yahoo.com [206.190.38.161]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id C3C1478F151 for ; Wed, 3 Oct 2007 08:23:57 -0400 (EDT) Received: (qmail 74427 invoked by uid 60001); 3 Oct 2007 12:23:57 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=IUYDJ/cHF58pZJi6IPUPei7Dr/8WzPnX1jwUug65lgHtyPnyUYSlti0hf0amhbrxjGVcwIV52KS8QCePkuvz1uHaEkJoslGu1Ll7Rw1NNVGdTmonGp6ooNSG5Ijg8WZWBCkUeE/foDdghaqG6h714PeKraCD7PC4kqS2BsVSihI=; X-YMail-OSG: WWUZbLcVM1kamOa6z5a_iE7RzaY0Fa9rFmcOovTPqTz3TfWj098m7oLMJ_N33Uw1ohP_i5U6EWhJKDKbMsznrsqCWqheNqWJFH.rf6g.dr5FdqsPy4XVJ10.EG8omxNUCgXpT5KkdWVD_vd.Iq6KqtHOCw-- Received: from [71.64.130.251] by web53912.mail.re2.yahoo.com via HTTP; Wed, 03 Oct 2007 05:23:57 PDT Date: Wed, 3 Oct 2007 05:23:57 -0700 (PDT) From: Job Miller Subject: Re: Asynchronous commit - wait or no wait 10GR2 To: john.hallas@bjss.co.uk, oracle-l@freelists.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1252980008-1191414237=:73989" Message-ID: <241629.73989.qm@web53912.mail.re2.yahoo.com> X-archive-position: 2098 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: jobmiller@yahoo.com Precedence: normal Reply-to: jobmiller@yahoo.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain --0-1252980008-1191414237=:73989 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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: 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. --0-1252980008-1191414237=:73989 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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@bjss.co.uk> 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 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. --0-1252980008-1191414237=:73989-- -- http://www.freelists.org/webpage/oracle-l