Ah. Several days late and you’ve already sorted this by avoiding adaptive
(please buy a bigger machine automation) tuning.
Anyway, you may find Gordian solution pattern #42 useful at some future point
when you don’t have time to beat your head against the CBO (concrete barrier to
optimization) wall. Sigh. It’s really a good CBO, sometimes it just doesn’t
seem like that, especially if someone makes it difficult to get 10053 traces of
the alternatives. Is there an event yet to trace only if an adaptive plan is
attempted?
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Mark W. Farnham
Sent: Monday, April 30, 2018 5:12 PM
To: gogala.mladen@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Tuning Advice
Those things should help you beat it into shape. However, if it does NOT work
out, you noted that you get a good plan for CTAS. If you CTAS a global (or
private if on high enough release level) temporary table does the good plan
hold? Now that is an annoying extra step, but if this is trouble to get
reliably fixed and always goes well as a CTAS, *possibly* your least
(engineering) cost solution is slap it in GTT and then copy the entire contents
to the real destination.
Gordian solution pattern #42.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Mladen Gogala
Sent: Monday, April 30, 2018 1:41 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Tuning Advice
Hi Jeremy,
Bad plan is probably caused by some statistics anomaly. I would try two things:
1. Trace the session and figure out what is the session waiting for. Then
try eliminating the wait.
2. Use /*+ GATHER_PLAN_STATISTICS */ hint and see if there is a difference
between E-rows and A-rows. If there is, see where does the difference come from.
Also, make sure that silly things like OPTIMIZER_ADAPTIVE_FEATURES are turned
off.
Regards
On 04/27/2018 11:50 AM, Sheehan, Jeremy wrote:
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
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
