Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: To Commit or NOT to Commit

RE: To Commit or NOT to Commit

From: Marina Leonardo, Jose Luis <JLMarina_at_Uni2.es>
Date: Thu, 25 May 2000 18:39:49 +0200
Message-Id: <10508.106722@fatcity.com>


Hi,

For instance an UPDATE clause. Why? Because off the DB system has to store the new and the old information. With INSERTS, only has to save new.

Regards

        Joselu

José Luis Marina
Sistemática Integrada SA
U N I 2 : 91252-12-00 Ext: 27850

> -----Mensaje original-----
> De: VIVEK_SHARMA [SMTP:vivek_sharma_at_inf.com]
> Enviado el: jueves 25 de mayo de 2000 16:41
> Para: Multiple recipients of list ORACLE-L
> Asunto: RE: To Commit or NOT to Commit
>
>
> Jared wrote "INSERT's for instance don't require much undo information."
> Qs. What then requires HIGH undo info ?
>
>
> > -----Original Message-----
> > From: Jared Still [SMTP:jkstill_at_bcbso.com]
> > Sent: Wednesday, May 24, 2000 11:49 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: To Commit or NOT to Commit
> >
> >
> >
> > The frequency of COMMIT depends on transaction size,
> > resources, and the nature of the transaction.
> >
> > INSERT's for instance don't require much undo information.
> >
> > I just created a mirror copy of DBA_OBJECTS, inserted
> > DBA_OBJECTS into the table UNDO_TEST until about 1.2
> > million rows were inserted.
> >
> > This is without a commit. It took < 3 minutes.
> >
> > The rollback space required was 1021 blocks on a
> > 4096 byte db_block_size.
> >
> >
> > This is less than half of a single rollback extent
> > on this particular database.
> >
> > Rolling this back took 2.5 minutes.
> >
> > Creating this table with 500 commits took about
> > the same time. Unfortunately this system is
> > very lightly loaded at this time.
> >
> > I would like to see this done on a more heavily
> > loaded machine for some real world numbers.
> >
> > One thing it does show however is that infrequent
> > commits are not difficult to deal with. All it
> > takes is a little planning.
> >
> > Jared
> >
> >
> >
> > On Wed, 24 May 2000, VIVEK_SHARMA wrote:
> >
> > >
> > > 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
> > > > 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
> > 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: VIVEK_SHARMA
> INET: vivek_sharma_at_inf.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
Received on Thu May 25 2000 - 11:39:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US