ORACLE FBI错误重现

原文作者：Jonathan lewis
原文地址：https://jonathanlewis.wordpress.com/2015/01/12/fbi-bug-reprise/
译文如下：

我现在有理由重现一下我三年前写的一篇博文。注释中说，在9.2.0.8中发现的一个异常在10.2.0.3中得到了修复——这对于帖子中的简单示例来说是正确的；但是最近在OTN数据库论坛上的一个问题表明，这个bug仍然出现在了更复杂的情况下。下面是创建表和相关索引的代码：

rem
rem Script: descending_bug_04.sql
rem Author: Jonathan Lewis
rem Dated: Jan 2015
rem
 
create table t1
nologging
as
with generator as (
    select  --+ materialize
        rownum id
    from dual
    connect by
        level <= 1e4
)
select
    rownum          id,
    cast(dbms_random.string('U',2) as char(2))  c1,
    cast(dbms_random.string('U',2) as char(2))  c2,
    cast(dbms_random.string('U',2) as char(2))  c3,
    cast(dbms_random.string('U',2) as char(2))  c4,
    rpad('x',100)       padding
from
    generator   v1,
    generator   v2
where
    rownum  <= 1e5    -- > comment to avoid wordpress formatting issue
;
 
begin
    dbms_stats.gather_table_stats(
        ownname      => user,
        tabname      =>'T1',
        method_opt   => 'for all columns size 1'
    );
end;
/
 
create index t1_iasc  on t1(c1, c2,      c3, c4) nologging;
create index t1_idesc on t1(c1, c2 desc, c3, c4) nologging;

为了对OTN上的问题进行模拟，我设计了上述表。这种异常可能会在更简单的情况下出现。请注意，我在相同的四列上有两个索引，但是其中一个索引的第二列声明为降序方式。为了在执行计划中明显的识别索引，后一个索引的名称中带有 “desc”字样。因此，这是带有执行计划的查询，该查询会使用以下索引之一：

select
        *
from t1
where
        (C1 = 'DE' and C2 >  'AB')
or      (C1 = 'DE' and C2 >= 'AB' and C3 > 'AA' )
or      (C1 = 'DE' and C2 >= 'AB' and C3 >= 'AA' and C4 >= 'BB')
order by
        C1, C2, C3, C4
;

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |    21 |  2478 |     4  (25)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IASC |    21 |       |     3  (34)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"='DE')
       filter(((SYS_OP_DESCEND("C2")<SYS_OP_DESCEND('AB') AND "C2">'AB') OR
              (SYS_OP_DESCEND("C2")<=SYS_OP_DESCEND('AB') AND "C3">'AA' AND "C2">='AB') OR
              (SYS_OP_DESCEND("C2")<=SYS_OP_DESCEND('AB') AND "C4">='BB' AND "C2">='AB' AND
              "C3">='AA')))

果然–查询使用了t1_iasc索引，但是当我们没有使用降序索引时，为什么优化器会在其中引入带有sys_op_descend（）函数调用的谓词呢？优化器在代码路径中的某些位置选择了另一个索引，并决定使用其中的一些冗余的信息。这样做的一个作用是cardinality预测为21 –如果我删除索引t1_idesc，则sys_op_descend（）调用消失，cardinality变为148。
Oracle 12c的行为有所不同–除非我添加了HINT/ * + first_rows * /（我不应该使用，但这是由OTN上的作者提供的HINT），否则它将使用concatenation将查询转换为三个单独的索引访问。有了HINT，我们得到了一个实际执行计划，该示例与通过explain plan做出的预测不同：

12c execution plan unhinted (concatenation, and lots of sys_op_descend()):
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |          |       |       |    12 (100)|          |
|   1 |  SORT ORDER BY                        |          |   149 | 17582 |    12   (9)| 00:00:01 |
|   2 |   CONCATENATION                       |          |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1       |   142 | 16756 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | T1_IDESC |     1 |       |     2   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1       |     6 |   708 |     4   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN                  | T1_IDESC |     1 |       |     2   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1       |     1 |   118 |     3   (0)| 00:00:01 |
|*  8 |     INDEX RANGE SCAN                  | T1_IASC  |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("C1"='DE' AND "C3">='AA' AND "C4">='BB' AND
              SYS_OP_DESCEND("C2")<=SYS_OP_DESCEND('AB'))
       filter("C4">='BB' AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("C2"))>='AB' AND "C3">='AA')
   6 - access("C1"='DE' AND "C3">'AA' AND SYS_OP_DESCEND("C2")<=SYS_OP_DESCEND('AB'))
       filter("C3">'AA' AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("C2"))>='AB' AND
              (LNNVL("C4">='BB') OR LNNVL(SYS_OP_DESCEND("C2")<=SYS_OP_DESCEND('AB')) OR
              LNNVL(SYS_OP_UNDESCEND(SYS_OP_DESCEND("C2"))>='AB') OR LNNVL("C3">='AA')))
   8 - access("C1"='DE' AND "C2">'AB' AND "C2" IS NOT NULL)
       filter((LNNVL("C3">'AA') OR LNNVL(SYS_OP_DESCEND("C2")<=SYS_OP_DESCEND('AB')) OR
              LNNVL("C2">='AB')) AND (LNNVL("C4">='BB') OR
              LNNVL(SYS_OP_DESCEND("C2")<=SYS_OP_DESCEND('AB')) OR LNNVL("C2">='AB') OR
              LNNVL("C3">='AA')))
 
12c Execution plan with first_rows hint (and the sys_op_descend have gone)
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |   150 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |   148 | 17464 |   150   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IASC |   148 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"='DE')
       filter(("C2">'AB' OR ("C3">'AA' AND "C2">='AB') OR ("C4">='BB' AND
              "C2">='AB' AND "C3">='AA')))
 
12c Execution plan with first_rows according to Explain Plan (and they're back again)
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |   148 | 17464 |   150   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |   148 | 17464 |   150   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IASC |   148 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"='DE')
       filter("C2">'AB' AND SYS_OP_DESCEND("C2")<SYS_OP_DESCEND('AB') OR
              "C3">'AA' AND SYS_OP_DESCEND("C2")<=SYS_OP_DESCEND('AB') AND "C2">='AB' OR
              "C4">='BB' AND SYS_OP_DESCEND("C2")<=SYS_OP_DESCEND('AB') AND "C2">='AB' AND
              "C3">='AA')

从好的方面来说，升级到12c消除了sys_op_descend（）的某些查询表现，并且似乎已解决了sys_op_descend（）调用确实出现时的cardinality异常的问题 -但仍然出现了奇怪的问题。（这似乎是另一种情况，可以删除不使用的索引并查看执行计划的更改。）

脚注：
在使用12c时，必须非常小心，以免引起混淆，因为SQL Plan Directives会不时出现，并且在我重复某些实验时会给我带来意想不到的结果。