If it is reliably fast up to 20 binds, a scalar solution is for you to generate
union alls for each 20 until you have not more bind values to swallow.
(Okay, it might be slightly superscalar, since the plan will likely be re-used
and some of the objects queried in tossing the first set of twenty might
providing slip-streaming for the latter.)
Of course union ALL is only cheaply known to be valid IF no :bn generates
duplicate tuples to any :bm where n and m are members of your set of bind
variables.
If that is not the case, you would have to use regular union. Whether or not
that is a practical solution depends on the number of rows returned and the
full projected lengths of the rows.
For the case where the number of rows and the full projected length is too long
to use regular union (the de-duplication of full length projected rows is OFTEN
the expensive part), then it might be practical for you to canonically define
the column combinations that decide effective uniqueness (Oracle, and any other
general purpose set processing language have to handle the general case, so
they cannot do this for you with respect to the difference between union and
union all even in theory unless very specific constraints are included. I do
not believe any such non-all union optimization has percolated upwards to
implementation, even though many of the column values are mere “ornaments” [my
term] in the overall set theory consideration.)
IF you can decide such column combinations, it may be possible for you to
generate a spine solution of the non-all unions that resolves the row-set
identifiers. (If you do this it is probably worth retrieving this set as rowid
references to each table.) Then you can wrap a select <all the column values
you want to return, including the decorations and leaving out any spine values
you don’t really need> from the (set of 20 bind variables queries returning
just the keys).
Whew. That was a mouthful. If your query stands up to the union-all is okay
requirement, this is trivial. If you need to package up a spiny starfish
solution it requires some care and sometimes it is complicated.
Good luck. (Sorry I didn’t read the rest of the thread and I’m hopping back to
the OP on this. I’ll try to read the whole thread later. By the names on the
respondent list, I’ll bet it is really good reading.)
Also sorry that is this a possible solution to what I think your actual problem
is rather than an answer to your choice of how to solve it.
Good luck!
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Eriovaldo Andrietta
Sent: Thursday, November 10, 2016 5:13 PM
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
