有没有办法让 Oracle 为每个查询调用重新计算查询计划？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/724516/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a way to make Oracle recalculate a query plan for each query invocation?
提问by Sergey Skoblikov
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?
我有一个参数化查询。根据参数值的不同，最优查询计划会有很大差异。问题在于：Oracle 将第一次查询调用的计划用于后续调用，从而导致性能不佳。我通过动态 SQL 处理它，但这种方式远非优雅。所以问题是：有没有办法告诉 Oracle 必须重新计算查询计划？
采纳答案by Il-Bhima
For Oracle 10g we would choose any table in the query and execute
对于 Oracle 10g，我们将选择查询中的任何表并执行
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 pagefor more information and a sample listing.
这将使引用此表的任何查询的计划无效。当然，您希望选择对其他查询影响最小的表。另请参阅此页面以获取更多信息和示例列表。
回答by Thilo
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).
或者，您可以在 SQL 语句中使用注释“/* THIS IS VALUE BRACKET ONE * /”来分隔它们（或查询分析器提示，如果您觉得自己知道哪些是合适的，例如 /*+ CARDINALITY */ 可能适用这里）。
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.
无论哪种方式，我认为您希望拥有单独的 SQL 语句，以便您可以在 Statspack 和朋友中获得单独的报告，因为看起来您确实想要微调该查询。
回答by Matthew Watson
If you really want to generate a new query plan each time, just put a unique comment in as thilo suggests
如果您真的想每次都生成一个新的查询计划，只需按照 thilo 的建议添加唯一的注释
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.
不过，我非常怀疑这样做的必要性，在尝试解决优化器之前，您应该非常确定您的统计数据没有错。
回答by Yas
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.
如果你不想要这个，那么你可以使用文字而不是绑定（如果你不会有太多版本的相同 sql）。或者您可以删除直方图，删除直方图确保独立于绑定参数值的相同计划将生成。
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.
每次执行都使 sql 无效并不是一个好主意。根据此 sql 的使用频率，它可能会导致新问题，例如硬解析导致的闩锁问题。
回答by Quassnoi
Is there a way to tell Oracle that the query plan must be recalculated?
有没有办法告诉 Oracle 必须重新计算查询计划？
You may create several OUTLINE's for different execution plans and select which one to use using OUTLINE CATEGORIES:
您可以OUTLINE为不同的执行计划创建多个，并选择使用哪个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 */
回答by dpbradley
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) */
/*+ opt_param('_OPTIM_PEEK_USER_BINDS ',FALSE) */
回答by tuinstoel
The OP tells us that he can't change the sql statements. With the use of package dbms_advanced_rewriteit is possible to intercept a SQL statements and to change this SQL statement.
OP 告诉我们他不能更改 sql 语句。使用包dbms_advanced_rewrite可以拦截一条SQL语句并改变这条SQL语句。
