RE: Tuning Advice

From: Mark W. Farnham <>
Date: Mon, 30 Apr 2018 17:20:45 -0400
Message-ID: <02d701d3e0c9$1b251620$516f4260$>

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?  


From: [] On Behalf Of Mark W. Farnham Sent: Monday, April 30, 2018 5:12 PM
To:; 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.  


From: [] On Behalf Of Mladen Gogala Sent: Monday, April 30, 2018 1:41 AM
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.


On 04/27/2018 11:50 AM, Sheehan, Jeremy wrote:

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!  



Mladen Gogala
Database Consultant
Tel: (347) 321-1217

-- Received on Mon Apr 30 2018 - 23:20:45 CEST

Original text of this message