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

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

Execute Immediate Gives Bad Query Plan [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.

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

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.

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.

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

Solution For insert statements use hint 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

Answer 2

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

Execute Immediate Gives Bad Query Plan [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.

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

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.

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.

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

Solution For insert statements use hint 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

Answer 3

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

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.

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.

The more extreme step is a 10053 trace. There's a viewer posted 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.

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.

Answer 4

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."