Re: Tuning "INSERT as SELECT"(B

From: Jonathan Lewis(B <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 2 Feb 2017 07:32:50 +0000
Message-ID: <MMXP123MB0911F7009F1BED10AA6BAB08A54C0_at_MMXP123MB0911.GBRP123.PROD.OUTLOOK.COM>

Your first step should be to check if you are making a valid comparison. Did you really select and fetch 80 million rows ? or was it the first few, or a count(*) of an inline view, or what ? Did the execution plan for the select stay the same when changed to an insert/select ?

    Remember that you won't get the same plan if the query is distributed, and you may not get the same plan if the optimizer mode (or hint) is first_rows(n). You say you disabled all the indexes - how, and did you disable any unique/pk constraints ?

    If, for example, your test starts with a truncate then it will have made the indexes valid again.

Bottom line: if you really can select 80M rows in 15 minutes then you should be able to insert them in roughly 15 minutes plus the time it takes to maintain indexes.

As Mladen says - once you've done the sanity checks - basic tracing to see where the time goes is the next step, or if you're licensed to use the diagnostic and tuning packs then SQL monitoring to watch the SQL statement actually running. (Don't use the rowsource execution stats option if you can use monitoring as it can add a very large CPU overhead).

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Prem Khanna J <jprem_at_outlook.com> Sent: 02 February 2017 00:48:51
To: oracle-l_at_freelists.org
Subject: Tuning "INSERT as SELECT"

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$B!A(B3 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

Received on Thu Feb 02 2017 - 08:32:50 CET