RE: Tuning Advice

From: Sheehan, Jeremy <>
Date: Fri, 27 Apr 2018 18:25:35 +0000
Message-ID: <>

One time there was 2mm records in the table, but most of the time the table was truncated prior to trying it out. Either way, it runs with the bad execution plan and never finishes.

From: [] On Behalf Of Chris Taylor Sent: Friday, April 27, 2018 2:20 PM
Cc: ORACLE-L <> Subject: Re: Tuning Advice

CAUTION - EXTERNAL EMAIL 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.


On Fri, Apr 27, 2018 at 12:54 PM, Jonathan Lewis <<>> 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 ?

Jonathan Lewis

From:<> <<>> on behalf of Sheehan, Jeremy <<>> Sent: 27 April 2018 16:50
Subject: Tuning Advice

Hello Gurus,

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!


-- Received on Fri Apr 27 2018 - 20:25:35 CEST

Original text of this message