| 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
Janne,
Thanks very much for your wonderful detail suggestion. I'll definitely use it to analyze the redo.
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 13:34:26 -0800
>
>Hi!
>
>I suggest you set up a test where you check the redo.
>
>For example:
>
>--------------------- start test
>
>SQL> select LOG_MODE from v$database;
>
>LOG_MODE
>------------
>ARCHIVELOG
>
>
>SQL> create table append_test as select * from all_objects where 1=2;
>Table created.
>
>SQL> create index append_test_ind on append_test(owner);
>Index created.
>
>SQL> alter table append_test nologging;
>Table altered.
>
>SQL> alter index append_test_ind nologging;
>Index altered.
>
>SQL> analyze table append_test compute statistics for table for all
>indexes for all indexed columns;
>Table analyzed.
>
>SQL> set serveroutput on size 100000
>SQL> set autotrace on STATISTICS
>
>
>SQL> insert /*+ append */ into append_test select * from
>all_objects_at_LOOPBACK;
>35605 rows created.
>
>Statistics
>----------------------------------------------------------
> 1213732 redo size
> 35605 rows processed
>
>SQL> rollback;
>Rollback complete.
>
>SQL> alter index append_test_ind unusable;
>Index altered.
>
>SQL> alter session set skip_unusable_indexes=TRUE;
>Session altered.
>
>SQL> insert /*+ append */ into append_test select * from
>all_objects_at_LOOPBACK;
>35605 rows created.
>
>
>Statistics
>----------------------------------------------------------
> 1172 redo size
> 35605 rows processed
>
>-------------------------------- end test
>
>This works well with a non-unique index. If the index is unique or you have
>primary key/unique constraint you will run into problem because you cannot
>use +append with a unusable unique index.
>
>You have 2 choices.
>1) have the constraints set to deferreble and disble them before load, set
>the unique index to unusable, load, rebuild index nologging, enable
>constraints
>2) if you dont want to have the constraints deferrable-> drop indexed
>before load and create them afterwards with nologging.
>
>
>Even if you do 2) you will save a lot of time and have a lot less redo.
>
>Regards,
>Janne!
>
>
>
>
>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: 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).
-- 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 Wed Nov 26 2003 - 08:59:27 CST
 |
 |