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

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 2

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.