Re: How to force Oracle generate a new execution plan

Hi,

You can purge a single sql using DBMS_SHARED_POOL.PURGE.

Example below

sqlplus / as sysdba

SQL> select address, hash_value, executions, loads, version_count, invalidations, parse_calls

     from v$sqlarea
     where sql_text = 'select ename from emp where empno=7900';
ADDRESS          HASH_VALUE EXECUTIONS      LOADS VERSION_COUNT

---------------- ---------- ---------- ---------- -------------
------------- -----------
000000007A6CF430 1052545619          1          1             1
     0           1

SQL> exec dbms_shared_pool.purge ('000000007A6CF430,1052545619 ','C');

SQL> select address, hash_value, executions, loads, version_count, invalidations, parse_calls

     from v$sqlarea
     where sql_text = 'select ename from emp where empno=7900';

I am unclear as to why you need to do this but this api has worked for me since 11.2.0.4 without any issues

Ta
Fairlie

https://fairlierego.wordpress.com/

On Fri, Nov 11, 2016 at 9:12 AM, 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
>
>

-- 
Fairlie Rego
Senior Oracle Consultant
http://www.linkedin.com/in/fairlierego
https://fairlierego.wordpress.com/

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