Re: How to force Oracle generate a new execution plan

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 11 Nov 2016 08:28:55 +0000
Message-ID: <LOXP123MB1255942508CF0E1EA01DC918A5BB0_at_LOXP123MB1255.GBRP123.PROD.OUTLOOK.COM>


Randolf,

I had read the question differently, viz: there are always 1000 bind variables of which a limited number are set. Re-reading the original posting, though, I see that your interpretation looks like a better match for the description.

Under my interpretation, the abrupt (and dramatic) switch from fast to slow on the 20/21 value break point would simply be due to the optimizer producing a different plan (the typical indexed access/NLJ vs. full tablescan/HJ type of thing) if if had to optimize for a slightly larger predicted data set - making the day's workload dependent on the size of the first set of values used.

Regards
Jonathan Lewis



From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Randolf Geist <info_at_www.sqltools-plusplus.org> Sent: 11 November 2016 08:13:13
To: ecandrietta_at_gmail.com
Cc: oracle-l_at_freelists.org
Subject: Re: How to force Oracle generate a new execution plan

Hi Eriovaldo,

you've got already answers to your specific questions, however the way I you understand your description these answers won't necessarily help you with your problem.

If your application issues the dynamic query using a variable length IN list of bind variables (so e.g. uses IN (:b1, :b2, :b3) in case of three members, IN (:b1, :b2, :b3, :b4) in case of four members etc.) then you effectively end up with different SQL texts / SQL_IDs for each of these variations - so you actually don't re-use any of the plans potentially already available in the Shared Pool, except for those cases where you issue the statement with the same number of bind variables but different/same bind values.

Hence your problem is very likely related to different execution plans being generated for the different variations / SQL_IDs, but it could also be a optimization / hard parse issue - sometimes with a higher number of bind variables the optimizer might take very long to come up with an execution plan.

So as a first step you need to identify what the problem actually is (bad execution plan, hard parse, something else) by measuring where the time goes e.g. via SQL trace, or maybe via Active Session History if you have an Enterprise Edition plus Diagnostic Pack license, then you can decide what measures you have at your disposal.

Randolf

> 1.) How can I do to force Oracle always generate a new execution plan and
> not reuse the plan that is in cache ?
> 2.) Is there a way to clear a execution plan for a specific sql_id ? (I
> have the sql_id that supose is with the bad execution plan).

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


--
http://www.freelists.org/webpage/oracle-l
Received on Fri Nov 11 2016 - 09:28:55 CET

Original text of this message