如何在 PL/SQL 中内联变量？

Question

情况

对于 Oracle 11.2.0.2.0 中的大量数据的中型查询，我的查询执行计划遇到了一些问题。为了加快速度，我引入了一个范围过滤器，它的作用大致如下：

PROCEDURE DO_STUFF(
    org_from VARCHAR2 := NULL,
    org_to   VARCHAR2 := NULL)

  -- [...]
  JOIN organisations org
    ON (cust.org_id = org.id
   AND ((org_from IS NULL) OR (org_from <= org.no))
   AND ((org_to   IS NULL) OR (org_to   >= org.no)))
  -- [...]

如您所见，我想使用可选范围来限制 organizations 的 JOIN组织编号。客户端代码可以在有（应该很快）或没有（非常慢）限制的情况下调用DO_STUFF。

麻烦的

是，PL/SQL 将为上述 org_from 和 org_to 参数创建绑定变量，这正是我在大多数情况下所期望

  -- [...]
  JOIN organisations org
    ON (cust.org_id = org.id
   AND ((:B1 IS NULL) OR (:B1 <= org.no))
   AND ((:B2 IS NULL) OR (:B2 >= org.no)))
  -- [...]

：

的 在这种情况下，当我内联这些值时，我测量到查询执行计划要好得多，即当 Oracle 执行的查询实际上类似于

  -- [...]
  JOIN organisations org
    ON (cust.org_id = org.id
   AND ((10 IS NULL) OR (10 <= org.no))
   AND ((20 IS NULL) OR (20 >= org.no)))
  -- [...]

“很多”时，我的意思是快 5-10 倍。请注意，该查询很少执行，即每月一次。所以我不需要缓存执行计划。

我的问题

• 如何在 PL/SQL 中内联值？我知道立即执行，但我更愿意PL/SQL 编译我的查询，而不进行字符串连接。

• 我只是测量了巧合发生的事情还是我可以假设内联变量确实更好（在这种情况下）？我之所以问这个问题，是因为我认为绑定变量迫使 Oracle 设计一个通用执行计划，而内联值则允许分析非常具体的列和索引统计信息。所以我可以想象这不仅仅是一个巧合。

• 我错过了什么吗？除了变量内联之外，也许还有一种完全不同的方法来实现查询执行计划的改进（请注意，我也尝试了很多提示，但我不是该领域的专家）？

Answer 1

在您的一篇评论中您说：

“我还检查了各种绑定值。
通过绑定变量我得到一些完整的
表扫描，而使用硬编码
值，该计划看起来好多了。”

有两种路径。如果您传入 NULL 作为参数，那么您将选择所有记录。在这种情况下，全表扫描是检索数据的最有效方法。如果您传入值然后索引读取可能更有效，因为您只选择信息的一小部分，

当您使用绑定变量制定查询时，优化器必须做出决定：是否应该假设大多数情况下您会通过。那么，换个角度看：当您只需要选择记录的子集时进行全表扫描，或者当您需要选择记录的子集时进行索引读取，效率是否更高。需要选择所有记录吗？

似乎优化器已经将全表扫描视为覆盖所有可能性的最低效操作，

而当您对值进行硬编码时，优化器会立即知道10 IS NULL 评估结果为 FALSE，因此它可以权衡使用索引读取来查找所需子集记录的优点。

---

那么，该怎么办呢？正如您所说，此查询每月仅运行一次，我认为只需要对业务流程进行少量更改即可进行单独的查询：一个针对所有组织，一个针对组织的子集。

---

“顺便说一句，删除 :R1 IS NULL 子句
不改变执行计划
太多了，这让我剩下了另一个
OR 条件的一侧，:R1 <=
org.no 其中 NULL 没有意义
无论如何，因为 org.no 不为空”

好吧，问题是你有一对指定范围的绑定变量。根据值的分布，不同的范围可能适合不同的执行计划。也就是说，这个范围（可能）适合索引范围扫描......

WHERE org.id BETWEEN 10 AND 11

而这可能更适合全表扫描......

WHERE org.id BETWEEN 10 AND 1199999

这就是绑定变量查看发挥作用的地方

（取决于分布 。当然是价值观）。

Answer 2

由于查询计划实际上始终不同，这意味着优化器的基数估计由于某种原因而关闭。您能否从查询计划中确认优化器预期使用绑定变量时条件选择性不足？由于您使用的是 11.2，Oracle 应该使用 自适应光标共享，因此它不应该是绑定变量窥视问题（假设您在测试中使用不同的 NO 值多次调用带有绑定变量的版本。

是好计划的基数估计实际上是正确的吗？我知道您说过 NO 列上的统计信息是准确的，但我对您的常规统计收集过程可能不会更新的杂散直方图表示怀疑例如，

您始终可以在查询中使用提示来强制使用特定索引（尽管使用

但是，可以尝试进行一项额外的测试。是将 SQL 99 连接语法替换为 Oracle 的旧语法，即

SELECT <<something>>
  FROM <<some other table>> cust,
       organization org
 WHERE cust.org_id = org.id
   AND (    ((org_from IS NULL) OR (org_from <= org.no)) 
        AND ((org_to   IS NULL) OR (org_to   >= org.no)))

这显然不应该改变任何内容，但 SQL 99 语法存在解析器问题，因此需要检查。

Answer 3

闻起来像绑定窥视< /a>，但我只使用 Oracle 10，所以我不能声称 11 中存在同样的问题。

Answer 4

这看起来很像需要自适应游标共享，并结合 SQLPlan 稳定性。
我认为发生的情况是 capture_sql_plan_baselines 参数为 true。 use_sql_plan_baselines 也是如此。如果这是真的，则会发生以下情况：

1. 第一次启动查询时，它会被解析，并获得一个新计划。
2. 第二次，该计划作为接受的计划存储在 sql_plan_baselines 中。
3. 此查询的所有后续运行都使用此计划，无论绑定变量是什么。

如果自适应游标共享已经处于活动状态，优化器将生成一个新的/更好的计划，将其存储在 sql_plan_baselines 中，但无法使用它，直到有人接受这个较新的计划作为可接受的替代计划。检查dba_sql_plan_baselines并查看您的查询是否包含accepted = 'NO'且verified = null的条目
您可以使用 dbms_spm.evolve 来改进新计划，如果计划的性能至少比没有新计划的性能好 1.5 倍，则自动接受它。

我希望这有帮助。

Answer 5

我将其添加为评论，但也会在这里提供。希望这不是过于简单化，看看详细的回复，我可能会误解确切的问题，但无论如何......

似乎您的组织表有列 no (org.no) 被定义为数字。在您的硬编码示例中，您使用数字进行比较。

JOIN organisations org
    ON (cust.org_id = org.id
   AND ((10 IS NULL) OR (10 <= org.no))
   AND ((20 IS NULL) OR (20 >= org.no)))

在您的过程中，您传入 varchar2：

PROCEDURE DO_STUFF(
    org_from VARCHAR2 := NULL,
    org_to   VARCHAR2 := NULL)

因此要将 varchar2 与数字进行比较，Oracle 将必须进行转换，因此这可能会导致完整扫描。

解决方案：更改过程以传递数字