У меня есть хранимая процедура, которую я вызываю с помощью EXECUTE IMMEDIATE. Проблема, с которой я сталкиваюсь, заключается в том, что план объяснения отличается, когда я вызываю процедуру напрямую, и когда я использую EXECUTE IMMEDIATE для вызова процедуры. Это приводит к увеличению времени выполнения в 5 раз. Основное различие между планами заключается в том, что когда я использую немедленное выполнение, оптимизатор не отменяет вложенность подзапроса (я использую условие НЕ СУЩЕСТВУЕТ). Мы используем оптимизатор на основе правил здесь на работе для большинства запросов, но у этого есть подсказка для использования индекса, поэтому используется CBO (однако мы не собираем статистику по таблицам). Мы используем Oracle9i Enterprise Edition Release 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_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; однако мне не удалось воспроизвести хороший план с помощью подсказки unnest (при запуске процедуры с немедленным выполнением). Другие подсказки учитываются оптимизатором при использовании немедленного выполнения, а не подсказки unnest.
Эта проблема возникает только в том случае, если я использую команду «Выполнить немедленно» для вызова процедуры. Если я использую выполнить немедленно для самого запроса, он использует хороший план.
