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


--
http://www.freelists.org/webpage/oracle-l