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

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).
Received on Tue May 23 2000 - 11:54:50 CDT