How to force Oracle generate a new execution plan
Date: Thu, 10 Nov 2016 20:12:42 -0200
Message-ID: <CAJdDhaPpMO3PPE_5ncd7UcPOXmCpumLAcqSUgUcvA=gKkFT2HQ_at_mail.gmail.com>
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.
1.) How can I do to force Oracle always generate a new execution plan and
not reuse the plan that is in cache ?
Is there any hint to do it ?
I cannot use:
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.
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).
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-lReceived on Thu Nov 10 2016 - 23:12:42 CET