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 theninserts 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 about2～3 hours (Disbaledall the indexes ,added nologgingetc.). The target table ispartitoinedsuch 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-paritionhelp 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
