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必须重新计算查询计划?
7 个解决方案
#1
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 page for more information and a sample listing.
这将使引用此表的任何查询的计划无效。当然,您可能希望选择对其他查询影响最小的表。有关更多信息和示例列表,另请参见此页面。
#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).
或者您可以在SQL语句中使用注释“/ *这是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和朋友中获得单独的报告,因为看起来您真的想要微调该查询。
#3
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.
我非常怀疑是否需要这样做,在尝试解决优化问题之前,你应该非常确定你的统计数据没有错。
#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.
如果您不想这样,那么您可以使用文字而不是绑定(如果您没有相同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的频率,它可能会导致新问题,例如由硬分析引起的锁存问题。
#5
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选择要使用的OUTLINE:
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 */
#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) */
/ * + opt_param('_ OPTIM_PEEK_USER_BINDS',FALSE)* /
#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.
OP告诉我们他无法更改sql语句。通过使用包dbms_advanced_rewrite,可以拦截SQL语句并更改此SQL语句。
#1
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 page for more information and a sample listing.
这将使引用此表的任何查询的计划无效。当然,您可能希望选择对其他查询影响最小的表。有关更多信息和示例列表,另请参见此页面。
#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).
或者您可以在SQL语句中使用注释“/ *这是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和朋友中获得单独的报告,因为看起来您真的想要微调该查询。
#3
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.
我非常怀疑是否需要这样做,在尝试解决优化问题之前,你应该非常确定你的统计数据没有错。
#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.
如果您不想这样,那么您可以使用文字而不是绑定(如果您没有相同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的频率,它可能会导致新问题,例如由硬分析引起的锁存问题。
#5
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选择要使用的OUTLINE:
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 */
#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) */
/ * + opt_param('_ OPTIM_PEEK_USER_BINDS',FALSE)* /
#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.
OP告诉我们他无法更改sql语句。通过使用包dbms_advanced_rewrite,可以拦截SQL语句并更改此SQL语句。