Re: Tuning Advice

From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Fri, 27 Apr 2018 13:19:38 -0500
Message-ID: <CAP79kiSuALL5P4Ou2R+DaK7u4mXx8jDxcbUe=27VVL7Lg_zQ8g_at_mail.gmail.com>



Jonathan touches on another important question.

How much data is in this table you're inserting into AND has the table been having "DELETES" on it? You might could try a simple "INSERT /*+ append */ hint if the problem is looking for free space in the table below the HWM.

Chris

On Fri, Apr 27, 2018 at 12:54 PM, Jonathan Lewis < jonathan_at_jlcomp.demon.co.uk> wrote:

> The commonest resolution of that type of problem is that "select" may be
> running under first_rows(n) optimization which changes to all_rows as soon
> as you change the query to "insert as select"; alternatively the plan for a
> distributed select can do some optimizations that "insert as distributed
> select" can't.
>
> In your case, though, you say a CTAS is just as fast as a select - and in
> both the above CTAS would have the same problem as "insert as select".
>
> This isn't just a case of your insert maintaining indexes while your CTAS
> doesn't have any indexes ?
>
> Regards
> Jonathan Lewis
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Sheehan, Jeremy <JEREMY.SHEEHAN_at_fpl.com>
> Sent: 27 April 2018 16:50
> To: ORACLE-L
> Subject: Tuning Advice
>
> Hello Gurus,
>
> Oracle 12.1.0.2
> AIX 7.1
>
> I have a query that runs fairly well. Takes about 8 minutes to run,
> nothing terrible about it (no huge FTS, joins seem to be in place
> correctly). When I try to do an insert into TABLE as SELECT, it picks a
> completely different execution plan and never finishes. The really odd
> thing is that it works great when using CTAS or initial creation of a MV.
>
> Any suggestion on how I can have the optimizer not use a specific
> execution plan or any session level parameters that would have it use a
> different execution plan?
>
> Thanks in advance!
>
> Jeremy
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Apr 27 2018 - 20:19:38 CEST

Original text of this message