函数直接用入参与本地变量的性能差异原因探究

架构老林

2026-8-2

问题原因分析

这个现象本质是Oracle优化器的绑定变量窥视（Bind Variable Peeking）机制导致的执行计划选择异常，结合函数参数的处理逻辑差异，引发了低效执行计划的生成。

1. 绑定变量窥视的核心影响

当函数直接使用输入参数（如par_MINIVALUE）时，Oracle优化器在函数首次执行时会“窥视”这个参数的具体值，基于该值生成执行计划并缓存。如果首次传入的是极端值（比如范围覆盖大部分数据，导致优化器选择全表扫描而非索引扫描），后续无论传入什么参数，都会复用这个错误的执行计划，直接导致性能暴跌。

而你用到的几种解决方法，本质都是打破优化器对输入参数的直接绑定窥视：

将参数赋值给本地变量：本地变量属于函数内部的非绑定变量，优化器会基于表的统计信息生成更通用的执行计划，不再依赖首次的参数值；
给参数加+ 0这类无意义运算：Oracle会将参数视为表达式而非直接绑定变量，绕过绑定变量窥视，触发优化器重新基于统计信息选择合适计划；
调整代码空格：Oracle会判定函数代码已变更，重新解析生成新执行计划，避开之前缓存的错误计划。

2. 结合你的场景验证

你的慢函数直接使用par_MINIVALUE等参数，假设首次执行时传入的参数让优化器选择了TRC表的全表扫描（比如参数范围覆盖大量数据，优化器认为全表扫描更快），但后续测试的参数（如42,42,-10000201）其实适合走索引过滤，却被缓存的错误计划拖累。

而快函数通过本地变量，或慢函数加+0/调整空格后，优化器不再依赖首次参数值，而是根据TRC表的统计信息（比如MAXIVALUE/MINIVALUE的数据分布、charact_refnum的索引情况）生成了最优执行计划（比如走索引快速过滤数据），因此性能恢复正常。

3. 补充说明

这类问题在Oracle的PL/SQL函数中很常见，尤其是当表存在数据倾斜（数据分布不均匀）时，绑定变量窥视更容易引发执行计划选择错误。除了你发现的临时解决方案，也可以通过更新表统计信息（DBMS_STATS）、调整优化器参数（如OPTIMIZER_FEATURES_ENABLE）来从根源优化，但本地变量/表达式的方法是最快捷的临时修复手段。

你的示例代码整理

慢函数（直接用参数）

create or replace function func_slow(par_number int) return int
as
cnt int;
begin
  select count(*) into cnt from MyTable where code > par_number;
  return cnt;
end;

快函数（用本地变量）

create or replace function func_fast(par_number int) return int
as
cnt int;
loc_number int;
begin
  loc_number := par_number;
  select count(*) into cnt from MyTable where code > loc_number;
  return cnt;
end;

完整测试用慢函数

CREATE or replace FUNCTION func_slow_complete_trunc
    (par_MINIVALUE number, par_MAXIVALUE number, par_charact_refnum int) return int
as
cnt int;
begin
        select tlvd_cnt into cnt from (
        select count(distinct TLVD.id) tlvd_cnt from trc, tlvd where trc.charact_refnum = par_charact_refnum and tlvd.tbidavis2_id = trc.tbidavis2_id and tlvd.lvd = 1 and tlvd.lvddate>sysdate
        and TRC.MAXIVALUE >= par_MINIVALUE and  TRC.MINIVALUE <= par_MAXIVALUE
         and exists(select * from "libisatz" where "libisatz".TBIDAVIS2_ID=TLVD.TBIDAVIS2_ID) 
        );
return cnt;
end;

完整测试用快函数

CREATE or replace FUNCTION func_fast_complete_trunc
    (par_MINIVALUE number, par_MAXIVALUE number, par_charact_refnum int) return int
as
cnt int;    
loc_MINIVALUE number; loc_MAXIVALUE number; loc_charact_refnum int;

begin
        loc_MINIVALUE := par_MINIVALUE;   
    loc_MAXIVALUE := par_MAXIVALUE;
        loc_charact_refnum:= par_charact_refnum;

        select tlvd_cnt into cnt from (
        select count(distinct TLVD.id) tlvd_cnt from trc, tlvd where trc.charact_refnum = loc_charact_refnum and tlvd.tbidavis2_id = trc.tbidavis2_id and tlvd.lvd = 1 and tlvd.lvddate>sysdate
        and TRC.MAXIVALUE >= loc_MINIVALUE and  TRC.MINIVALUE <= loc_MAXIVALUE
         and exists(select * from "libisatz" where "libisatz".TBIDAVIS2_ID=TLVD.TBIDAVIS2_ID) 
        );
return cnt;
end;

测试语句

select func_slow_complete_trunc(42,42, -10000201) from onerow;
select func_fast_complete_trunc(42,42, -10000201) from onerow;

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