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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala Sent: Monday, April 30, 2018 1:41 AM
To: oracle-l_at_freelists.org
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

--

http://www.freelists.org/webpage/oracle-l Received on Mon Apr 30 2018 - 23:12:14 CEST