Oracle EXECUTE IMMEDIATE 更改解释查询计划

Question

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

例子： 快：

begin
   package.procedure;
end;
/

慢：

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

查询：

  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)

良好的解释计划：

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

错误的解释计划：

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

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

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

Answer 1

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

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

Answer 2

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

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

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

更极端的步骤是 10053 迹线。 Jonathan Lewis 的网站上发布了一位查看者。这可以让您深入优化的内部，尝试找出不同计划的原因。

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

Answer 3

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

立即执行会产生错误的查询计划 [ID 398605.1]

Modified 09-NOV-2006     Type PROBLEM     Status MODERATED

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

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

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

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

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

Bug 2906307 已作为 Bug 3182582 SQL 语句在 DBMS_JOB 中运行速度比 SQL*PLUS 中运行慢的重复项而被关闭。
中修复

它已在 10.2 解决方案
对于插入语句，请使用提示 BYPASS_RECURSIVE_CHECK：
INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO 表

参考
BUG：2871645 - 对于递归 SQL > 不会发生复杂视图合并深度1
BUG：3182582 - DBMS_JOB 中的 SQL 语句运行速度比 SQL*PLUS 中慢

Answer 4

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

立即执行会产生错误的查询计划 [ID 398605.1]

已修改 2006 年 11 月 9 日 类型问题状态 已审核

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

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

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

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

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

Bug 2906307 已作为 Bug 3182582 SQL 语句在 DBMS_JOB 中运行速度比 SQL*PLUS 中运行慢的重复项而被关闭。它在 10.2 解决方案中已修复

对于插入语句，请使用提示 BYPASS_RECURSIVE_CHECK: INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO 表

引用 BUG:2871645 - 对于 RECURSIVE SQL > 不会发生复杂视图合并深度 1 BUG：3182582 - DBMS_JOB 中的 SQL 语句运行速度比 SQL*PLUS 中慢