Oracle EXECUTE IMMEDIATE changes explain plan of query

Question

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.

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.

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

You've used ANSI join syntax which will force the use of the CBO (see 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."