Look at the dbms_shared_pool package (described in
$ORACLE_HOME/rdbms/admibn/dbmspool.sql).
The purge procedure can be used to remove individual items from the shared pool.
Flag => 'C' will eliminate cursors
heaps => 64 will eliminate only the execution plans for the child cursors
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
Sent: 10 November 2016 22:12
To: ORACLE-L
Subject: How to force Oracle generate a new execution plan
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
--
//www.freelists.org/webpage/oracle-l
