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_IDSELECT STATEMENT                                                0       SORT            GROUP BY                                        1       0NESTED LOOPS                                                    2       1HASH JOIN       ANTI                                            3       2TABLE ACCESS    BY INDEX ROWID  H                               4       3NESTED LOOPS                                                    5       4NESTED LOOPS                                                    6       5NESTED LOOPS                                                    7       6TABLE ACCESS    FULL            B                               8       7TABLE ACCESS    BY INDEX ROWID  A                               9       7INDEX           RANGE SCAN      IDX_A_1         UNIQUE          10      9INDEX           UNIQUE SCAN     G               UNIQUE          11      6INDEX           RANGE SCAN      H_UK            UNIQUE          12      5TABLE ACCESS    FULL            F                               13      3TABLE ACCESS    FULL            DOW                             14      2

糟糕的解释计划:

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

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

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

Answer 1

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

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