| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: insert in batch loading
What I meant is that checkpoint occurs only when redo log switches. We set
log_checkpoint_interval = 210000 and our OS block size is 512. If I
increase the size of redo log to 500 MB, checkpoint will occur before log
switching. Will the transaction wait until checkpoint complete during
checkpoint? Thanks for your inputs.
David
>From: Mladen Gogala <mladen_at_wangtrading.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Re: insert in batch loading
>Date: Tue, 25 Nov 2003 12:49:28 -0800
>
>What does it mean that redo log size dominates checkpoint frequency?
>It brings to mind cases of Edmund Blackadder and Baldrick or Ace Rimmer
>and Lister & Kryten, both very clear cases of domination. I'm sure
>that J. Lewis, as a Britt, can tell you more about Black Adder and Red
>Dwarf,
>despite the fact that he missed those two subjects in his book.
>Redo logs of 100M are not really large. You, basically, have two choices:
>a) Increase redo logs to decent size (500M)
>b) Disable indexes during load.
>
>The third choice (out of two) would be to use "_disable_logging"
>parameter, recently
>mentioned by Mr. Anjo Kolk. Now, that would be practical, don't you think?
>
>
>
>On 11/25/2003 11:59:26 AM, David Boyd wrote:
> > Janne,
> >
> > Thanks for your reply.
> >
> > We have 6 redo log switchings during inserting a table that has 1
>million
> > records. Our redo log size (100 MB) dominates the checkpoint frequency.
> > The table has two indexes. We don't set them to unusable during
>inserting.
> >
> > David
> >
> >
> > >From: Jan Korecki <Jan.Korecki_at_contactor.se>
> > >Reply-To: ORACLE-L_at_fatcity.com
> > >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > >Subject: Re: insert in batch loading
> > >Date: Tue, 25 Nov 2003 08:19:26 -0800
> > >
> > >David Boyd wrote:
> > >
> > >>Hi All,
> > >>
> > >>We have some batch loading jobs that truncate the tables first, then
> > >>insert into the tables as select from tables through database link.
>Those
> > >>jobs run daily. Every time when those jobs run, they cause "cannot
> > >>allocate new log, Checkpoint not complete". All of tables and their
> > >>indexes are in nologging mode. We have /*+append*/ hint in the insert
> > >>statement. We have 5 redo groups with member of 100 MB. Some tables
>have
> > >>more than 1 million records. I was wondering if any body knows a
>method
> > >>that forces a commit after every 1000 records inserted, which is like
> > >>delete_commit procedure.
> > >>
> > >>David
> > >>
> > >>_________________________________________________________________
> > >>Groove on the latest from the hot new rock groups! Get downloads,
>videos,
> > >>and more here.
>http://special.msn.com/entertainment/wiredformusic.armx
> > >
> > >
> > >Hi!
> > >If you do incremental commits the batch will run slower or not at all
>(ora
> > >-01555).
> > >
> > >Have you checked how much redo your insert statement generates? You
>might
> > >have missed something.
> > >If you have indexes on the table you will have to set them unusable and
> > >alter session set skip_unusable_indexes=true
> > >Rebuild the indexes after the load with nologging.
> > >
> > >Janne!
> > >
> > >
> > >--
> > >Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > >--
> > >Author: Jan Korecki
> > > INET: Jan.Korecki_at_contactor.se
> > >
> > >Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > >San Diego, California -- Mailing list and web hosting services
> > >---------------------------------------------------------------------
> > >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).
> >
> > _________________________________________________________________
> > online games and music with a high-speed Internet connection! Prices
>start
> > at less than $1 a day average. https://broadband.msn.com (Prices may
>vary
> > by service area.)
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: David Boyd
> > INET: davidb158_at_hotmail.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > 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).
> >
>
>Mladen Gogala
>Oracle DBA
>
>
>
>Note:
>This message is for the named person's use only. It may contain
>confidential, proprietary or legally privileged information. No
>confidentiality or privilege is waived or lost by any mistransmission. If
>you receive this message in error, please immediately delete it and all
>copies of it from your system, destroy any hard copies of it and notify the
>sender. You must not, directly or indirectly, use, disclose, distribute,
>print, or copy any part of this message if you are not the intended
>recipient. Wang Trading LLC and any of its subsidiaries each reserve the
>right to monitor all e-mail communications through its networks.
>Any views expressed in this message are those of the individual sender,
>except where the message states otherwise and the sender is authorized to
>state them to be the views of any such entity.
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Mladen Gogala
> INET: mladen_at_wangtrading.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>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).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David Boyd INET: davidb158_at_hotmail.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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 Nov 25 2003 - 15:14:25 CST
 |
 |