To: ecandrietta@gmail.com
Cc: "oracle-l@freelists.org" <oracle-l@freelists.org>
From: Randolf Geist <info@www.sqltools-plusplus.org>
Subject: Re: How to force Oracle generate a new execution plan
Message-ID: <d4117b66-06ea-7e30-23e3-a2222bbedf46@www.sqltools-plusplus.org>
Date: Fri, 11 Nov 2016 09:13:13 +0100
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:45.0) Gecko/20100101
 Thunderbird/45.4.0
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: info@www.sqltools-plusplus.org

Hi Eriovaldo,

you've got already answers to your specific questions, however the way I 
you understand your description these answers won't necessarily help you 
with your problem.

If your application issues the dynamic query using a variable length IN 
list of bind variables (so e.g. uses IN (:b1, :b2, :b3) in case of three 
members, IN (:b1, :b2, :b3, :b4) in case of four members etc.) then you 
effectively end up with different SQL texts / SQL_IDs for each of these 
variations - so you actually don't re-use any of the plans potentially 
already available in the Shared Pool, except for those cases where you 
issue the statement with the same number of bind variables but 
different/same bind values.

Hence your problem is very likely related to different execution plans 
being generated for the different variations / SQL_IDs, but it could 
also be a optimization / hard parse issue - sometimes with a higher 
number of bind variables the optimizer might take very long to come up 
with an execution plan.

So as a first step you need to identify what the problem actually is 
(bad execution plan, hard parse, something else) by measuring where the 
time goes e.g. via SQL trace, or maybe via Active Session History if you 
have an Enterprise Edition plus Diagnostic Pack license, then you can 
decide what measures you have at your disposal.

Randolf

> 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).
--
http://www.freelists.org/webpage/oracle-l