I have a stored procedure that I call using EXECUTE IMMEDIATE. The problem I am facing is that the explanation plan is different when I call the procedure directly and when I use EXECUTE IMMEDIATE to call the procedure. This leads to an increase in 5x runtime. The main difference between the plans is that when I use immediate start, the optimizer does not repel the subquery (I use the NOT EXISTS clause). We use the work-based rule optimizer here to work for most queries, but it has a hint of using an index, so CBO is used (however, we do not collect statistics on the tables). We are working on the release of Oracle9i Enterprise Edition version 9.2.0.4.0 - 64bit.
Example: Fast:
begin
package.procedure;
end;
/
Slow:
begin
execute immediate 'begin package.' || proc_name || '; end;';
end;
/
Query:
SELECT
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 explanatory 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
:
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 ; , unsest ( ). .
, . , .