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.