Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 23574 invoked from network); 2 Feb 2008 09:15:24 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by static-ip-69-64-49-119.inaddr.intergenia.de with SMTP; 2 Feb 2008 09:15:24 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9E2B57F810B; Sat, 2 Feb 2008 10:15:24 -0500 (EST) 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 17778-02; Sat, 2 Feb 2008 10:15:24 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 125EB7F7E47; Sat, 2 Feb 2008 10:15:24 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Sat, 02 Feb 2008 09:40:20 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id AD4297F3724 for ; Sat, 2 Feb 2008 09:40:20 -0500 (EST) 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 11848-04 for ; Sat, 2 Feb 2008 09:40:20 -0500 (EST) Received: from troll.tpk.net (mail.tpk.NET [216.107.198.11]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 639CB7F3216 for ; Sat, 2 Feb 2008 09:40:19 -0500 (EST) Received: from MWF600XL (216-107-223-2.RightSizing.tpk.static.cust.seg.net [216.107.223.2]) by troll.tpk.net (8.13.6/8.12.11) with ESMTP id m12EeBWB011542; Sat, 2 Feb 2008 09:40:19 -0500 From: "Mark W. Farnham" To: , Cc: References: <200802020817.m128HEmM020069@smtp43.singnet.com.sg> <6e9345580802020121y401aa5ebifecde9f3537896a3@mail.gmail.com> <012601c86584$337c9430$0200a8c0@Primary> <6e9345580802020252s4f0a05eu2df357afb552d3aa@mail.gmail.com> Subject: RE: Usage of COMMIT NOWAIT, BATCH Date: Sat, 2 Feb 2008 09:40:09 -0500 Message-ID: <000f01c865a9$83b7ac30$1100a8c0@rsiz.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0010_01C8657F.9AE1A430" In-Reply-To: <6e9345580802020252s4f0a05eu2df357afb552d3aa@mail.gmail.com> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198 X-archive-position: 5224 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: mwf@rsiz.com Precedence: normal Reply-to: mwf@rsiz.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 ------=_NextPart_000_0010_01C8657F.9AE1A430 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit If you commit rarely, then nowait on commit won't save you much time, and Jonathan's possibility certainly applies. If you commit frequently and that is required by your design, then you may save a significant amount of elapsed time waiting for commits, but you do not have the long running transaction to which JL refers. I've never seen an actual situation where reducing the commit frequency failed to be a better time saver than introducing "nowait." Remember, this is only going to affect the actual commit, so any potential savings is proportional to the number of commits, not the volume of the transactions. In high speed interactive transactions protecting against the uncertainty introduced most likely wipes out any advantage. Now if you're running a benchmark of small frequent transactions and the plan if something goes wrong is to reset to the original conditions of the benchmark and try again, then the elapsed time savings of not waiting for commits might safely accrue to something significant. Regards, mwf _____ From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of LS Cheng Sent: Saturday, February 02, 2008 5:53 AM To: jonathan@jlcomp.demon.co.uk Cc: oracle-l@freelists.org Subject: Re: Usage of COMMIT NOWAIT, BATCH Hi Jonathan I forgot to mention that this is a insert intensive application so 3 seconds would not apply I guess. The insert rate is around 1000 to 2000 per seconds. ------=_NextPart_000_0010_01C8657F.9AE1A430 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

If you commit rarely, then nowait = on commit won’t save you much time, and Jonathan’s possibility certainly applies.

If you commit frequently and that = is required by your design, then you may save a significant amount of = elapsed time waiting for commits, but you do not have the long running transaction to = which JL refers.

 

I’ve never seen an actual = situation where reducing the commit frequency failed to be a better time saver = than introducing “nowait.” Remember, this is only going to affect = the actual commit, so any potential savings is proportional to the number of commits, not the volume of the transactions. In high speed interactive transactions protecting against the uncertainty introduced most likely = wipes out any advantage. Now if you’re running a benchmark of small = frequent transactions and the plan if something goes wrong is to reset to the = original conditions of the benchmark and try again, then the elapsed time savings = of not waiting for commits might safely accrue to something = significant.

 

Regards,

 

mwf

From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] = On Behalf Of LS Cheng
Sent: Saturday, February = 02, 2008 5:53 AM
To: = jonathan@jlcomp.demon.co.uk
Cc: = oracle-l@freelists.org
Subject: Re: Usage of = COMMIT NOWAIT, BATCH

 

Hi Jonathan

 

I forgot to mention that this is a insert intensive application = so 3 seconds would not apply I guess. The insert rate is around 1000 to 2000 = per seconds.

 

<snip>

 

------=_NextPart_000_0010_01C8657F.9AE1A430-- -- http://www.freelists.org/webpage/oracle-l