Oracle存储过程使用变量后Delete语句性能骤降问题咨询

核心原因：常量与绑定变量的执行计划差异 #

你原来的Delete语句中，直接内嵌子查询获取最新刷新日期，这个子查询的结果对优化器来说是已知常量。Oracle会基于这个具体值的过滤选择性（比如该日期能过滤掉多少数据）生成最优执行计划：如果过滤后结果集很小，会优先走索引；如果结果集大，可能选择全表扫描。

改成用变量存储日期后，优化器的处理逻辑变了：

• 绑定变量窥探失效或误判：虽然你这个变量是固定值，但Oracle优化器在处理DML语句的绑定变量时，无法像对待常量那样精准评估选择性。它只能依赖表的统计信息平均值来估算过滤后的行数，一旦估算值和实际值偏差极大（比如实际只删几百行，优化器却估算要删几十万行），就会选择错误的执行计划——比如放弃索引走全表扫描，或者用低效的连接方式，直接导致耗时暴增。
• 执行计划重用问题：如果这个存储过程之前用不同的变量值执行过，Oracle可能会重用之前的执行计划，而不是为当前的固定日期重新生成最优计划。

为什么游标用变量没问题？ #

游标查询的执行逻辑和DML语句有区别：

• 游标通常是一次性获取数据，且结果集规模本身不大，即使优化器对变量的选择性估算有偏差，对整体耗时的影响微乎其微；
• 游标的执行计划是独立生成的，不会和Delete语句的执行计划互相干扰，而且游标查询的优化优先级和DML不同，更容易适配变量的场景。

解决办法 #

• 强制优化器按常量处理变量：在Delete语句中添加Hint，比如 /*+ USE_CONST_FOR_BIND(:v_last_refresh_date) */，让优化器把变量当作常量来评估选择性，生成最优执行计划；或者关闭绑定变量窥探：/*+ OPT_PARAM('optimizer_bind_peeking' 'false') */。
• 直接在Delete中调用函数：放弃用变量存储，直接在Delete的谓词里写get_last_refreshed_date()，这样优化器能直接解析函数的返回值（常量），和原来的内嵌子查询逻辑一致，同时也复用了日期获取的逻辑。
• 更新表统计信息：如果表的统计信息过时，优化器无法准确评估数据分布，执行ANALYZE TABLE 目标表名 COMPUTE STATISTICS;或者调用DBMS_STATS.GATHER_TABLE_STATS更新统计信息，帮助优化器做出正确判断。
• 对比执行计划找差异：用EXPLAIN PLAN FOR分别生成原Delete语句和改后Delete语句的执行计划，查看是否存在索引未使用、连接方式变更等问题，针对性调整。

内容的提问来源于stack exchange，提问作者James Harpe