RE: To Commit or NOT to Commit

What then is the Heuristic of spacing COMMITS ?

> -----Original Message-----
> From: Jared Still [SMTP:jkstill_at_bcbso.com]
> Sent: Tuesday, May 23, 2000 11:28 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: To Commit or NOT to Commit
>
>
> Committing every thousand rows is much to often on millions
> of rows.
>
> This will cause a commit every few seconds, or maybe every
> couple of seconds on a fast system, with all of the inherent
> overhead.
>
> Jared
>
>

	-----Original Message-----
	From:	Steve Adams [SMTP:steve.adams_at_ixora.com.au]
	Sent:	Tuesday, May 23, 2000 4:28 AM

Hi All,

Every commit waits for a log write and 3 scheduling latencies. There may be good reasons for coding procedures to commit intermittently, but you should keep those commits as infrequent as possible.

Regards,

Steve Adams

> On Mon, 22 May 2000, Rajagopal Venkataramany wrote:
>
> > Hi,
> >
> > Normally, in any large size prodtn environment, most of these data
> > extracting/loading process would be automated. So there is no question
> > of manual intervention here. These jobs are expected to get kicked off
> > on their own.
> >
> > I was referring this situation...
> >
> > I am finding it difficult to educate my developers on the cost of a
> > job failing due to resouce issue after running for a long time (say)
> > 3 hrs or so. Most of them beleive that all the resources are at their
> > disposal. Most of the situations they have been proved WRONG as some
> > one or the other has some activities scheduled in the production db
> > especially after office hours.
> >
> > Anyway, in any kind of environment, it is not a good practise to have
> > a deferred commit (say) after millions of records because you never
> > know as to when the job may fail due to any runtime error.
> >
> > To place it safe, frequent commits (say ) at 1000 rows would be ideal.
> >
> > Regards
> > Rajagopal Venkataramany
> >
> > ----Original Message Follows----
> > To: "Rajagopal Venkataramany" <rajagopalvr_at_hotmail.com>
> > CC: ORACLE-L_at_fatcity.com
> > Date: Mon, 22 May 2000 16:12:31 -0500
> >
> > Hi -
> >
> > Well, it's trial and error. Setting commit frequency too low is a
> > performance
> > hit. Setting it higher allows a lone process to fly. It's getting
> everyone
> > to
> > play nicely together that's the problem.
> >
> > At our shop most code is written with commit frequency as a parameter.
> That
> > way
> > if they run a job at a non-scheduled time (which happens very
> frequently,
> > esp
> > today ! ) and they call me and complain about ora-1555, I can say
> 'reduce
> > your
> > commit frequency'.
> >
> > I'm interested in what your response would be to the situation today: A
> ton
> > of
> > processing, writing one 100m arclog a minute. A ton of updates, I'm
> > scurrying
> > to keep the arclog destination less than 90%. Another process keeps
> bombing
> > out
> > with ora-1555. These are both batch-type processes. My response was 1.
> > re-schedule the process that's reading, or 2. reduce the commit
> frequency
> > for
> > the updating process. They should not run simultaneously.
> >
> > I thought I understood this stuff, until it happens again. Your
> > input/thoughts/comments are appreciated.
> >
> > Thanks
> > Lisa
> >
> >
> >
> >
> >
> > "Rajagopal Venkataramany" <rajagopalvr_at_hotmail.com> on 05/22/2000
> 04:06:46
> > PM
> >
> > To: Lisa Koivu/GELCO_at_GELCO, ORACLE-L_at_fatcity.com
> > cc:
> >
> >
> >
> >
> > Hi,
> >
> > How does one know as to whether there are enough activity in the
> > system ? There may be situation where the online users are high
> > and at times during after office hours, there could be lot of
> > batch jobs running and competing for resources.
> >
> > So, I really do not agree to setting the commit frequency
> dynamically.
> >
> > Regards
> > Rajagopal Venkataramany
> >
> >
> >
> > ----Original Message Follows----
> > Reply-To: ORACLE-L_at_fatcity.com
> > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > Date: Mon, 22 May 2000 10:41:50 -0800
> >
> > Doesn't that depend on whether or not another process is trying to read
> the
> > data
> > you are modifying/deleting? If there is no activity, bang away and put
> your
> > commit frequency at ~1000, I've even seen 5000 used here with no
> problems.
> > Then
> > again that's during batch, no users on system.
> > And if there are users on the system, your commit frequency should be
> fairly
> > low...
> >
> > Isn't this correct? Tell me if I'm wrong.
> >
> >
> >
> >
> >
> >
> > "Rajagopal Venkataramany" <rajagopalvr_at_hotmail.com> on 05/22/2000
> 10:35:14
> > AM
> >
> > Please respond to ORACLE-L_at_fatcity.com
> >
> > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > cc: (bcc: Lisa Koivu/GELCO)
> >
> >
> >
> >
> > Hi,
> >
> > I agree with you. But at the same time, I have also seen "Snapshot
> too
> > old" error at times when we use this.
> >
> > Regards
> > Rajagopal Venkataramany
> >
> >
> > ----Original Message Follows----
> > Reply-To: ORACLE-L_at_fatcity.com
> > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > Date: Sun, 21 May 2000 23:44:40 -0800
> >
> > Hi !
> >
> > Frequent Commits are always good for performance..
> >
> > Any one agree ..
> >
> > Gopal
> >
> >
> > --
> > Author: kgopal
> > INET: kgopal_at_mantraonline.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
> > ________________________________________________________________________
> > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
> >
> > --
> > Author: Rajagopal Venkataramany
> > INET: rajagopalvr_at_hotmail.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
> >
> >
> >
> >
> >
> > --
> > Author:
> > INET: Lisa_Koivu_at_gelco.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
> > ________________________________________________________________________
> > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
> >
> >
> >
> >
> >
> >
> > ________________________________________________________________________
> > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
> >
> > --
> > Author: Rajagopal Venkataramany
> > INET: rajagopalvr_at_hotmail.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
>
>
> Jared Still
> Certified Oracle DBA and Part Time Perl Evangelist ;-)
> Regence BlueCross BlueShield of Oregon
> jkstill_at_bcbso.com - Work - preferred address
> jkstill_at_teleport.com - private
>
>
> --
> Author: Jared Still
> INET: jkstill_at_bcbso.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
Received on Wed May 24 2000 - 02:02:26 CDT