RE: Usage of COMMIT NOWAIT, BATCH

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of LS Cheng
Sent: Saturday, February 02, 2008 5:53 AM To: jonathan_at_jlcomp.demon.co.uk
Cc: oracle-l_at_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>  

--
http://www.freelists.org/webpage/oracle-l