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
Received on Wed May 24 2000 - 12:03:09 CDT