Is there a way to make Oracle recalculate a query plan for each query invocation?

Question

I have a parameterized query. Depending on parameter values optimal query plan varies significantly. Here is the trouble: Oracle uses the plan from the first query invocation for subsequent invocations resulting in bad performance. I deal with it by dynamic SQL but this way is far from elegant. So the question is: is there a way to tell Oracle that the query plan must be recalculated?

Answer 1

For Oracle 10g we would choose any table in the query and execute

GRANT SELECT ON table1 TO user1;

This would invalidate the plan of any query referencing this table. Of course you would want to choose a table which has minimal impact on other queries. See also this page for more information and a sample listing.

Answer 2

If the query plan really changes significantly on the parameter value, maybe you should not use bind variables for this parameter.

How many different values can that parameter take? If there are only a few, you would end up with a couple of query plans (one for each value), and those would hopefully perform well and can be re-used.

Or you could use comments "/* THIS IS VALUE BRACKET ONE * /" in the SQL statement to separate them (or query analyzer hints, if you feel like you know which ones are appropriate, something like /*+ CARDINALITY */ might apply here).

Either way, I think you want to have separate SQL statements so that you can get separate reporting in Statspack and friends, because it looks like you really want to fine-tune that query.

Answer 3

If you really want to generate a new query plan each time, just put a unique comment in as thilo suggests

select /* SQLID=1234 */ 1 from dual;
select /* SQLID=1235 */ 1 from dual;

These should generate unique plans.

I'd be highly suspicious of the need to do this though, before trying to work around the optimiser, you should be very sure your stats aren't wrong.

Answer 4

One of the things the optimizer uses is histograms on the related columns. If you are using a bind variable and if you have histograms on the related column the plan may change depending on the parameter value. This first plan will stay in the shared pool and will be used for all values.

If you do not want this then you can use literals instead of binds (if you will not have too many versions of the same sql). Or you can remove the histogram, removing the histogram ensures that independent of the bind parameter value the same plan will be generated.

Invalidating the sql for every execution is not a good idea. Depending on how often this sql is used it may cause new problems like latch problems caused by hard parsing.

Answer 5

Is there a way to tell Oracle that the query plan must be recalculated?

You may create several OUTLINE's for different execution plans and select which one to use using OUTLINE CATEGORIES:

CREATE OUTLINE ol_use_nl
FOR
SELECT  *
FROM    mytable1 mt1
JOIN    mytable2 mt2
ON      mt1.id = mt2.id
WHERE   mt1.value BETWEEN :a AND :b
CATEGORY FILTERED;

/* Edit the outline to add USE_NL */

CREATE OUTLINE ol_use_nl
FOR
SELECT  *
FROM    mytable1 mt1
JOIN    mytable2 mt2
ON      mt1.id = mt2.id
WHERE   mt1.value BETWEEN :a AND :b
CATEGORY UNFILTERED;

/* Edit the outline to add USE_HASH */

ALTER SESSION SET USE_STORED_OUTLINES = FILTERED;

SELECT  *
FROM    mytable1 mt1
JOIN    mytable2 mt2
ON      mt1.id = mt2.id
WHERE   mt1.value BETWEEN 1 AND 2

/* This will use NESTED LOOPS */

ALTER SESSION SET USE_STORED_OUTLINES = UNFILTERED;

SELECT  *
FROM    mytable1 mt1
JOIN    mytable2 mt2
ON      mt1.id = mt2.id
WHERE   mt1.value BETWEEN 1 AND 1000000

/* This will use HASH JOIN */

Answer 6

Your problem is due to bind variable peeking - turning it off for the whole database would probably break other things, but you can turn it off for just this query by adding the following hint:

/*+ opt_param('_OPTIM_PEEK_USER_BINDS ',FALSE) */

Answer 7

The OP tells us that he can't change the sql statements. With the use of package dbms_advanced_rewrite it is possible to intercept a SQL statements and to change this SQL statement.