RE: How to force Oracle generate a new execution plan

It may have been mentioned, but I’d ask about the *source* of the values in the large in-list. In numerous instances I’ve seen like this, the values come from another table, where they have queried data and stored those values locally. We’ve all seen those cases where the input to one query is the output of another query, and that data was not used for anything else. So it may be as simply as combining queries. You may still have the need for multiple plans, but, if, for example, you are hanging on parses due to the huge in-list, that problem goes away, and you have a single query making it easier to apply baselines/profiles/patches if needed.  

So, in addition to all the good advice, including *where* the query is spending it’s time, I’d ask the where those values come from. In many cases you find out it came from a query preceding the statement, and in many cases you can determine this by looking at the queries the session has been executing.  

Regards,  

Larry G. Elkins

 <mailto:elkinsl_at_verizon.net> elkinsl_at_verizon.net

214.695.8605  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Niall Litchfield Sent: Friday, November 11, 2016 5:40 AM
To: Eriovaldo Andrietta <ecandrietta_at_gmail.com> Cc: ORACLE-L <oracle-l_at_freelists.org> Subject: Re: How to force Oracle generate a new execution plan  

I think it's highly unlikely that you want to force a new plan on each execution. In fact judging by your description, you most likely have a large number of similar, but textually different, statements already - each of which will have been parsed and had execution plans generated.

I suspect it's much more likely that you'll gain more traction by one of the following approaches.

1. Populating a temporary table and joining to it. I think this has already been mentioned.
2. Using one of the various methods of converting delimited strings to in-lists and then binding an appropriate delimited string in the application. http://www.williamrobertson.net/documents/comma-separated.html shows some examples.
3. Encapsulating data access in a pl/sql API. This is a hard sell, but https://www.youtube.com/watch?v=8jiJDflpw4Y might help a bit.

These would give you one tuning target if necessary rather than a large number.  

On 10 Nov 2016 22:15, "Eriovaldo Andrietta" <ecandrietta_at_gmail.com <mailto: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  

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