Re: Tuning Advice

From: Mladen Gogala <>
Date: Mon, 30 Apr 2018 01:41:06 -0400
Message-ID: <>

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,
> Oracle
> 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

Received on Mon Apr 30 2018 - 07:41:06 CEST

Original text of this message