Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: 10gR2 - direct path load with enabled indexes - lots of undo generated from indexes

Re: 10gR2 - direct path load with enabled indexes - lots of undo generated from indexes

From: Arul Ramachandran <contactarul_at_gmail.com>
Date: Tue, 30 May 2006 13:06:36 -0700
Message-ID: <1c1a62990605301306j228577efmdd2d5fcbef9ae210@mail.gmail.com>


Hi Christo,

What Oracle version?
What is the top wait event?
Are you seeing high "log file sync" and "transaction rollback" in your statspack report?

On 5/30/06, Christo Kutrovsky <kutrovsky.oracle_at_gmail.com> wrote:
>
> Hello All,
>
> We're doing a direct path to an empty table via:
>
> insert /*+ APPEND*/ into TABLE as select * from other_schema.SAME_TABLE;
>
> The table has a primary key and a few extra indexes. The goal is to
> perform the entire operation in nologging mode. The table and all
> indexes are "nologging".
>
> The problem we have is that there's undo (rollback) generated for the
> indexes, which I dont understand. Why is there undo generated for an
> index built, equivalent to the size of all indexes ? Is this normal ?
>
> The process goes as follow
> - start writing into table segment
> - while writing, have 1 sort area for each index on the table, and
> populate it with data been written
> - At this point only 1 undo record is been used
> - after the table load is complete, write up the index segments (from
> sort area/temp tablespace) 1 by 1, creating as much undo as the size
> of the indexes.
>
> Any ideas? It doesn't make sense to use that much undo (rollback) ?
> When you do a normal index build you dont use that much undo.
>
> --
> Christo Kutrovsky
> Senior Database/System Administrator
> The Pythian Group - www.pythian.com
> I blog at http://www.pythian.com/blogs/
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Arul

--
http://www.freelists.org/webpage/oracle-l
Received on Tue May 30 2006 - 15:06:36 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US