使用参数的Select查询性能异常缓慢问题排查求助

这种绑定变量和硬编码性能天差地别的问题我碰过好多次了，十有八九是Oracle的**绑定变量窥探（Bind Variable Peeking）**在搞鬼！咱们一步步拆解排查：

1. 先对比执行计划，找到差异根源 #

这是最关键的第一步，必须先看两个版本的执行计划到底哪里不一样：

• 硬编码版本：跑EXPLAIN PLAN FOR <你的硬编码查询>，然后执行SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);拿到执行计划。
• 绑定变量版本：可以用DBMS_SQLTUNE.REPORT_SQL_MONITOR（如果查询正在运行），或者执行完后查V$SQL_PLAN，重点关注是不是索引未被使用、全表扫描替代了索引扫描、连接方式异常变更（比如嵌套循环变哈希连接）。

2. 绑定变量窥探是头号嫌疑 #

Oracle默认会在第一次执行带绑定变量的SQL时，用当时传入的参数值生成执行计划，之后不管传什么参数都复用这个计划。如果第一次用绑定变量时传的参数和你硬编码的5339914数据分布差异极大，就会生成低效计划一直复用：
比如硬编码的5339914是个极小众的值，Oracle选择走索引；但第一次用绑定变量时传了个范围极大的值，Oracle生成了全表扫描的计划，之后哪怕传和硬编码一样的值，也还是用全表扫描，自然慢到离谱。

解决办法： #

方法A：加提示强制重新生成计划

在查询里加/*+ REOPTIMIZE */提示，让Oracle每次执行都根据当前参数重新计算最优计划：

SELECT ... 
FROM ... 
WHERE ... AND ooha.order_number BETWEEN NVL(:p,ooha.order_number) AND NVL(:q,ooha.order_number)
/*+ REOPTIMIZE */;

也可以试试关闭局部绑定变量窥探：/*+ OPT_PARAM('_optimizer_bind_peeking' 'false') */，不过这个是全局参数的局部设置，建议测试后再落地。

方法B：动态SQL（谨慎使用，防注入）

如果提示不管用，可以考虑用PL/SQL写动态SQL，根据参数值拼接SQL，这样Oracle会为不同的参数生成不同的执行计划：

DECLARE
  v_sql VARCHAR2(32767);
  v_p NUMBER := :p;
  v_q NUMBER := :q;
BEGIN
  v_sql := 'SELECT ... FROM ... WHERE ... AND ooha.order_number BETWEEN ' || 
           CASE WHEN v_p IS NULL THEN 'ooha.order_number' ELSE v_p END || ' AND ' || 
           CASE WHEN v_q IS NULL THEN 'ooha.order_number' ELSE v_q END;
  -- 执行动态SQL，可按需添加输出逻辑
  EXECUTE IMMEDIATE v_sql;
END;
/

⚠️ 注意：如果参数来自用户输入，一定要做严格的校验，防止SQL注入风险！

3. 检查统计信息是否过期 #

如果表的统计信息太久没更新，Oracle对数据分布完全没概念，生成的执行计划肯定拉胯。可以手动收集最新的统计信息：

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'Ooha', CASCADE => TRUE);

CASCADE => TRUE会同时收集索引的统计信息，优化效果更彻底。

4. 简化NVL逻辑，让Oracle更容易优化 #

你写的NVL(:p,ooha.order_number)其实可以拆成更清晰的条件，让Oracle更容易识别是否能用到order_number上的索引：
把原来的条件改成：

AND (ooha.order_number >= :p OR :p IS NULL)
AND (ooha.order_number <= :q OR :q IS NULL)

这种写法更直白，Oracle的优化器更容易判断当:p或:q不为空时，直接走索引范围扫描。

5. 确认绑定变量的数据类型匹配 #

一定要确保:p和:q的数据类型和ooha.order_number完全一致！比如order_number是NUMBER类型，参数就不能传VARCHAR，不然会触发隐式转换，直接导致索引失效，全表扫描自然慢到难以接受。

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