Re: Tuning "INSERT as SELECT"

Did you try:

Alter session enable parallel dml ?

On Wed, 1 Feb 2017 at 22:49 Prem Khanna J <jprem_at_outlook.com> wrote:

> Friends,
>
> I have a question regarding tuning "insert .. as select" statment on 12.1.
> source table (paritioned) has 80million records . The above statment
> selects the records from source table ,does some little
> formatting/massaging and then inserts into target table which again is partitioned
> . Finally , close to 80million records will need to be inserted into the
> target table.
>
> we tuned the SELECT stmt and that alone gets done in 15 mins. we are okay
> with that. But when it works along with "INSERT..as SELECT" , the INSERT
> happens slow. Takes about 2～3 hours (Disbaled all the indexes , added
> nologging etc.). The target table is partitoined such that all these 80M
> recs go into 1 single partition.This is 1 day's data. Every day 80m recs
> will go into other partition (as it is partitioned on date). FYI : the
> machine has 50 CPUs and enough CPU is available for parallel processes.
>
> Tried "insert /*+ append parallel */ hint too . But still takes 2 hrs.
> Checked the explain plan . The final "LOAD as SELECT" line does not fall
> under a "PX co-ordinator" makes me think , INSERT does not happen parllely.
> My question, with just one partition can append happen in parallel ?
> Would sub-parition help and make insert happen in parallel ? Or it does
> not even matter !!!
>
> I am going to try it anyway.But would like hear your expert opinion and
> the best way to do it.
>
> Regards,
> JP
>
>

--
http://www.freelists.org/webpage/oracle-l