Re: How to force Oracle generate a new execution plan

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 10 Nov 2016 22:27:11 +0000
Message-ID: <LOXP123MB125543C127D0C7916691C45CA5B80_at_LOXP123MB1255.GBRP123.PROD.OUTLOOK.COM>

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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Eriovaldo Andrietta <ecandrietta_at_gmail.com> 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

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

Received on Thu Nov 10 2016 - 23:27:11 CET