Re: Forall performance

JusungYang_at_yahoo.com (Jusung Yang) wrote in message news:<130ba93a.0302141000.7480eb42_at_posting.google.com>...
> After taking a second look at your SQL, I realized your direct-path
> insert hint is basically useless. You have to use
>
> insert/*+ append */ into .. select ... from ...
>
> for it to work. It does not matter if you turn off the logging, this
> SQL will be logged, and undo info. will be generated. So, in addition
> to the redo log sizing, you have to consider rollback segments as
> well. Though small redo logs is more likely to be the root of your
> problem in this case.
>
> How do you use direct-path insert within forall then? It may not be
> possible with current version of ORACLE. If you use
>
> insert/*+ append */ into .. select array_name(i) from dual;
>
> you get ORA-12838. I have yet to see a solution to this.
>

Regarding the append hint, I only used it to test the impact. Are you saying that it has no impact with the VALUES statent (as opposed to select)?

> > Just a guess. When you are inserting large amount of data, the time
> > saving you get from forall may be covered up by the inefficient redo
> > log managemnet of your database. Even though you use direct load
> > insert, the transaction is still logged - unless you turn off the
> > logging. What are your redo log sizes? Try increasing the size and see
> > what happen.
> >

The redo logs utilized are 3 x 10MB in size. I am going to make some modifications to see the impact.

BTW. I am now getting an even bigger performance gain by going with smaller array size (variable but averaging 22 records per). What was stated earlier as taking 3.2 minutes is now down to an average of 2.7 minutes. Received on Fri Feb 14 2003 - 17:48:51 CST