Re: How to force Oracle generate a new execution plan

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_at_gmail.com> 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
>

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