DELETE followed by UPDATE..
At 06:40 AM 5/25/00 -0800, VIVEK_SHARMA wrote:
>
>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 - 21:25:07 CDT
