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@xxxxxxxxx>:
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@xxxxxxxxxxxxxxxxxx>:
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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxxxxxx>
Sent: 11 November 2016 08:13:13
To: ecandrietta@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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 planand
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).
//www.freelists.org/webpage/oracle-l
--
//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>
