Oracle EXECUTE IMMEDIATE 更改解释查询计划

Question

提问by Gunny

I have a stored procedure that I am calling using EXECUTE IMMEDIATE. The issue that I am facing is that the explain plan is different when I call the procedure directly vs when I use EXECUTE IMMEDIATE to call the procedure. This is causing the execution time to increase 5x. The main difference between the plans is that when I use execute immediate the optimizer isn't unnesting the subquery (I'm using a NOT EXISTS condition). We are using Rule Based Optimizer here at work for most queries but this one has a hint to use an index so the CBO is being used (however, we don't collect stats on tables). We are running Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production.

我有一个使用 EXECUTE IMMEDIATE 调用的存储过程。我面临的问题是，当我直接调用过程与使用 EXECUTE IMMEDIATE 调用过程时，解释计划是不同的。这导致执行时间增加了 5 倍。计划之间的主要区别在于，当我使用立即执行时，优化器不会取消子查询的嵌套（我使用的是 NOT EXISTS 条件）。我们在这里对大多数查询使用基于规则的优化器，但这个优化器提示使用索引，因此正在使用 CBO（但是，我们不收集表的统计信息）。我们正在运行 Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production。

Example: Fast:

示例：快速：

begin
   package.procedure;
end;
/

Slow:

减缓：

begin
   execute immediate 'begin package.' || proc_name || '; end;';
end;
/

Query:

询问：

  SELECT                                               /*+ INDEX(A IDX_A_1) */
        a.store_cd,
           b.itm_cd itm_cd,
           CEIL ( (new_date - a.dt) / 7) week_num,
           SUM (a.qty * b.demand_weighting * b.CONVERT) qty
    FROM            a
                 INNER JOIN
                    b
                 ON (a.itm_cd = b.old_itm_cd)
              INNER JOIN
                 (SELECT   g.store_grp_cd, g.store_cd
                    FROM   g, h
                   WHERE   g.store_grp_cd = h.fdo_cd AND h.fdo_type = '1') d
              ON (a.store_cd = d.store_cd AND b.store_grp_cd = d.store_grp_cd)
           CROSS JOIN
              dow
   WHERE       a.dt BETWEEN dow.new_date - 91 AND dow.new_date - 1
           AND a.sls_wr_cd = 'W'
           AND b.demand_type = 'S'
           AND b.old_itm_cd IS NOT NULL
           AND NOT EXISTS
                 (SELECT
                        NULL
                    FROM   f
                   WHERE   f.store_grp_cd = a.store_cd
                           AND b.old_itm_cd = f.old_itm_cd)
GROUP BY   a.store_cd, b.itm_cd, CEIL ( (dow.new_date - a.dt) / 7)

Good Explain Plan:

好的解释计划：

OPERATION       OPTIONS         OBJECT_NAME     OBJECT_TYPE     ID      PARENT_ID
SELECT STATEMENT                                                0       
SORT            GROUP BY                                        1       0
NESTED LOOPS                                                    2       1
HASH JOIN       ANTI                                            3       2
TABLE ACCESS    BY INDEX ROWID  H                               4       3
NESTED LOOPS                                                    5       4
NESTED LOOPS                                                    6       5
NESTED LOOPS                                                    7       6
TABLE ACCESS    FULL            B                               8       7
TABLE ACCESS    BY INDEX ROWID  A                               9       7
INDEX           RANGE SCAN      IDX_A_1         UNIQUE          10      9
INDEX           UNIQUE SCAN     G               UNIQUE          11      6
INDEX           RANGE SCAN      H_UK            UNIQUE          12      5
TABLE ACCESS    FULL            F                               13      3
TABLE ACCESS    FULL            DOW                             14      2

Bad Explain Plan:

错误解释计划：

OPERATION       OPTIONS         OBJECT_NAME     OBJECT_TYPE     ID      PARENT_ID
SELECT STATEMENT                                                0       
SORT            GROUP BY                                        1       0
NESTED LOOPS                                                    2       1
NESTED LOOPS                                                    3       2
NESTED LOOPS                                                    4       3
NESTED LOOPS                                                    5       4
TABLE ACCESS    FULL            B                               6       5
TABLE ACCESS    BY INDEX ROWID  A                               7       5
INDEX           RANGE SCAN      IDX_A_1         UNIQUE          8       7
TABLE ACCESS    FULL            F                               9       8
INDEX           UNIQUE SCAN     G               UNIQUE          10      4
TABLE ACCESS    BY INDEX ROWID  H                               11      3
INDEX           RANGE SCAN      H_UK            UNIQUE          12      11
TABLE ACCESS    FULL            DOW                             13      2

In the bad explain plan the subquery is not being unnested. I was able to reproduce the bad plan by adding a no_unnest hint to the subquery; however, I couldn't reproduce the good plan using the unnest hint (when running the procedure using execute immediate). Other hints are being considered by the optimizer when using the execute immediate just not the unnest hint.

在错误的解释计划中，子查询没有被取消嵌套。通过向子查询添加 no_unnest 提示，我能够重现错误的计划；但是，我无法使用 unnest 提示（使用立即执行运行程序时）重现好的计划。当使用立即执行而不是 unnest 提示时，优化器正在考虑其他提示。

This issue only occurs when I use execute immediate to call the procedure. If I use execute immediate on the query itself it uses the good plan.

仅当我使用立即执行来调用过程时才会出现此问题。如果我在查询本身上使用立即执行，它会使用好的计划。

Answer 1

采纳答案by Gunny

It turns out that this is a known bug in Oracle 9i. Below is the text from a bug report.

事实证明，这是 Oracle 9i 中的一个已知错误。以下是错误报告中的文本。

Execute Immediate Gives Bad Query Plan [ID 398605.1]

立即执行给出错误的查询计划 [ID 398605.1]

Modified 09-NOV-2006     Type PROBLEM     Status MODERATED

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

本文档通过 Oracle 支持的快速可见性 (RaV) 流程交付给您，因此未经过独立技术。

Applies to:Oracle Server - Enterprise Edition - Version: 9.2.0.6 This problem can occur on any platform.

适用于：Oracle Server - 企业版 - 版本：9.2.0.6 此问题在任何平台上都可能发生。

SymptomsWhen a procedure is run through execute immediate the plan produced is a different than when procedure is run directly.

症状当一个过程通过立即执行运行时，产生的计划与直接运行过程时不同。

CauseThe cause of this problem has been identified and verified in an unpublished Bug 2906307. It is caused by the fact that SQL statements issued from PLSQL at a recursive depth greater than 1 may get different execution plans to those issued directly from SQL. There are multiple optimizer features affected by this bug (for example _unnest_subquery,_pred_move_around=true) HINTS related to the features may also be ignored.

原因此问题的原因已在未发布的错误 2906307 中确定和验证。这是由于从 PLSQL 以大于 1 的递归深度发出的 SQL 语句可能会获得与直接从 SQL 发出的执行计划不同的执行计划。有多个优化器功能受此错误影响（例如 _unnest_subquery,_pred_move_around=true）与功能相关的提示也可能会被忽略。

This bug covers the same basic issue as Bug 2871645 Complex view merging does not occur for recursive SQL > depth 1 but for features other than complex view merging.

此错误涵盖与错误 2871645 相同的基本问题，对于递归 SQL > 深度 1，但对于复杂视图合并以外的功能，不会发生复杂视图合并。

Bug 2906307 is closed as a duplicate of Bug 3182582 SQL STATEMENT RUN SLOWER IN DBMS_JOB THAN IN SQL*PLUS. It is fixed in 10.2

错误 2906307 作为错误 3182582 SQL STATEMENT RUN SLOWER IN DBMS_JOB THAN IN SQL*PLUS 的副本而关闭。它已在 10.2 中修复

SolutionFor insert statements use hint BYPASS_RECURSIVE_CHECK: INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO table

解决方案对于插入语句使用提示 BYPASS_RECURSIVE_CHECK: INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO table

ReferencesBUG:2871645 - COMPLEX VIEW MERGING DOES NOT OCCUR FOR RECURSIVE SQL > DEPTH 1 BUG:3182582 - SQL STATEMENT RUN SLOWER IN DBMS_JOB THAN IN SQL*PLUS

参考文献BUG：2871645 - 复杂视图合并不会发生在递归 SQL > 深度 1 错误：3182582 - SQL 语句在 DBMS_JOB 中运行比在 SQL*PLUS 中慢

Answer 2

回答by Gary Myers

You've used ANSI join syntax which will force the use of the CBO (see http://jonathanlewis.wordpress.com/2008/03/20/ansi-sql/)

您已经使用了 ANSI 连接语法，这将强制使用 CBO（请参阅http://jonathanlewis.wordpress.com/2008/03/20/ansi-sql/）

"Once you're running cost-based with no statistics, there are all sorts of little things that might be enough to cause unexpected behaviour in execution plan."

“一旦你在没有统计数据的情况下基于成本运行，就会有各种各样的小事情可能足以在执行计划中导致意外行为。”

Answer 3

回答by Gary Myers

There's a few steps you can take. the first is a 10046 trace.

您可以采取几个步骤。第一个是 10046 跟踪。

Ideally I would start a trace on a single session that executes both the 'good' and 'bad' queries. The trace file should contain both queries with a hard parse. I'd be interested in WHY the second has a hard parse as, if it has the same SQL structure and same parsing user, there's not a lot of reason for the second hard parse. The same session should mean there's no oddities from different memory settings etc.

理想情况下，我会在执行“好”和“坏”查询的单个会话上开始跟踪。跟踪文件应包含具有硬解析的两个查询。我会对为什么第二个有硬解析感兴趣，因为如果它具有相同的 SQL 结构和相同的解析用户，那么第二个硬解析就没有太多理由。相同的会话应该意味着没有来自不同内存设置等的奇怪之处。

The SQL doesn't show any use of variables, so there should be no datatype issues. All columns are 'tied' to a table alias, so there seems no scope for confusing variables with columns.

SQL 没有显示任何变量的使用，因此应该没有数据类型问题。所有列都“绑定”到表别名，因此似乎没有将变量与列混淆的范围。

The more extreme step is a 10053 trace. There's a viewerposted on Jonathan Lewis' site. That can allow you to get into the guts of the optimization to try to work out the reason for the differing plans.

更极端的步骤是 10053 跟踪。乔纳森·刘易斯 (Jonathan Lewis) 的网站上发布了一个查看器。这可以让您深入了解优化的内容，以尝试找出不同计划的原因。

In the wider view, 9i is pretty much dead and the RBO is pretty much dead. I'd be seriously evaluating a project to move the app to CBO. There are features that will force the CBO to be used and without stats this manner of problem will keep cropping up.

从更广泛的角度来看，9i 几乎已经死了，而 RBO 几乎已经死了。我会认真评估将应用程序移至 CBO 的项目。有些功能会强制使用 CBO，如果没有统计数据，这种问题会不断出现。

Answer 4

回答by ffaaa

It turns out that this is a known bug in Oracle 9i. Below is the text from a bug report.

事实证明，这是 Oracle 9i 中的一个已知错误。以下是错误报告中的文本。

Execute Immediate Gives Bad Query Plan [ID 398605.1]

立即执行给出错误的查询计划 [ID 398605.1]

Modified 09-NOV-2006 Type PROBLEM Status MODERATED

修改 09-NOV-2006 类型问题状态缓和

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

本文档通过 Oracle 支持的快速可见性 (RaV) 流程交付给您，因此未经过独立技术。

Applies to: Oracle Server - Enterprise Edition - Version: 9.2.0.6 This problem can occur on any platform.

适用于：Oracle Server - 企业版 - 版本：9.2.0.6 此问题在任何平台上都可能发生。

Symptoms When a procedure is run through execute immediate the plan produced is a different than when procedure is run directly.

症状当一个过程通过立即执行运行时，产生的计划与直接运行过程时不同。

Cause The cause of this problem has been identified and verified in an unpublished Bug 2906307. It is caused by the fact that SQL statements issued from PLSQL at a recursive depth greater than 1 may get different execution plans to those issued directly from SQL. There are multiple optimizer features affected by this bug (for example _unnest_subquery,_pred_move_around=true) HINTS related to the features may also be ignored.

原因此问题的原因已在未发布的错误 2906307 中确定和验证。这是由于从 PLSQL 以大于 1 的递归深度发出的 SQL 语句可能会获得与直接从 SQL 发出的执行计划不同的执行计划。有多个优化器功能受此错误影响（例如 _unnest_subquery,_pred_move_around=true）与功能相关的提示也可能会被忽略。

This bug covers the same basic issue as Bug 2871645 Complex view merging does not occur for recursive SQL > depth 1 but for features other than complex view merging.

此错误涵盖与错误 2871645 相同的基本问题，对于递归 SQL > 深度 1，但对于复杂视图合并以外的功能，不会发生复杂视图合并。

Bug 2906307 is closed as a duplicate of Bug 3182582 SQL STATEMENT RUN SLOWER IN DBMS_JOB THAN IN SQL*PLUS. It is fixed in 10.2

错误 2906307 作为错误 3182582 SQL STATEMENT RUN SLOWER IN DBMS_JOB THAN IN SQL*PLUS 的副本而关闭。它已在 10.2 中修复

Solution For insert statements use hint BYPASS_RECURSIVE_CHECK: INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO table

解决方案对于插入语句使用提示 BYPASS_RECURSIVE_CHECK: INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO table

References BUG:2871645 - COMPLEX VIEW MERGING DOES NOT OCCUR FOR RECURSIVE SQL > DEPTH 1 BUG:3182582 - SQL STATEMENT RUN SLOWER IN DBMS_JOB THAN IN SQL*PLUS

参考文献 BUG：2871645 - 复杂视图合并不会发生在递归 SQL > 深度 1 错误：3182582 - SQL 语句在 DBMS_JOB 中运行比在 SQL*PLUS 中慢

Oracle EXECUTE IMMEDIATE 更改解释查询计划

提问by Gunny

采纳答案by Gunny

回答by Gary Myers

回答by Gary Myers

回答by ffaaa

相关推荐

最近更新

标签

Oracle EXECUTE IMMEDIATE 更改解释查询计划

提问by Gunny

采纳答案by Gunny

回答by Gary Myers

回答by Gary Myers

回答by ffaaa

相关推荐

Oracle 警告：执行完成但有警告

在 Oracle 中创建变量

为什么我得到绑定变量“DeliveryDate_Variable”未声明（Oracle 完全新）

oracle UniversalConnectionPoolManagerMBean 已经注册

相关推荐

最近更新

标签