performance - 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

它几乎立即开始返回行。

到目前为止，我已经尝试用绑定(bind)变量替换动态日期条件，以及使用从对偶表中选择动态日期的子查询。到目前为止，这些方法都没有帮助提高性能。

因为我对 PL/SQL 比较陌生，所以我无法理解执行计划中出现如此大的差异的原因。

我还尝试将查询作为 SAS 的传递来运行，但为了测试执行速度，我一直在使用 SQL*Plus。

编辑:

为了澄清起见，我已经尝试使用如下绑定(bind)变量:

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 的执行成本。

如果可能的话，我还想避免绑定(bind)变量，因为我不相信我可以从 SAS 传递查询中引用它们(尽管我可能错了)。

Answer 1

我怀疑这里的问题与 ADD_MONTHS 函数的执行时间有很大关系。您已经表明，当您使用硬编码的最小日期时，执行计划存在显着差异。执行计划的大变化对运行时间的影响通常比函数调用开销可能更大，尽管可能不同的执行计划可能意味着函数被调用更多次。无论哪种方式，要看的根本问题是为什么你没有得到你想要的执行计划。

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

我也会建议绑定(bind)变量，但我认为因为你在 8i 上，优化器无法查看绑定(bind)值，所以这让它和以前一样处于黑暗中。在更高版本的 Oracle 中，我希望绑定(bind)解决方案会有效。

但是，这是一个很好的情况，使用文字替换可能比使用绑定(bind)变量更合适，因为 (a) 开始日期值不是用户指定的，并且 (b) 它将在整个月内保持不变，所以你不会解析许多略有不同的查询。

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