Re: insert in batch loading

From: David Boyd <davidb158_at_hotmail.com>
Date: Tue, 25 Nov 2003 08:59:26 -0800
Message-ID: <F001.005D7C7F.20031125085926@fatcity.com>

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).

Received on Tue Nov 25 2003 - 10:59:26 CST