Oracle 8i日期函数运行缓慢

Question

我正在尝试在Oracle 8i服务器上运行以下PL/SQL代码（比较老旧，我知道）：

select
    -- stuff --
from
    s_doc_quote d,
    s_quote_item i,
    s_contact c,
    s_addr_per a,
    cx_meter_info m
where
    d.row_id = i.sd_id
    and d.con_per_id = c.row_id
    and i.ship_per_addr_id = a.row_id(+)
    and i.x_meter_info_id = m.row_id(+)
    and d.x_move_type in ('Move In','Move Out','Move Out / Move In')
    and i.prod_id in ('1-QH6','1-QH8')
    and d.created between add_months(trunc(sysdate,'MM'), -1) and sysdate
;

然而，执行速度非常缓慢。由于每晚服务器都会在午夜左右关闭，因此经常无法及时完成。

执行计划如下：

SELECT STATEMENT   1179377
 NESTED LOOPS   1179377
  NESTED LOOPS OUTER  959695
   NESTED LOOPS OUTER  740014
    NESTED LOOPS   520332
     INLIST ITERATOR
      TABLE ACCESS BY INDEX ROWID S_QUOTE_ITEM 157132
       INDEX RANGE SCAN S_QUOTE_ITEM_IDX8 8917
     TABLE ACCESS BY INDEX ROWID S_DOC_QUOTE 1
      INDEX UNIQUE SCAN S_DOC_QUOTE_P1 1
    TABLE ACCESS BY INDEX ROWID S_ADDR_PER 1
     INDEX UNIQUE SCAN S_ADDR_PER_P1 1
   TABLE ACCESS BY INDEX ROWID CX_METER_INFO 1
    INDEX UNIQUE SCAN CX_METER_INFO_P1 1
  TABLE ACCESS BY INDEX ROWID S_CONTACT 1
   INDEX UNIQUE SCAN S_CONTACT_P1 1

如果我修改以下的where子句：

and d.created between add_months(trunc(sysdate,'MM'), -1) and sysdate

对于静态值，例如：

and d.created between to_date('20110101','yyyymmdd') and sysdate

执行计划变为：

SELECT STATEMENT   5
 NESTED LOOPS   5
  NESTED LOOPS OUTER  4
   NESTED LOOPS OUTER  3
    NESTED LOOPS   2
     TABLE ACCESS BY INDEX ROWID S_DOC_QUOTE 1
      INDEX RANGE SCAN S_DOC_QUOTE_IDX1 3
     INLIST ITERATOR
      TABLE ACCESS BY INDEX ROWID S_QUOTE_ITEM 1
       INDEX RANGE SCAN S_QUOTE_ITEM_IDX4 4
    TABLE ACCESS BY INDEX ROWID S_ADDR_PER 1
     INDEX UNIQUE SCAN S_ADDR_PER_P1 1
   TABLE ACCESS BY INDEX ROWID CX_METER_INFO 1
    INDEX UNIQUE SCAN CX_METER_INFO_P1 1
  TABLE ACCESS BY INDEX ROWID S_CONTACT 1
   INDEX UNIQUE SCAN S_CONTACT_P1 1

该查询几乎可以立即返回行。

到目前为止，我已尝试使用绑定变量替换动态日期条件，并使用选择来自Dual表的动态日期的子查询。但是这些方法都没有帮助提高性能。

由于我对PL / SQL相对较新，因此无法理解执行计划中存在如此大的差异的原因。

我还尝试将查询作为SAS通过运行，但出于测试执行速度的目的，我一直在使用SQL * Plus。

编辑：

为了澄清，我已经尝试过使用以下绑定变量：

var start_date varchar2(8);
exec :start_date := to_char(add_months(trunc(sysdate,'MM'), -1),'yyyymmdd')

使用以下where子句：

and d.created between to_date(:start_date,'yyyymmdd') and sysdate

该代码返回执行成本为1179377。

如果可能的话，我还想避免使用绑定变量，因为我认为无法从SAS传递查询中引用它们（尽管我可能是错的）。

- taheris

---

这些表的优化器统计信息是否最新？索引和外键等是否已定义。从您的计划中无法确定，但可能是因为它无法计算出输出集的正确基数，然后选择嵌套循环。 - Mike Meyers

@MikeyByCrikey - 查询已设置为使用索引，但不幸的是我没有权限分析数据库/表统计信息。 - taheris

Answer 1

我怀疑这里的问题与ADD_MONTHS函数的执行时间关系不大。您已经表明，当您使用硬编码的最小日期时，执行计划存在显着差异。执行计划的大变化通常对运行时间的影响更大，而函数调用开销可能不太可能产生影响，尽管潜在的不同执行计划可能意味着需要多次调用该函数。无论如何，要查看的根本问题是为什么没有获得所需的执行计划。

良好的执行计划从S_DOC_QUOTE_IDX1上的范围扫描开始。鉴于查询的更改性质，我认为这是CREATED列上的索引。通常，优化器在过滤条件基于SYSDATE的情况下不会选择使用日期列上的索引。因为它直到执行时间才被评估，在确定执行计划之后，解析器无法对日期过滤条件的选择性做出良好的估计。当您使用硬编码的开始日期时，解析器可以使用该信息来确定选择性，并更好地选择索引的使用。

我也建议使用绑定变量，但我认为由于您使用的是8i版本，优化器无法查看绑定值，因此它仍然像以前一样处于黑暗中。在较新的Oracle版本上，我希望绑定解决方案将有效。

但是，这是一个很好的例子，其中使用文字替换可能比使用绑定变量更合适，因为（a）开始日期值不是用户指定的，并且（b）它将保持整个月的恒定，因此您将不会解析许多略有不同的查询。

因此，我的建议是编写一些代码来确定开始日期的静态值，并在解析和执行之前直接将其连接到查询字符串中。

- Dave Costa

---

感谢您的解释。由于您提到的8i限制，我决定在执行SQL之前在SAS中解析一个文字日期值。 - taheris

Answer 2

首先，你获取不同的执行时间并不是因为Oracle频繁执行日期函数。即使对于每一行都执行该SQL函数（顺便说一下，它可能并不是这样），该函数的执行时间也与从磁盘/内存检索行所需的时间相比微不足道。

你获取完全不同的执行时间，是因为Oracle选择了不同的访问路径。选择一个访问路径而不是另一个访问路径可能导致执行时间相差数个数量级。因此，真正的问题不是“为什么add_months需要时间？”，而是：

为什么Oracle在存在更有效的路径时选择了这条特定的低效路径？

要回答这个问题，必须了解优化器的工作原理。优化器通过估算多个访问路径的成本（如果只有几个表，则估算所有访问路径）来选择特定的访问路径，并选择预计最有效的执行计划。确定执行计划成本的算法具有规则，并且根据从数据中收集的统计信息进行估算。

作为所有估算算法一样，它对你的数据做出了一些假设，例如基于列的最小/最大值、基数和段中值的物理分布等，来推断总体分布。

这如何适用于你的特定查询

在你的情况下，优化器必须对不同过滤子句的选择性进行估算。在第一个查询中，过滤器位于两个变量之间 (add_months(trunc(sysdate,'MM'), -1) and sysdate)，而在另一个查询中，过滤器位于常量和变量之间。

它们看起来对你来说是相同的，因为你已经用它的值替换了变量，但对于优化器来说，这两种情况是非常不同的：优化器(至少在8i中)只为特定的查询计算一次执行计划。一旦访问路径确定，所有后续执行都将获得相同的执行计划。因此，它不能用其值替换变量，因为值可能会在未来改变，访问计划必须适用于所有可能的值。

由于第二个查询使用变量，优化器无法精确确定第一个查询的选择性，因此优化器进行猜测，结果导致你的情况出现了不良计划。

当优化器不能选择正确的计划时，你可以怎么做

如上所述，优化器有时会做出错误的猜测，导致次优的访问路径。即使这种情况很少发生，但仍可能造成灾难性后果（从几秒变成数小时）。以下是一些您可以尝试的操作：

• 确保您的统计数据是最新的。在ALL_TABLES和ALL_INDEXES上的last_analyzed列将告诉您这些对象的统计信息上次收集的时间。良好可靠的统计信息会导致更准确的猜测，从而带来更好的执行计划。
• 了解不同的选项以收集统计信息（dbms_stats包）
• 重写查询以利用常量，当它有意义时，以便优化器进行更可靠的猜测。
• 有时候两个逻辑上相同的查询将导致不同的执行计划，因为优化器不会计算所有可能路径的相同访问路径。
• 有一些技巧可以强制优化器在其他之前执行某些连接，例如：
  
  • 使用rownum来材料化子查询（可能需要更多的临时空间，但可以让您强制优化器通过特定步骤）。
  • 使用提示符，尽管大多数情况下我只会在所有其他方法都失败时使用提示符。特别是，我有时使用LEADING提示来强制优化器从特定表（或一对表）开始。
• 最后，您可能会发现，较新的版本通常具有更可靠的优化器。 8i已经12年了，也许是时候升级了 :)

---

这是一个非常有趣的话题。Oracle优化器在不同版本之间不断变化，在时间推移中不断改进，即使有时会因为纠正缺陷而引入新的问题。如果您想了解更多信息，我建议阅读Jonathan Lewis的基于成本的Oracle: 基础知识。

- Vincent Malgrat

---

虽然我选择了使用Dave Costa的文字替换建议，但这也是一个很好的答案。通过使用你提到的LEADING提示，我能够显示一些改进。不过，最终证明拥有一个文字日期值是最有效的。 - taheris

Answer 3

这是因为该函数在每次比较时都会运行。

有时将其放入“select from dual”中会更快：

and d.created 
    between (select add_months(trunc(sysdate,'MM'), -1) from dual) 
    and sysdate

否则，您也可以像这样连接日期：

select
    -- stuff --
from
    s_doc_quote d,
    s_quote_item i,
    s_contact c,
    s_addr_per a,
    cx_meter_info m,
    (select add_months(trunc(sysdate,'MM'), -1) as startdate from dual) sd
where
    d.row_id = i.sd_id
    and d.con_per_id = c.row_id
    and i.ship_per_addr_id = a.row_id(+)
    and i.x_meter_info_id = m.row_id(+)
    and d.x_move_type in ('Move In','Move Out','Move Out / Move In')
    and i.prod_id in ('1-QH6','1-QH8')
    and d.created between sd.startdate and sysdate

最后一个选项，也是提高性能的最佳机会：向查询添加日期参数，如下所示：

and d.created between :startdate and sysdate

[编辑] 很抱歉，我看到你已经尝试了这些选项。仍然很奇怪。如果常量值有效，绑定参数也应该有效，只要你将add_months函数保持在查询之外。

- GolezTrol

---

谢谢，不过我已经尝试了类似于你建议的方法。然而，正如你所提到的，我怀疑根本原因与函数被多次调用有关。 - taheris

当您使用绑定参数或连接选择（建议3和2分别）时，不应该发生这种情况。然而，Oracle的工作方式很神秘。 :) - GolezTrol

Answer 4

这是SQL。您可能希望先使用PL/SQL并将计算add_months(trunc(sysdate,'MM'), -1)保存到变量中，然后再绑定。

此外，我曾经看到SAS计算需要很长时间，因为它要在网络上拉取数据并对每行进行额外的处理。根据您的环境，您可以考虑创建一个临时表来存储这些连接的结果，然后访问临时表（尝试使用CTAS）。

- tbone

---

谢谢，不过我已经尝试过绑定变量了。关于使用SAS，我确保所有操作都通过透传查询完成，以便只返回结果，而不在SAS端进行任何处理。 - taheris