我有一个存储过程,我使用EXECUTE IMMEDIATE调用 . 我面临的问题是,当我使用EXECUTE IMMEDIATE调用该过程时直接调用该过程时,解释计划是不同的 . 这导致执行时间增加5倍 . 计划之间的主要区别在于,当我使用execute immediate时,优化器不会取消子查询(我使用NOT EXISTS条件) . 我们在这里使用基于规则的优化器来处理大多数查询,但是这个提示使用索引以便使用CBO(但是,我们不收集表上的统计信息) . 我们正在运行Oracle9i企业版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提示,我能够重现糟糕的计划;但是,我无法使用不必要的提示重现好计划(使用execute immediate运行程序时) . 优化程序在使用execute immediate而不是不必要的提示时正在考虑其他提示 .
仅当我使用execute immediate来调用该过程时才会出现此问题 . 如果我在查询本身使用execute immediate,它会使用好的计划 .
4 回答
你已经使用了ANSI连接语法,它将强制使用CBO(参见http://jonathanlewis.wordpress.com/2008/03/20/ansi-sql/)
“一旦你在没有统计数据的情况下运行基于成本的运行,就会出现各种可能导致执行计划出现意外行为的小事 . ”
您可以采取几个步骤 . 第一个是10046跟踪 .
理想情况下,我会在单个会话上开始跟踪,执行“好”和“坏”查询 . 跟踪文件应包含具有硬解析的两个查询 . 我感兴趣的是为什么第二个有一个硬解析,如果它具有相同的SQL结构和相同的解析用户,那么第二个硬解析没有太多理由 . 相同的会话应该意味着不同的内存设置等没有奇怪之处 .
SQL没有显示任何变量的使用,因此不应该有数据类型问题 . 所有列都与表别名“绑定”,因此似乎没有将变量与列混淆的余地 .
更极端的步骤是10053跟踪 . 在Jonathan Lewis的网站上发布了一个viewer . 这可以让你深入了解优化的内容,试图找出不同计划的原因 .
从更广泛的角度来看,9i已经死了,RBO已经死了 . 我会认真评估一个项目,将应用程序转移到CBO . 有些功能会强制使用CBO,如果没有统计数据,这种问题就会不断出现 .
事实证明,这是Oracle 9i中的一个已知错误 . 以下是错误报告中的文字 .
Execute Immediate Gives Bad Query Plan [ID 398605.1]
本文档是通过Oracle Support的快速可见性(RaV)流程提供给您的,因此未经过独立的技术审核 .
Applies to: Oracle Server - Enterprise Edition - 版本:9.2.0.6任何平台上都可能出现此问题 .
Symptoms 当程序通过执行立即执行时,生成的计划与直接运行程序时不同 .
Cause 已在未发布的错误2906307中识别并验证了此问题的原因 . 这是由于PLSQL以大于1的递归深度发出的SQL语句可能会获得与直接从SQL发出的SQL语句不同的执行计划 . 有多个优化器功能受此错误影响(例如_unnest_subquery,_pred_move_around = true)与功能相关的HINTS也可能被忽略 .
此错误包含与错误2871645相同的基本问题 . 对于递归SQL>深度1,复杂视图合并不会发生,但对于复杂视图合并以外的功能 .
错误2906307作为一个重复的错误3182582 SQL语句运行SLOWER DBMS_JOB比SQL * PLUS . 它固定在10.2
Solution 对于插入语句,使用提示BYPASS_RECURSIVE_CHECK:INSERT / * BYPASS_RECURSIVE_CHECK * / INTO表
References 错误:2871645 - 复杂视图合并不会导致返回SQL>深度1 BUG:3182582 - SQL语句在DBMS_JOB中运行,而不是SQL * PLUS
事实证明,这是Oracle 9i中的一个已知错误 . 以下是错误报告中的文字 .
执行立即提供错误的查询计划[ID 398605.1]
修改了09-NOV-2006类型问题状态调整
本文档是通过Oracle Support的快速可见性(RaV)流程提供给您的,因此未经过独立的技术审核 .
适用于:Oracle Server - Enterprise Edition - 版本:9.2.0.6此问题可能在任何平台上发生 .
症状当执行立即执行过程时,生成的计划与直接运行过程时不同 .
原因已在未发布的情况下识别并验证了此问题的原因错误2906307.这是由于PLSQL以大于1的递归深度发出的SQL语句可能会获得与直接从SQL发出的SQL语句不同的执行计划 . 有多个优化器功能受此错误影响(例如_unnest_subquery,_pred_move_around = true)与功能相关的HINTS也可能被忽略 .
此错误包含与错误2871645相同的基本问题 . 对于递归SQL>深度1,复杂视图合并不会发生,但对于复杂视图合并以外的功能 .
错误2906307作为一个重复的错误3182582 SQL语句运行SLOWER DBMS_JOB比SQL * PLUS . 它固定在10.2
解决方案对于insert语句,使用提示BYPASS_RECURSIVE_CHECK:INSERT / * BYPASS_RECURSIVE_CHECK * / INTO表
参考文献BUG:2871645 - 复杂视图合并不会导致返回SQL>深度1 BUG:3182582 - SQL语句在DBMS_JOB中运行,而不是SQL * PLUS