Re: Tuning "INSERT as SELECT"

hello JP,

     Have you tried "alter session enable parallel dml;"?!

      You can use "force" option too.

[ ]'s
#mufalani

   Desculpe por erros! Este e-mail foi escrito do meu smartphone!

    Sorry for typos! This mail was written from my smartphone!!!

Em 2 de fev de 2017, ?s 01:03, Mladen Gogala <gogala.mladen_at_gmail.com<mailto:gogala.mladen_at_gmail.com>> escreveu:

Prem, please turn on SQL tracing using DBMS_MONITOR or alter session and see what events you are waiting for. Everything else is pure witchcraft. Regards

On 02/01/2017 07:48 PM, Prem Khanna J 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

--

Mladen Gogala
Oracle DBA
Tel: (347) 321-1217

--

http://www.freelists.org/webpage/oracle-l Received on Thu Feb 02 2017 - 04:07:32 CET