DELETE's
--
Chuck Hamilton
QVC Inc.
Enterprise Technical Services
Oracle DBA
VIVEK_SHARMA
<vivek_sharma To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
@inf.com> cc: (bcc: CHUCK HAMILTON/QVC)
Ext: NA Subject: RE: To Commit or NOT to Commit
Sent by:
root_at_fatcity.
com
05/25/00
10:40 AM
Please
respond to
ORACLE-L
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 - 13:06:44 CDT
