What do the two plans look like, and how different are the OUTLINE sections -
are there any clues about features being blocked (or forced) in one but not the
other.
Regards
Jonathan Lewis
________________________________________
From: Sheehan, Jeremy <JEREMY.SHEEHAN@xxxxxxx>
Sent: 27 April 2018 19:25
To: christopherdtaylor1994@xxxxxxxxx; Jonathan Lewis
Cc: ORACLE-L
Subject: RE: Tuning Advice
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: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Chris Taylor
Sent: Friday, April 27, 2018 2:20 PM
To: jonathan@xxxxxxxxxxxxxxxxxx
Cc: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
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.
Chris
On Fri, Apr 27, 2018 at 12:54 PM, Jonathan Lewis
<jonathan@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>> 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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on behalf
of Sheehan, Jeremy <JEREMY.SHEEHAN@xxxxxxx<mailto:JEREMY.SHEEHAN@xxxxxxx>>
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
