From: Prem Khanna J <jprem_at_outlook.com>
Date: Thu, 2 Feb 2017 00:48:51 +0000
Message-ID: <TY1PR01MB0538F2C3A70674B77DBBC319BC4C0_at_TY1PR01MB0538.jpnprd01.prod.outlook.com>


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.


Received on Thu Feb 02 2017 - 01:48:51 CET

Original text of this message