Re: How to force Oracle generate a new execution plan

Hello,

I will suggest to development the use of a temporary table in order to get the same plan for all executions.
For my scenary it seems to be the best way.

Thanks for answers.

Regards
Eriovaldo

2016-11-11 6:44 GMT-02:00 Mohamed Houri <mohamed.houri_at_gmail.com>:

> Randolf,
>
> Yes you're right. And I have been very often confronted to such a
> situation in the last couple of years.
>
> I have optimized an overnight query with a varying IN-LIST and fixed a SQL
> Profile over it. A couple of weeks later
> there was a performance issue within the same job but using a different
> sql_id. I have recognized that big query and asked
> the end user whether it is the same query as the one I have SQL Profiled
> the last couple of weeks; and the answer was yes with an addition of a
> couple of few new clients in the varying IN-LIST.
>
> I have then transferred the plan of the SQL Profiled query to the new
> sql_id and the performance was good again.
>
> I have asked, at the same time, the development team to replace the
> IN-LIST with a select from a dedicated table so that the sql_id will not
> change and the SQL Profile will be used.
>
> Best regards
> Mohamed Houri
>
> 2016-11-11 9:28 GMT+01:00 Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>:
>
>>
>> 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
>>
>>
>>
>
>
> --
>
> Houri Mohamed
>
> Oracle DBA-Developer-Performance & Tuning
>
> Member of Oraworld-team <http://www.oraworld-team.com/>
>
> Visit My - Blog <http://www.hourim.wordpress.com/>
>
> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
> Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
>
> My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
> <https://twitter.com/MohamedHouri>
>
>
>

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