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).