Eriovaldo,
Consider storing your values on a global temporary table and join it.
If for whatever reason that were not possible, please find the SQL_ID and send
me a SQLd360 for it. I would help you then to identify WHY your plan performs
poorly. You do not want to “force” a new plan on every execution.
Carlos
On Nov 10, 2016, at 2:12 PM, Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
wrote:
Hello,
I have a dynamic query that is mounted by a java application.
This query uses IN (:b1, :b2, :b3 ... :b1000)
When the application submit the sql statment using until 20 bind variables,
the return is fast.
When the application submit the sql statment using more than 20 bind
variables, the return is slow, very slow. Here It maybe using a certain bad
execution plan.
The limit of bind variables is 1000.
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).
Is there any hint to do it ?
I cannot use:
alter system flush BUFFER_CACHE;
alter system flush SHARED_POOL;
because I will clear all execution plans of the instance. I would like to
solve only 1 sql_id.
Regards
Eriovaldo
